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.

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

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...

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?

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.

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

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.

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?

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.

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

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.

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

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

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 --if you're suggesting that he has to slap a different PK on this table, you're nuts

no, it wouldn't, and you're nuts too