Unanswered: Design Theory - Question about repeating values
I'm assuming the idea is to minimize null and repeating values...yes?
If I have a field that has only 12 possible answers or variables (so to speak) and it's related table may require anywhere from 1-12 of those variables to be related to it. Would it not be better to create a table for said variables and put each in it's own record numbered 1-12. Then create a junction and just link the necessary records to the new record in the main table?
Can you rephrase with a specific example (e.g. your relationship between songs and singers) please? Your question doesn't actually seem to have much to do with repeating values and more to do with relationship cardinality.
One thing I would say:
Originally Posted by .:RoKsTaR:.
I'm assuming the idea is to minimize <snip> repeating values...yes?
Let's say I have a song table that lists the song name only and a number of other tables that list different aspects of that table. Ex. Meter
Here are my two options:
tblSong - songid, song
tblmeter - meterid, meter
Junction to join them
Now if a song has one meter (ex. 4/4 time) then I enter it once in the meter table and link back. If it has two, then i enter 2 records and link back...etc.
But since meter is finite (only so many combination's) and 4/4 get's repeated so much. Wouldn't it be better just to enter all the possible meters in the meter table and just use the the junction to link to the right ones to your new song record.
or do I just keep enter a new 4/4 record for every song that requires it?
I think I understand. Does option 1 involve two tables (Song and SongMeter) and option 2 three tables (Song, Meter and the junction table SongMeter)?
Are you worried that you will get lots of rows with '4/4' in them in the first design, and that the second design solves this "problem"?
They both involve 3 tables, it's just that the first one will have repeating values in the meter table and the 2nd one I guess will have them in the junction. also, the first may require a lookup table for the form combo box, although that may also apply to the 2nd. I'm still new to this
The starting point is defining the natural keys. These are the meaningful ones (TNT, Money, 4/4). The surrogate keys are the IDs. They are surrogates for the natural keys; in other words they are used to represent the natural keys in all your relationships throughout the database.
It is because a surrogate key only serves a purpose when used in a relationship that I suggested songmeteid is redundant. Having just remembered your design, this might not be the case for you.