Results 1 to 15 of 15
  1. #1
    Join Date
    May 2008
    Posts
    10

    Question Unanswered: Create One To Many Relationship

    Hello, noob here.

    I've got the following tables:
    Artists
    Fieds in Artisits:
    Artist ( Primary Key )
    Title
    Type

    Compilations
    Fields in Compilations:
    Artist ( Primary Key )
    Title
    Type

    Funnies
    Fields in Funnies:
    Artist ( Primary Key )
    Title
    Type

    Gone:
    Fields in Gone:
    Artist ( Primary Key )
    Title
    Type

    Mixes
    Fields in Mixes:
    Artist ( Primary Key )
    Title
    Type

    All these table will be used to store different cds.
    So I thought, let me create a Junction table, which will be the main table where I can enter & link all the above info.
    Here is my Junction table :

    CDs
    Fields in CDs:
    Artist ( Primary Key )
    Title
    Type

    When I try to make add relationships, I keep getting a One to One.

    Question, how can I set up a One to Many relationship, so that I only can enter & retrieve info from the CDs table?

    Any help would be appreciated.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I don't think you need relationships.... wouldn't you just need one table?
    Attached Thumbnails Attached Thumbnails sshot.jpg  
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2008
    Posts
    10
    So I'm over complicating things?

    I actually agree. Why I laid out all the tables was because they were on separate sheets in an Excel workbook, and I always organise things too much.

    OK, so, if I just include these fields (as you have in the pic), and like a boolean field for the Old Excel sheet name, like :

    Artist, Title, Type, Status, Mix, Compilation, Artists (not sure how to name this one)

    Would that be fine?
    Sorry but I'm dumb.

    Nice music collection in the pick by the way!

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    So I'm over complicating things?
    Yes... and no. You're also oversimplifying ... I know that sounds like a contradiction, but it's not. The table structure is over complicated, but the field content is over simplified.

    Artist, Title, Type, Status, Mix, Compilation, Artists (not sure how to name this one)
    Well tbh, I find a number of problems here... firstly, the primary key can't be Artist. What do you do when it's a compilation? Those CDs have lots of artists. You also can't put "Various" as the artist since it won't be unique.

    What do you want to do with compilations? Do you want to relate to each artist (entering 20 or so different artists) for each CD or do you want to just enter "Various"?

    Are Mix and Compilation intended to be boolean? If so, I find them unnecessary since you can enter "Mix" or "Compilation" into the Type field as I showed you.

    So far, I see no need for more than:

    CDID (Primary Key)
    Title
    Artist
    Type
    Status

    in a single CD table.

    Nice music collection in the pick by the way!
    Thanks, not bad for a quick chuck together ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2008
    Posts
    10
    Thanks for all your advice.
    Yes, to have Artist as the Primary key won't work

    What I have is:
    Artist
    Title
    Type
    Compilation
    Gone ( or Status )
    Mix
    Funnies
    Number - Primary Key

    Compilation through to Funnies are booleans.
    What I do with Type is I say if it is a Single, CD, Double, or Triple album

    You know what, all those boolean fields are unecessary, I can just include, those options in ListType, for example, if I do:
    Artist
    Title
    Type
    ListType
    Number

    I can specify all the artists, even Various ( As perhaps a default value, if no value entered ), I can give the title, the Type of album, The ListType ( which would be Compilation, Gone ( or here ), Mix, Funnies )

    Would that do?

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    That's more like it

    I'd go with Type and Genre, but Type and ListType is ok

    One last thing is to get you to consider making that Number field something like CDNumber or CDID (Number might be a reserved word, not sure, but I would avoid it just in case) and also consider making it an AutoNumber field.

    And you're welcome to the help
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    May 2008
    Posts
    10
    Thanx, yes, this is indeed much much better

    I settled with this:
    CDArtist
    CDTitle
    CDType
    CDListType
    CDNumber

    And, while I was honing my new learnt skills, I added another table called DVDs:
    DVDTitle
    DVDArtist
    DVDListType
    DVDNumber

    everything works nice, thanks to you

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Again, you're welcome

    Why did you add the other table? I see no need for it...
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    May 2008
    Posts
    10
    It was just to separate the DVDs from the CDs

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    That could be done with just another field (MediaType) which would contain "CD" or "DVD" or "Blu-Ray". Still no need for another table!!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    May 2008
    Posts
    10
    To be honest, I never even thought about that. How stupid of me

    Thanx!!

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by StarTrekker
    That could be done with just another field (MediaType) which would contain "CD" or "DVD" or "Blu-Ray". Still no need for another table!!
    yah don't need another table, but in all honesty I'd reccomend that you do... why?
    then your "users" are constrained to define a a media type which already exists, so you wont have "cd", "CD", "cD" or "Cd".. or the even worse prospect " CD" or "CD ". that ay round when you come to producing say a report sorted by media type then ALL cd will be in the same group

    but then again Im not convinced that media type is neccesarily the correct specifier, as its possible to have, say, music on CD that is in a different format, its also possible to have music in multiple formats (i have quite a few albums that are on LP & CD, and all are now on MP3 (stored on CD, DVD & disk))

    ..but that may be a sophistication beond the current requirement
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    There's a difference between the media the music is on and the format it is in. If you wanted to detail the format then you would have another field called MusicFormat or something that allows you to record MP3, CDA, DV etc.

    I still see no need for another table here, unless you're talking about a lookup table so the user can manage the list of selectable choices.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agree media type and format are different attributes

    however I do think that the media type should be "pushed" to a parent table to enforce data integrity. but that may be a level of sophistication not required, likewise the recording format.

    I do think that its perfectly possible that a person may well have more than one version of a specific album or track (as sadi before I do have some LP's & CD's of the same Album, and ALL my LP's & CD's are also encoded/duplicated as MP3's, a few are WMA's and definately NO AAC's)
    so in my case its quite possible that the album will exist in several states
    on LP
    on CD as both conventional CD & Digital compressed (MP3, WMA)
    on DVD likewise
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just to play devil's advocate healdem, when you say
    Quote Originally Posted by healdem
    then your "users" are constrained to define a a media type which already exists, so you wont have "cd", "CD", "cD" or "Cd".. or the even worse prospect " CD" or "CD ".
    My response would be: media types table with a foreign key constraint

    Anyhow, this should be designed using the sub/supertype model.
    George
    Home | Blog

Posting Permissions

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