1. Registered User
Join Date
Apr 2010
Posts
49

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?

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
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?
No

3. Registered User
Join Date
Apr 2010
Posts
49
Ok, if I go back to my design:

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?

4. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
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"?

5. Registered User
Join Date
Apr 2010
Posts
49
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

6. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Ah - so this question then is about natural keys Vs surrogate keys.
So in option one your junction table data will be like:
Code:
```songId      Meter
1           4/4
1           4/3
2           4/3
2           12/11```
and option 2:
Code:
```songId      MeterID
1           1
1           2
2           2
2           3```
Correct?

7. Registered User
Join Date
Apr 2010
Posts
49
more like this, in both scenarios there is a song, meter, and junction table. except that the first meter and junction tables will have endless entries.

In the second, the meter table would be something like this:
2/4
3/4
4/4
6/8
12/8
5/4
7/8 etc
Plus another dozen or so, but in the end it would be finite as i would only enter each value 1x.

The junction on the other hand would be neverending

8. Registered User
Join Date
May 2010
Posts
601
Originally Posted by .:RoKsTaR:.
I'm assuming the idea is to minimize null and repeating values...yes?
In the same record that would be true

In data normalization you should avoid repeating fields in the same record. This is where a group of field in the same record holding for example phone numbers.

Repeating fields in the same record is not the same thing as having multiple rows/record with the same data in a field.
Last edited by HiTechCoach; 10-08-10 at 13:05.

9. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by .:RoKsTaR:.
more like this, in both scenarios there is a song, meter, and junction table. except that the first meter and junction tables will have endless entries.

In the second, the meter table would be something like this:
2/4
3/4
4/4
6/8
12/8
5/4
7/8 etc
Plus another dozen or so, but in the end it would be finite as i would only enter each value 1x.

The junction on the other hand would be neverending
Ok - please show some data in the meter and junction tables for scenario one.

10. Registered User
Join Date
Apr 2010
Posts
49
ok, let me put something together and i'll be back

11. Registered User
Join Date
Apr 2010
Posts
49
ok here's the two examples; the top 3 are from my original design and the bottom row is how I think it probably should have been done to minimize repeating values in the meter table.

12. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Aha - we've got there!
Ok - option 1 is not even an option
Easy, eh?

This is a very good example of why, when data modelling, you should not use surrogate keys, only natural keys. If those terms are unfamiliar please, please read this:
The Relational Data Model, Normalisation and effective Database Design

BTW - songmeterid is redundant in both models.

13. Registered User
Join Date
Apr 2010
Posts
49
Thanks

which ones are surrogate keys in my table? That may make the definition more clear

also, how is songmeter redundant? sorry for all the questions :P

14. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912

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.

15. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102