Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Apr 2010
    Posts
    49

    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?

  2. #2
    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:
    Quote Originally Posted by .:RoKsTaR:. View Post
    I'm assuming the idea is to minimize <snip> repeating values...yes?
    No
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    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. #4
    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"?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    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. #6
    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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    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. #8
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by .:RoKsTaR:. View Post
    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.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by .:RoKsTaR:. View Post
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

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

  11. #11
    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.
    Attached Thumbnails Attached Thumbnails Capture.JPG  

  12. #12
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    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. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would definitely recommend reading the link.

    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    But what about meter changes?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •