I would like to get some advice on how I currently define the keys for my Junction Tables. (*NOTE: I am doing this in MySQL, but my questions should apply to any database environment including Oracle.)
Based on some discussions and advice from this past summer, whenever I create a new table I try and follow this approach...
- Create an ID field
- Set the ID to AutoIncrement and make it a UK
- Look for some physical attribute which naturally describes the record and choose that as the PK (e.g. Username, Email, etc)
This allows me to always have a Unique Key for every record via the AutoIncrement.
It also helps to avoid this problem...
ID User
--- -----
1 DoubleDee
2 DoubleDee
3 DoubleDee
4 DoubleDee
This approach seems to be working very well.
Now on to my specific question...
I have the following tables...
member
- id (UK)
- username (PK)
article
- id (UK)
- slug (PK)
article_thread
- id (UK)
- article_id (PK1)(FK)
- member_id (PK2)(FK)
- subscribed_on (PK3)
Is there any problem creating a Composite Primary Key on the 3 fields above?
A lot of people would just do this...
article_thread
- id (PK)
- article_id (FK)
- member_id (FK)
- subscribed_on
Similar to what I said in my introduction, I don't like the second approach, because it doesn't ensure uniqueness from a PHYSICAL standpoint. (Maybe less true since I am using the Timestamp.)
In my mind, by having an ID that is a UK, I have an easy "hook" to refer to my Junction Table for other joins. But I also have the benefit of ensuring that there is ONLY one Article + Member + Subscribed On combination in my Junction Table.
Am I putting undo stress on my database taking this approach?
MySQL seems fine with things in a test environment, but if I ever have 10,000 or 100,000 or one million records, then maybe I would regret this architectural decision...
What do you gurus think?
Sincerely,
Debbie
"Trying to be cool like Oracle people!!"