Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jan 2008
    Posts
    10

    Question How to store differing items in a single table?

    Short description: I have three attributes that can together fully describe a single item. The problem is that one of the three is not always applicable, but when it is, there are logical restrictions on one of the other two attributes.

    Long description: What I'm doing is creating a database to catologue an ever growing video game collection. Each item can be fully qualified with three attributes: system, title, and type. System is the gaming system (Atari 5200, Nintendo, etc.). Title is the title of the game (Super Mario Bros., Pong, etc.). Type is what type of an item it is (cart, manual, box, etc.). Thus, entries in an "Item" table would look something like this (ignoring the surrogate key for each entry):

    System, Title, Type

    NES, Final Fantasy, cart
    NES, Final Fantasy, manual
    SNES, Donkey Kong Country, cart
    Playstation, Metal Gear Solid, guide

    The first problem I have is that I want to make sure certain titles can only be associated with certain systems. For instance:

    NES, Metal Gear Solid, cart

    doesn't make sense, since there is no Metal Gear Solid game for the NES. Some games _do_ appear on multiple systems, however, so something like:

    SNES, Donkey Kong Country, cart
    GBA, Donkey Kong Country, cart

    needs to be possible. Also, hardware items for a system causes trouble too, since they aren't associated with any particular title. An entry would look like:

    NES, ???, system
    N64, ???, controller

    I suppose I could use the type on each line as the title as well, but that doesn't really make much sense, and then could also lead to the following which also doesn't make sense:

    NES, system, cart

    So, I have two problems:

    1) How do I tie titles and systems together? One idea was to create a reference table that only has the valid system-title pairs, but then I still have the second problem:

    2) How do I allow for items that don't have a title attribute?

    I think my biggest problem is wanting to restrict valid entries to real life items. I will probably be the only one to ever use this database, so one "solution" would be to remove the restrictions and if I make an invalid entry, it's my own fault. That's certainly easy, but being as I'm new to databases and want to learn the best ways to build and use them, that seems like just an easy way out. Another thought was to put some logic into the front-end entry system, but I'm not sure if that's a very good solution either.

    Any thoughts on this are greatly appreciated, as I keep bouncing back and forth between a couple different ideas, and can't seem to find any I really like. Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    congrats on wanting to do it right

    start off by building three tables, one for systems, one for games, and one for items

    do not use numeric ids at this point in the process

    add as many descriptive columns to each table as possible

    for example, the systems might have name, chiptype, joystick channel, etc.

    the games would have title, playability, memory requirements, rating, etc.

    get the idea?

    see if you can pick a column or columns in each table that will uniquely identify each row in the table

    show us how far you get, before going further to record your actual data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2008
    Posts
    10
    Quote Originally Posted by r937
    congrats on wanting to do it right

    start off by building three tables, one for systems, one for games, and one for items

    do not use numeric ids at this point in the process

    add as many descriptive columns to each table as possible

    <snip>

    see if you can pick a column or columns in each table that will uniquely identify each row in the table
    The systems table would be fairly easy. The columns I'd actually be interested in storing would be:

    Code, Name, Year Released

    where:
    Code = NES, SNES, AT5200, etc.
    Name = Nintendo, Super Nintendo, Atari 5200
    Year Released = 1985, 1992, etc.

    Uniqueness could be either Code or Name or both, but I'd probably keep it just Code.

    A games table would probably have:

    System, Title, Subtitle, Year Released

    And the unique columns would have to be System and Title together, since one Title could be on multiple Systems, even in the same Year Released.

    The Items table would have:

    Acquisition Cost, System, Title, Type, Condition

    The unique columns would have to be a combination of all three of System, Title, and Type, but that leads me to the same wall I've been bashing my head against as I explained in my first post...

  4. #4
    Join Date
    Jan 2008
    Posts
    10
    After submitting my previous reply, I made some further thought on the Items table. This (potentially) makes more sense, and is closer to what I think I mean:

    Acquisition Cost, System, Game, Type, Condition

    System and Type are still required, as every item must be for a particular system and be of a particular type, but Game is optional, since it's not necessarily associated with any particular game (such as the hardware).

    HOWEVER, Game is still needed to uniquely identify each item, since two different carts for the same system would not be distinguishable without it. But how can Game be both necessary to uniquely identify the item AS WELL AS optional?

  5. #5
    Join Date
    Jan 2008
    Posts
    10
    After further further thought, I think maybe I'm looking at this from the wrong angle.

    I basically have two types of items that I'm trying to record: game-related items and non-game related (hardware) items. So maybe instead of trying to fit both directly into one single items table, maybe my solution is to have a Game table and a Hardware table, both of which have foreign keys to an Item table. Something like:

    Tables:
    Game: (Title (pk), Type (pk), System (pk/fk), Item ID (fk), Subtitle, Year Released)
    Hardware: (Type (pk), System (pk/fk), Item ID (fk))
    Item: (Item ID (pk), Purchase ID (fk), Acquisition Cost, Condition)
    Purchase: (Purchase ID(pk), Seller ID (fk), Total Cost, Received Date, ...)

    (System for both Game and Hardware could reference a System table but is also necessary to distinguish exactly which game or hardware the item is, which is why I put it as both a primary key and a foreign key.)

    This would mean that a SELECT of all the items in a single purchase (of which there could be any number of both games and hardware) would have to check two separate tables instead of one, but I'm not sure how else it would work.

    Further, if I wanted to expand this application into a buying/selling database for multiple different items (shoes, CDs, books, etc.), this seems to be the only option to maintain a purchase and/or a sale table of all the various items, unless I'm missing something, which is very possible.

    Does this make sense? Have I overlooked a simpler solution? Thanks again.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you've done a wonderful job on some pretty tough concepts that many people struggle with

    thanks for taking my suggestions and working through them

    before you get too much further along the path of designing specific tables, see what happens if you add a few rows of sample data (make it realistic, if possible) to each table

    multiple worksheets in an excel file works for me

    give each table enough rows of data to make yourself comfortable that your design is working

    you will recognize situations easily which aren't working too well, as you will find yourself spotting duplication, missing data, etc.

    also, remember that when something is a foreign key, this means that there must be a row in the referenced table with that particular value as the primary key

    make sure you do the check yourself -- for every row of sample data that you create, check the foreign key value to make sure it exists in the primary key

    for example, you mention Seller ID as a foreign key, but i don't see a Sellers table -- you would have to create that first and populate it before can add a row with a particular Seller ID value as a foreign key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2008
    Posts
    10
    Okay, I've attached a spreadsheet I've worked out. Some explainations:

    - The tables are each in their own tab/sheet. The tables with "(ref)" in their name were my way of noting those tables that would typically be set up before actual item entry. i.e., the building blocks (system codes, game types, hardware types, etc.) upon which the items in the collection are created.

    - Columns with "(PK)" indicate a primary key, "(FK)" indicate a foreign key, and "(PK,FK)" indicate a combination primary and foreign key.

    - Columns with a "*" are required.

    - The break down is like this:
    1) A Seller can sell 1+ Purchases.
    2) A Purchase has 1+ Items.
    3) Each Item is either a Hardware Item or a Game Item.
    4) Each Hardware Item references a System and Hardware Type, and MUST correspond with exactly one Item.
    5) Each Game Item references a Game, System, and Game Type, and MUST correspond with exactly one Item.


    Some thoughts:

    - The "Total Cost" column in the "Purchase" table is probably unneccessary since that information can be arrived at by summing the "Acquisition Cost" of each associated Item.

    - The "Game" table is basically a list of all real-life game titles. To simplify the "Game Item" table, I could create a "Game ID" surrogate key in the "Game" table that would uniquely identify each game, but still require each combination of System Code and Title to be unique. "Game Item" would then have an "Item ID," "Game ID," and "Type ID," 3 columns as opposed to the 4 it has now. However, values in the "Game" table really won't ever change (once a game is released, it's name or system can't really change), so I'm not sure if the use of a surrogate key is really necessary or usefull...?

    - The "Item" table, while useful in consolodating unrelated items into one table that can be referenced for purchases and sales, almost seems unneccessary. Each column (Purchase ID, Acquisition Cost, condition) could very well be put into the Game Item and Hardware Item tables. This removes an extra table and its corresponding key and localizes the item specific information. Also, there is no guarantee that any future "Items" will have use of the same columns currently in "Item", so again maybe that table is unneccessary. Something tells me I need to read up on Inheritance...

    Those are a few of my thoughts and ideas. Any thoughts, corrections, input, etc., from anyone still reading this is greatly appreciated.
    Attached Files Attached Files

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jrwahl
    ...so I'm not sure if the use of a surrogate key is really necessary or usefull...?
    i applaud you on your insight

    i found a couple of your tables confusing, and it was the numeric keys that did it

    i doubt that you will want to track sellers, perhaps you can just carry the seller name as a text attribute of whatever item was purchased

    your item table seems particularly problematic -- what would your scheme look like without it?

    anyhow, nice job

    you're probably ready to try building your first draft of the database

    what database system will you be using?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2008
    Posts
    10
    Here's a second version, with the Item and Seller tables removed.

    The "Game Item" and "Hardware Item" tables now directly reference the Purchase ID they were bought from, as well as include the Acquisition Cost and Condition columns. They still need a surrogate key as far as I can tell, since there's no other way to uniquely identify an item, since, for example, multiple carts of the same game can be bought in the same purchase, and even for the same price (see the two Golden Sun carts I added for an example). The same goes for the Purchase table, for similar reasons.

    Since I'm doing this project to mostly polish up my Java skills as well as learn more about working with databases (my new job uses a lot of both), I've kind of chosen writing the front end in Java (using SWING) and using HSQLDB for the database system. However, I haven't gotten TOO far into my research of HSQLDB, so I'm certainly open to other suggestions.

    BTW, I really appreciate all your help.
    Attached Files Attached Files

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think what you have now will work splendidly

    i've never seen anyone learn data modelling so quickly

    good luck with your project and do let us know how it goes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jul 2007
    Posts
    96
    Any particular reason why you are picking HSQLDB over other RDBMS? Perhaps your new job requiers you to have some experience with that particular system?

    If that's not the case I would recomend you to build that app of yours in the mainstream rdbms used by your new employer, which is likely to be Oracle, SQL Server or MySql.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    r937 has done a great job, but I still need to add one additional criteria for you to consider that is important to me but not to r937. R937 deals with the design of systems, I deal with the entire lifecycle of them so I hit practical problems that have nothing to do with the design process itself. I'm not saying that my criteria is "right", but I am strongly suggesting that you consider it.

    My criteria can be simply stated as: NEVER include any attribute that can change in the primary key. There are usually huge issues when you change an attribute in the PK, and different database engines cope with those issues in different ways. I haven't seen a commercially viable database that I think does a really good job of coping with those problems.

    Keys come in several flavors. If any attribute within a key can be changed by the user, by the government, or by the laws of nature, I won't use that Alternate Key as the Primary Key. I will still declare it as an AK, and enforce that declaration with the tools available in the database, but I won't choose it as the PK. This means that I nearly always use a surrogate key such as a GUID or a system generated number for my PKs.

    I'm not saying that this is right, wrong, or indifferent. It is Just another thought/viewpoint to consider.

    -PatP

  13. #13
    Join Date
    Jul 2007
    Posts
    96
    I tend to agree with Pat. In this case, if a publisher is aquired by another company it is likely to change it's name, which in turn would require you to propagate that change throughout the application. Eventhough that can easely be done in SQL Server it's not something I personaly like due to the performance impact it would have on the database during the update process.

    Well spotted, PatP

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, it's a hobby database for game consoles, not a bank or government application

    please take the time and walk through his design, look at his sample data, and suggest which of his PKs will change

    here, i'll get you started --
    Code:
    System Code (PK)  System Name
     
    NES               Nintendo
    SNES              Super Nintendo
    N64               Nintendo 64
    PS                Playstation
    GBA               Game Boy Advance
    GB                Game Boy
    GBC               Game Boy Color
    if you're suggesting that he has to slap a different PK on this table, you're nuts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Diabolic
    ... which in turn would require you to propagate that change throughout the application.
    no, it wouldn't, and you're nuts too

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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