I did the sketch of my model, but I don't know if the relationship (identifying and non-identifying) are correct, and I don't want to continue to let it pass.
If you can just look at what I did, and tell me if that is correct (or not), I would appreciate it.
One product may be on many orders; one order may contain many products:
You have this structure for your cross-reference table:
+-----------------+
| orders_products |
+-----------------+
| id PK |
| orders_id PK |
| products_id PK |
+-----------------+
I don't think the auto-incrementing id field is necessary unless an order can contain the same product more than once (which wouldn't make sense). If you are doing this to record the quantities of each product specified on the order, it would be much better to have a quantity column for each order_id -> product_id instead. I would change the structure of that table to:
+-----------------+
| orders_products |
+-----------------+
| orders_id PK |
| products_id PK |
| quantity |
+-----------------+
One user may have many address presets; one address preset may only belong to one user:
I don't think it would be a good idea to connect the addresses table to the orders table. What if the user deletes or updates one of his/her address presets? The delete/change would cascade to any past order that contained that address. Depending on how you have your cascade actions set up, those order records would be deleted entirely or you would have misinformation on old orders. This is one situation where referential integrity would NOT be appropriate. Once an order is placed, the shipping address that was specified for that order should not be able to be changed later down the road.
What you should do instead is use the addresses table only to store a list of address presets a user has (so that a drop-down selection can be generated for the user). When a user submits an order, you should just insert the actual textual address information into a 'shipping_address' column on the orders table.
To better understand the difference between identifying vs non-identifying relationships, see:
and my answer here:
