How to set up keys in child table

[UpstateLeafPeeper]

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!!

[r937]

classic natural vs surrogate keys issue

if you feel you need ~both~ id and order_id, it can only be because you want to allow the latter to change values (whereas the former can’t/won’t)

[UpstateLeafPeeper]

Well, not exactly. Obviously the ID will never change, and I created an “order_no” because I am thinking it might be more user-friendly. (Also, it might look funny to customers if their OrderNo is 1, 2, 3, 4,… )

So what I was trying to ask is if I should bundle up the “order_no” and the “member_id” into one UK? (Or the same would apply to whether I should bundle the “order_id” and the “member_id” into one UK.)

Follow my question?

But on a side note, what are your thoughts on using natural vs. surrogate keys?

I know some people think it is stupid to give every table an “ID” because they complain that it doesn’t describe anything in real life.

However i would point out that making sure every table has an “ID” gurantees you have a non-changing PK, and there is nothing stopping you from finding other keys which have a physical nature to them and can serve as a secondary way to uniquely identify something.

For instance, in my MEMBER table, I have the “username” as a UK and “email” as another UK. (In theory the username would never change and always be unique, so it could be a PK, but I was just taught that it is better to link tables with integers like an ID.)

What are your thoughts on this side issue/