Hey guys, I'm designing a shopping cart e-commerce database. I basically want to have a table called purchase_order which provides the details of each order. I then also want a table called order_item which provides details or order items. My delima is this I want to have product tables that differ in what columns they have. Basically I want to have certain products that have certain attributes that the others don't have. So I could create different tables for each of these products but how do I associate it with the order_item table? How does the order_item table know which table to get the product from? Any help would be appreciated.
if I understand you correctly, just take unique ID for product and just associate whit this ID its silly to "create different tables for each of these products"
That makes the most sense. Unless you only have 2-3 different product types then a single table would be easiest to manage and set up. You can control what fields are used from this table for different product types through the code - this is much simpler. Using different multiple different tables for different products will make your code a mess, DB access a nightmare to manage, and your shopping cart difficult to maintain. Keep it simple and use the code rather than the database to manage the interface.
There's one thing called relationship, you can build a relationship between two tables, and make primary key, secondary key, foreign key for each table based on your needs! The keys can be used for sorting, searching, and selecting! Good luck with that!