Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Unanswered: newbie database design question

    I started reading vb and database books this summer because it might help me later on. So I am pretty new at this. This is probably really simple to do, but I have no idea how to go about designing it.

    I have 2 tables:
    Type Table
    ID Name
    1 A
    2 B
    3 C

    Object Table
    ID Name Type Info1 Info2
    1 First A misc misc
    2 First B misc misc
    3 Second C misc misc


    The problem is that the records in the Object table can have multiple types. Number of types an object can have is variable. For example the Object "first" is type A and B. But the object "second" is type C only.

    My question is do I have to make 5 records for an Object if it has 5 different types. Or can I do something like this:
    Object Table
    ID Name Type Info1 Info2
    1 First A, B misc misc
    2 Second C misc misc

    I am using MS access by the way.

  2. #2
    Join Date
    May 2004
    Location
    NH
    Posts
    87

    Cool

    Depending how you have your keys setup it will/won't work. Just make sure you include type as a primary key along with what looks like is name?

    However I don't think that's the best way. I would create an additional table group that manages object/type relation.

    Type Table
    type_id, name

    Object Table
    object_id, object_name, group_id, info1, info2, etc....

    type_group Table
    group_id (pk), type_id (pk)

    Let me know if that helps or if I need to sleep before I post ...

    -Warren

  3. #3
    Join Date
    Oct 2004
    Posts
    4
    I see what you did by using the grouping table.

    I guess my main question is if its possible to input multiple pieces of similar data in one field. For example I have an "Info" table and a keyword table:

    Info Table
    Info_ID====Info_Text====keyword_ID
    1 ========abcdefg=====1, 2, 3
    2=========hijklmno=====4, 5

    keyword table
    Keyword_ID==Keyword_text
    1==========keyword1
    2==========keyword2
    3==========keyword3
    4==========keyword4
    5==========keyword5

    depending on the info, each keyword will be different. And the number of keywords will be different. Is that possible in access? or will I have to resort to repeating the same info for each keyword using a Info_keyword group table:

    Info_keyword_table
    Info_Keyword_ID=====Info_ID====Info_Text===keyword _ID
    1================1=========abcdefg=====1
    2================1=========abcdefg=====2
    3================1=========abcdefg=====3
    4================2=========hijklmno=====4
    1================2=========hijklmno=====5

  4. #4
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    So you're saying you want one attribute to be able to hold multiple foreign key values? i.e. type_id can equal "1,2,3"? No no no, cause you're taking away the relationship. How will you refer to that objects type? You'll have to parse that string and then grab the data.... you'd be taking away the logic of a relational db design.

    I would go with the group design I laid out above, unless anyone else has some ideas?

    -Warren

  5. #5
    Join Date
    Oct 2004
    Posts
    4
    so these are the three options I have:
    1) repeat the data for each foreign key
    2) input multiple keys and parse the data
    3) have key01, key02, key03... fields. And try to anticipate the max amount of keys any piece of info would need.

  6. #6
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    I wouldn't really consider those as options...

    The best thing to do is break up the type_id into a group table.

    Type Table
    type_id, name
    22, "WarrenType1"
    23, "WarrenType2"

    Object Table
    object_id, object_name, group_id, info1, info2, etc....
    123, "WarrenObject", 15, "Some attribute1", "Some Attribute2", etc..

    type_group Table
    group_id (pk), type_id (pk)
    15, 22
    15, 23

  7. #7
    Join Date
    Oct 2004
    Posts
    4
    oooh...I see. thanks. so I do have the repeat the keys. but with a group table, I won't have to repeat all the data, just the keys.

Posting Permissions

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