May 11, 2007 at 9:44 am
I would like suggestions on how to properly create an inventory database which will hold the current inventory for possibly thousands of companies in a central database.
Example would be tracking the current inventory of all car dealerships that sell Chevrolet vehicles. Remember this is a hypothetical question, so the numbers may seem out of whack in a realistic situation.
I would like the database to store each dealer's contact information in one table and their inventory in a seperate table. That seems simple enough if it was one dealership or even several dealerships. The number of records in that table would be relatively small. But what if you have 10000 dealerships each selling 5000 vehicle models, or 50,000,000 records?
Would 50 million records create a problem (slowness) when updating database?
Would you create an index on the inventory table to speed up the process of querying the database?
OR
Would you create a seperate inventory table for each dealership? The thought here is I have 10000 dealerships (Dealer_1, Dealer_2, Dealer_3, ..., Dealer_10000) with table names that are the same as their index in the dealership information table. As dealerships are added, you would create a new inventory table using their index as part of the table name.
When Dealer1 sells a vehicle, gets a delivery of vehicles, or trades vehicle to another dealer the inventory for that vehicle needs to be updated to reflect the transaction.
Is it "better" to have 1 table with 50 million records or 10,000 tables with 5000 records in each? Better equalling more efficient in terms of speed of queries.
Thanks for any help!
May 11, 2007 at 10:11 am
While your premise is wildly unrealistic and does not take into account many "real" inventory questions I will answer the very specific question at the end of the post:
"Is it "better" to have 1 table with 50 million records or 10,000 tables with 5000 records in each? Better equalling more efficient in terms of speed of queries."
IMHO the correct design would be one dealer table, one inventory table (remember I'm keeping it simple!) with the inventory table properly indexed and horizontally partitioned based on (at a minimum) the dealership. The partition would probably extend to Dealership and either an inventory "type" or a time frame (month, year, etc). The partitioned table could then be spread across hardware to increase efficiency.
James.
May 11, 2007 at 10:52 am
Thanks for the response!
While my original question's example is wildly unrealistic it is however realistic that my database will have 50,000-100,000 companies with 100-300 products in inventory. Each of these companies would have to access the database to update their inventory as needed, perhaps as few as 10 times per day or as many as 500 times per day.
Since, I am not an expert by any means, isn't horizontally partitioning the table similar to creating one physical table for each company? I am sure there are benefits to horizontal partitioning otherwise its capability would not exist.
At this point we do not foresee the need to combine company inventories for the purpose of calculations, so keeping the data in one table does not seem necessary for now. My main concern is having a large number or update queries to the inventory table at one time.
I do realize that much of this is above my abilities at this time, that is why I am asking for everyone's input.
thanks!
May 11, 2007 at 11:15 am
Partitioning is "like" having multiple tables, only they are transparent to the front end. The database will handle accessing the correct table based on the data that is being added/edited/deleted. So you get the performance benefits of smaller tables while the front-end gets the benefit of dealing with a single table (this is a highly simplified explanation of a partitioned table).
The point of one table isn't so much for your calculations as for data integrity (and maintenance). If you create several hundred (or thousand) tables that all look alike you get into maintenance and integrity issues. For example if you discover that the Inventory Item "Name" column needs to be 50 characters instead of 25, now you have to change hundreds of tables individually. If your front end application is written "generically" (i.e. it is used by every dealership) then the application must "dynamically" create all database interactions to target the appropriate table names. If your program has a bug it could easily update the wrong table. The update would probably work and it would be difficult to 1) Find the bad data, 2) fix the bad data, 3) identify that your program even has an error. These problems would probably manifest later in the inventory cycles during reporting when one company might have "excess" bogus inventory while someone else would report "missing" data. Very hard to track down.
While I never call myself an expert at anything, I do have 20 years experience designing relational databases and data warehouses. (I'm pretty sure I can at least get a database into proper 3rd Normal Form most of the time:-)) So if you want to discuss any particular design issues feel free to drop me a private message and I'll help where I can.
James.
May 11, 2007 at 11:37 am
TYVM for the responses! I think I am getter a better understanding of how to deal with this from your suggestions and googling database partitions and indexes.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply