Hi, i am creating a small site which sells different types of products (i know there are several free off the shelf sites that are already built, so please do not let this influence your help) I am thinking of setting up an ORDERS table which will holds various details about the order and then another table called PRODUCTS which will hold the various specific details about the product itself. Then i plan on storing the "id" from the PRODUCT table in the "product-id" field in the ORDERS table in order to link the product with the order. This is an example of both tables: ORDERS id date receiver product-id PRODUCTS id product-name price I have two questions which i hope you can help me with: 1 - as i mentioned above i plan on storing the "id" from the PRODUCT table in the "product-id" field in the ORDERS table in order to link the product with the order... how should i name the fields in both tables considering there is an "id" field in both tables and a "product-id" field in the actual ORDER table... i know you can work with 2 tables that have the same field name BUT is it best practice to avoid this... what would you suggest naming all of the above fields... 2 - i will be selling various different types of products, and all of these products have very different attributes that needs to be stored... so should i just make one big huge PRODUCTS table that will have a field that caters for all of the products and there attributes OR should i create a different table for each product type... for example some products will need a fields called "height", "width", "water-depth" etc... whereas other products do not have any values for these fields and need other set fields... Thanks in advance for your help... looking forward to your feedback, thanks...
1. Your naming convention is fine and let "id" be named as "id" only in both tables. As far as foreign key "product-id" is concerned, I would advice on using underscored name "product_id". 2. Don't create separate column for each attribute. You can rather have following kind of table structure. Table: tbl_product_attributes product_id attribute_id attribute_description You can do search on product_id to get all its attributes. You can do search by a particular attribute_id to know which all product has it. Note that I have used "attribute_id" which will be numeric and thus making search faster. Actual values can be in another master table which can be read and mapped onto attribute_id while displaying the content. Moreover, you can store other attribute level meta data into that master table and save data duplication.
Thanks for your reply... so are you saying i should have tbl_orders, tbl_products and tbl_attributes and just link all of the tables using a foreign key... If so, should i just big a big attributes table that will cover all products even if some of the products don't need some of the attributes...
Yes, the attributes table will have details for all products having even a single attribute. You don't need any entry in attributes table for the products which does not have any attribute. In fact you can't make it.
Ok thanks... actually... how do you think this site operates in terms of table(s) design: https://www.ros.ie/evrt-enquiry/vrtenquiry.html?execution=e1s2 If you click on "M1 Passenger Vehicle", then "No" you will see that the drop down that displays next is based on your selection in the current drop down... do you think it goes to a different table each time, depending on your selection
I could not understand your last post. Knowing database design from front-end is not easy, only guess can be made. Moreover, dependent drop-down can work in several way from static data to ajax load and during ajax load it can come from file or database.
One more thing, you may want to break your Orders table out into two tables: Orders and OrderLineItems. Otherwise you will not be able to have more than one product per order. Orders id date receiver OrderLineItems id order_id (relates to the Orders table) product_id (relates to the Products table) To handle a new order (in pseudocode): 1) Insert new order 2) Get the order id (using something like mysql_insert_id in PHP) 3) Insert line items individually into the OrderLineItems table