need some database design advice

[apok69]

I did try to search on this site but for some reason it wouldn't work for me or my search terms didn't work.

 

I am working on designing a system to track user transactions in different stores.  It's fairly basic in that I'm just brainstorming right now.  I think I have all the tables I need but I wasn't sure which direction to go with the transaction table. So right now I have:

 

1) Products table

2) Categories table

3) Company table

4) Store table

5) Users table

6) Transaction table by user

 

My original thought was to have one transaction table per user.  But then I got worried that if I have several thousand users with thousands of transactions will the database be able to handle that kind of querying.  So my next thought was to have a transaction management table i.e. a table that has the date, store, and transaction_table columns.  This it would mean that I would have hundreds of mini transaction tables but I would be able to point to them via the transaction management table.

 

Is this a bad idea? Should I approach differently?

 

 

[thehippy]

If the number of tables scales with the number of users or objects, its a bad design.

 

Could you explain what your transactions consists of? For example would it be something like "User buys 18 oranges, 5 apples, a cucumber, and a 8oz steak at store xy" or if had you imagined something else, please explain it.

[apok69]

Yes, that's something very similar to what I was thinking.

 

10 books

5 dvds

1 orange

 

I was looking at some ecommerce schema's like oscommerce, and they put all their order's in to one mega table.

[thehippy]

Right, so.. transactions is kind of the wrong word I think for this, you want a table to record the buy orders from customers

 

Order table

order_id, customer_id (FK), store_id (FK), product_id (FK), quantity, status ... et cetera

 

Then an order with multiple items of varying quantities can be inserted into the table by adding additional rows, all with the same order_id.  You'll probably want to subtract the products from the main product inventory while an order is pending, if cancelled added back.

 

I'm not sure what you mean by a mega table.

[apok69]

Now that I see I guess "transaction" wasn't the best word.  The table layout there is what I was thinking of originally. It's something similar that I saw in the oscommerce schema.  I called it a mega table because it was one table holding all the records for all the customers.

 

I think I once I have it have fleshed out more I can see how well it works.

 

Thanks for the advice.

[DavidAM]

Order table

order_id, customer_id (FK), store_id (FK), product_id (FK), quantity, status ... et cetera

 

Then an order with multiple items of varying quantities can be inserted into the table by adding additional rows, all with the same order_id. 

 

Actually, you would want to normalize this into two separate tables:

 

OrderHeader - order_id, customer_id (FK), store_id(FK), order_date, order_status, etc.

 

OrderLines - order_id (FK), line_no, product_id (FK), line_qty, line_status, etc.

 

There is no sense in repeating all of the "header" data multiple times.

 

And for the record: Anytime you are thinking about having multiple tables with the same layout, you almost certainly need to re-think the design. The database server (and your application) will handle one very large table much more efficiently that multiple small ones.

[thehippy]

OrderHeader - order_id, customer_id (FK), store_id(FK), order_date, order_status, etc.

 

OrderLines - order_id (FK), line_no, product_id (FK), line_qty, line_status, etc.

 

Yes, admittedly it did need that.  Though its a bit confusing to me, again perhaps its just a use of language.

 

Order - order_id, customer_id (FK), store_id(FK), order_date, order_status, etc.

 

OrderHasProduct - order_id (FK), product_id (FK), line_no, line_qty, line_status, etc. (where line_* is the inventory data? That's not in something like StoreHasInventory?)

 

 

[apok69]

Order table

order_id, customer_id (FK), store_id (FK), product_id (FK), quantity, status ... et cetera

 

Then an order with multiple items of varying quantities can be inserted into the table by adding additional rows, all with the same order_id. 

 

Actually, you would want to normalize this into two separate tables:

 

OrderHeader - order_id, customer_id (FK), store_id(FK), order_date, order_status, etc.

 

OrderLines - order_id (FK), line_no, product_id (FK), line_qty, line_status, etc.

 

There is no sense in repeating all of the "header" data multiple times.

 

And for the record: Anytime you are thinking about having multiple tables with the same layout, you almost certainly need to re-think the design. The database server (and your application) will handle one very large table much more efficiently that multiple small ones.

 

I think this is most definitely what I was leaning towards but didn't know how to say it.  I haven't developed my own system before so I worried that very large tables would be worse in the long run, but this may work out better.

[epixeltechnologies]

Order table

order_id, customer_id (FK), store_id (FK), product_id (FK), quantity, status , Transaction id(FK)

 

Put user wise transaction in transaction table with very few info.so all details will be stored in order table only.