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…