I have a question on database design. For example, I have a STUDENT table and a COURSE table. I know that I have to create a composite table to link the tables together to resolve the many-to-many relationship. Therefore, I created a REGISTRATION table. For the primary key of the REGISTRAION table, is it better to use a sequence number called REGRISTRATION NUMBER as Primary Key or better to use the PKs from the two table, i.e. composite primary key Student Number & Course Number? What will give the better performance & better database design technique, the use of sequence number or composite primary key? Also, which of these techniques will facilitate the use of indexes more?
It is better to use the PKs from the two tables as a composite primary key. This method insures that only valid values are used in the REGISTRATION table. If you used a sequence number, then it would just add extra overhead that is not necessary. Since you will derive the composite key values from existing keys from the other tables, you'll likely have the STUDENT and COURSE tables already queried when you are ready to do REGISTRATIONs. Therefore, the use of a sequence wouldn't really buy you much, if any, time advantage.
As far as which technique facilitates the use of indexes, I'd say it's a toss up. The primary key of a table is automatically indexed, so it really doesn't matter if the PK is generated with a sequence or otherwise.
So the bottom-line is that there may not be a real clear winner performance wise, but design wise, the best choice in my opinion is to go with the composite key.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.