Results 1 to 7 of 7

Thread: Design help...

  1. #1
    Join Date
    Oct 2011
    Posts
    4

    Design help...

    I am trying to design a Lego Database, for my own use at home.
    As I am sure many of you know, Lego bricks can come in many different types, and each of those types in many colors.

    My question is this.
    In designing this db I am trying to understand whether it is best to have a table containing just part numbers and their descriptions, and have a table for colors, and then have a relationship between the 2 to an adjoining table which contains then a unique ID for each piece in each color.

    Or

    Just have 1 table that has each part listed in each color as a unique record in that table.

    I am so confused as to which is the preferred method, to access the data quickly.

    Thanks in advance.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Does every piece come in every color?
    Does Lego's own product identification numbering indicate color?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    With only one person entering and querying the data, it is perfectly fine to have one table. if you have several people entering the data, you may end up with something like this for "How many green pieces do I have?"
    Code:
    select count(*) 
    from table 
    where color = 'Green'
       or color = 'Emerald'
       or color = 'Gren'
       or color = ...well, you get the point.

  4. #4
    Join Date
    Oct 2011
    Posts
    4
    Each Part has it own unique Lego Part Number
    Each Lego Color has it's own official Lego Color Name, but not a unique Number.
    No, every part does not come in every color.

  5. #5
    Join Date
    Oct 2011
    Posts
    4
    Quote Originally Posted by MCrowley View Post
    With only one person entering and querying the data, it is perfectly fine to have one table.
    For now, I will be the only one entering data. However, at some point, I would love to pack this db out, and give it to some of my friends, so they could utilize it, however I do not want them to be able to edit the data. I want to do that on my own, solely. But that is down the road.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    The I'd go with a table of parts, a table of possible colors, and a many-to-many table joining the two.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2011
    Posts
    30
    Quote Originally Posted by blindman View Post
    The I'd go with a table of parts, a table of possible colors, and a many-to-many table joining the two.
    That is exactly what I would do. Just be sure when creating your associative entity, as many-to-many relationships cannot be saved, that you only put the two primary keys from the Part table (PartID) and the Color table (ColorID) in the associative entity.

    Also, when naming your tables, keep them singular. It is much, much easier to manage.

    I hope this helps!

    Martin

Posting Permissions

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