Hello. I am working on an e-commerce site, and could use some advice on the best way to link tables.
For example, I have your classic setup…
MEMBER -||-----0< ORDER MEMBER - id (PK) - username - email ORDER - id (PK) - order_no - member_id (FK)
I thought that I had seen in the past people doing something like this because it apparently helps performance…
MEMBER - id (PK) - username - email ORDER - id (PK) - member_id (FK)(UK) - order_no (UK)
In this case you are binding the “member_id” and the “order_no” because I guess it build a composite index that links “Members” and “Orders” together?
Or do I have it backwards, and should it be this…
MEMBER - id (PK) - username - email ORDER - id (PK) - order_no (UK) - member_id (FK)(UK)
In the past I have always had an “id” for every table and I use that as my PK, and then for foreign keys I just link back to that autonumber.
But I think from a tuning standpoint, some DBAs argue that it is better to use fields that represent things in real life and to use them when you build keys and indexes.
Hopefully my questions make sense!!