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