Confused newbie

Hi everyone,

I am new to database development, and I'm getting a little confused about how to properly structure a database I need to set up.

Here's the scenario. I work for a software company, maintaining their web site, and I would like to change our web site to use a product release/compatibility database.

I have figured out how to appropriately structure most of the database, I think, but I am getting totally stumped about how to normalize the compatibility data.

We have multiple products, and each product has multiple versions and (usually) multiple releases under each version number. We offer both current release and historical release compatibility information on our web site, so the database needs to maintain prior release compatibility info. Each product is, of course, compatible with numerous other products (of ours, and of other software developers).

I don't know how to normalize the compatibility data. The only thing I can think of is to do a separate table for each product's compatibility data, but I realize that is not how these things are normally handled.

FWIW, here's what I'm looking at so far:

PRODUCTS table
-- Code (primary)
-- Name
-- Category (foreign)

RELEASE table
-- ID (primary)
-- Code (foreign)
-- Date
-- Version

CATEGORY table
-- ID (primary)
-- Category

COMPATIBILITY table (one for each product listed in PRODUCTS table)
-- Date (primary)
-- Version
-- One column for each compatible product; each record would contain compatible version numbers for that product

If I didn't do the compatibility table as described above, what other alternatives would I have? Suggestions?

Thanks very much in advance for your help!

Tracy

For the compatibility table, you'll want a many-to-many relationship. That is, you can have multiple rows per product/release. Assume the following data in your tables:

PRODUCTS:
A | Product A | 1
B | Product B | 1
C | Product C | 1

RELEASE:
1 | A | 2004-05-04 | 1.1.2
2 | A | 2003-11-25 | 1.1.1
3 | B | 2002-12-23 | 0.9
4 | C | 2003-09-01 | 2.0

Your compatibility table associates releases with other releases. So for release id 1, you might have a row noting its compatibility with release ids 2, 3, and 4. You might also have another table containing software information about software you don't maintain (or a flag in your products table noting that software package X is internal or external). The compatibility table would also contain rows corresponding to these packages. I'd consider a table something like the following:

COMPATIBILITY:
id (primary)
date (a date should never be a primary key, incidentally -- what if you need to have more than one of a given date?)
release_id (which is a reference also to the version no.)
compatible_id (which is a reference to the version no. for the compatible product.

A sample data set for this table might run as follows:

1 | 2004-07-27 | 1 | 3
2 | 2004-07-27 | 1 | 4
3 | 2004-07-27 | 2 | 4

So when you want to see a listing of all compatible software for Product A, you select from the version table (joining on the products table to get package names) where the id matches the compatibile_ids returned when you query for compatibility.release_id=1. Which will return ids 3 and 4.

Hope that helps a little. Also, please try to provide descriptive subject lines on any future posts. Helps the helpers more easily determine whether or not they can help with a given topic.

Thanks, dhouston (and apologies for the lack of descriptive thread title).

This does really help! I have a couple more questions/clarifications, though.

I'm not entirely clear: would the compatibility table model you're suggesting require one separate compatibility table for each product, as I asked? I don't think so, but your sample data set only lists release IDs for Product A in the compatibility chart, so I'm not sure if that was intentional or just because you were providing a limited example.

If the compatibility table model DOES require a separate table for each product, my next question regards dates as primary keys. You mentioned the date should never be a primary key, because what if there's more than one of a given date. In our release structure, there is never, ever more than one date. Each release always has its own date, even if we make a mistake and have to pull a release. So in that case, is it still not OK to have a date as the primary key?

(Actually, now that I think of it more... the compatibility table wouldn't need a date field at all if it was simply referring to the release table, which contains the pertinent release information. Is that true?)

Finally, in the instance of a single product being compatible with multiple releases of another product, i.e. Product B, release_id 3, could be compatible with both Product A, release_id 1 and Product A, release_id 2 as in this compatibility table (based on your example with columns of id, date, release_id and compatible_id):

1 | 2004-07-28 | 3 | 1
2 | 2004-07-28 | 3 | 2

Would a query be able to join that on a table, so that under Product A's name, the row would simply list "1.1.1, 1.1.2", rather than having to have two rows for each instance of compatibility with Product A?

Once again, I really appreciate the help! Thank you!

Tracy

Yes, the compatibility model I'm suggesting (or something similar) would eliminate the need for a separate compatibility table for each product. Having such tables could wind up producing scalability nightmares. My listing release ids for Product A only was a function of my example being limited, as you suggest.

I guess you could use dates as primary keys in the scenario you present, but it still strikes me as pretty odd. Suppose your release structure changed at some point and you could have multiple releases on a given date. (Realistically, I guess you'd just rename the second release, but work with me -- stranger things have happened than what I'm proposing.) Having the date as a primary key could royally screw you in such a case. As much as possible in db design, you have to try to predict the future in addition to designing for the present. Pointy-haired bosses have a knack for finding ways to screw you in a year when you've got 200 tables using the date as the primary key. I almost always use a 10-digit auto-increment integer as a primary key.

As you note, since the compatibility table is now mapped to the release table, you no longer need a date in the compatibility table anyway.

In answer to your final question, you'll have to retrieve separate rows to get the version numbers, but you can manipulate them programmatically in any way you wish for display purposes. In PHP, for example, I'd get the version numbers (1.1.1 and 1.1.2) into an array and join the array using commas to print out the string format you desire, which you can then print in one row.

Hope this helps. Before you set this compatibility model in stone, build some test tables and make sure you run some queries to make sure you can extract the information you want without too much contortion.

Perfect, I understand everything now... and definitely see your point about the date as well.

Thanks for helping me figure this out! For a newcomer to database design, it seemed a little daunting, even though I'm sure you could do this in your sleep. So I appreciate the assistance greatly!