Fog Creek Software
Discussion Board




Database help -- which is best

I am just retuning the database for my registration. Trying to save room and make these easier to read.

*************************************
TABLE: License_Key
Lic_key (primary key)
Lic_type (hardcopy/online/promo)
type_ref (the primary key from the type table)

TABLE: Online_keys (keys sold online, via swreg.org)
online_ref (the primary key)

TABLE: Promo_keys (keys used for promotions)
promo_ref (the primary key)

TABLE: hardcopy_keys (keys sold as a sticker on the cd)
hardcopy_refs (the primary key)
*************************************

I hope that makes sense. I am not an expert in databases (okay I am barely a beginner), and this probably really isn't that important, but it is always good to learn the right ways.

Basically each lic_key is of one type, either sold online, sold as a hardcopy, or given away as a promo.

However each type (ie promo/hardcopy/online) stores different information, so I don't want to waste room by keeping them all in the same table.

What is the best way to join the lic_key table with the other three?

I though of putting a 'lic_key' foreign key in each of the three 'type' tables. However this wouldn't work as it would be possible to have the same key in more then one table...

My idea (as seen in my table desciption) is to have a unique key in the lic_key table, made up of 'Lic_type' and 'Type_Key'. However the 'type_key' would not be able to be a proper 'foreign key' as it could be taken from any of three tables.

I thought of dropping the 'lic_type' and 'type_key' colums from the lic_key table, and instead have three columns:
online_key/promo_key/hardcopy_key which are all foreign keys from the respective tables. However doesn't work for the same reasons that my first idea didn't work.

Can anybody help?

Aussie Chick
Wednesday, August 18, 2004

>I am just retuning the database for my registration. Trying to save room and make these easier to read.

Oops just assumed that the entire world knows what I am up to! I am writing the registration process for a little program that I am writing. The databases aren't good enough and I am working on them.

Aussie Chick
Wednesday, August 18, 2004

Aussie Chick, you don't need the three tables Online_keys, Promo_keys and hardcopy_keys, since they can be recovered from the master table as queries. (Select the licence_keys of the required type ).


Wednesday, August 18, 2004

If I did that would be storing heaps of empty fields.

ie the online table has 5 fields that the other two tables don't.
likewise for the other tables. so each field in the master table would have twice as many fields as are actually filled.

Aussie Chick
Wednesday, August 18, 2004

If you want to be strict, then you have a licence table which has all the information common to all licences, which could be:

Licence
Date Issued
Date Ends
Issued to [could be a flat name, address, better would be a contact id to a contact table]
Licence Type

And then a table for each of the different types of licence with their specific information.

However, if its only a couple of fields difference keeping everything all in one licence table wouldn't be such a sin.

Simon Lucy
Wednesday, August 18, 2004

You could either:

a.) not worry to much about the "wasted" space. If you sell 10.000 software packages, you'll be able to afford the ~200k disk space being wasted.

b.) >> I though of putting a 'lic_key' foreign key in each of the three 'type' tables. However this wouldn't work as it would be possible to have the same key in more then one table... <<

That doesn't matter, the lic_key could be in each of the three "subtables". The constraint that the primary key has to be unique only applies to one table. You could even have several entries with the same lic_key in each "subtable".

Leave out the "Lic_type" from the main table. You can infer the type of license it is by the presence of entries in the subtables.

Table: License_Key
lic_key (primary key)
... (general info that applies to all licenses)

Table: online_keys
lic_key (foreign key references license_key.lic_key)
... (specific online key info)

Table: promo_keys
lic_key (foreign key references license_key.lic_key)
... (specific promo key info)

... lots of other tables for your other license types....

Then you create a view using an outer join that combines all the different tables.

  -tim

a2800276
Wednesday, August 18, 2004

You've described a classic database design situation:
"My Foobar entity has three subtypes - do I:
a)  make one big table, with a Foobar_Type column and use what columns are needed for each type....or
b) use three separate tables, all with a 1-to-1 relationship to a parent table with has the job of making holding the keys...or
c) do I use three separate tables with no parent table?"

The answer is...it doesn't really matter. Any options works, as long as you write your queries properly. I would choose one big table for all types, if there will only be a few unused columns for each row. The database housekeeping is easy this way.

BTW:
Find a good guide to database naming conventions, it will make you look like a database pro. I recommend at the very least:
* don't abbreviate words: license_type is better than lic_type for the person who comes 6 months later to make changes.
* use singular form for table names: "online_key" instead of "online_keys"
* use case consistently for column names and table names. For MySQL I suggest 100% lowercase if use dashes "license_key" or mixed case without dashes "LicenseKey".

Herr Herr
Wednesday, August 18, 2004

Herr is correct.  Either way, since you're unsure of the path you'd really like to take and might change your mind in the future, I suggest creating a view and only referencing that view.

The view could either be the single monster-table, or the three type tables - its your call.  But you can then change the underlying tables, update the view, and not change any code.  It's a question of what you want to maintain when representations change (think of this as an interface, because it is).  Good luck.

Lou
Wednesday, August 18, 2004

AC,

how many entries are you expecting that "DB size" would be your primary problem? Smells like premature optimization to me.

Just me (Sir to you)
Wednesday, August 18, 2004

I admire the thought given to wasted space, just because one of the other DB programmers where I work would've never had it :(

I think you should roll them into one big table Aussie. As has been said, housekeeping is easier and the actual impact of your empty fields is negligible unless you are dealing with astronomical numbers of rows.

It's possible, you mmight even use LESS space this way as you will only need to index one table instead of four.

I am Jack's two bits
Wednesday, August 18, 2004

The 'classic' solution is as you've said -- add a 'lic_key' field to each of the other three tables.  It doesn't matter if it is unique across tables -- only that in each table there are no duplicates.

Typically, my database tables (when normalised a little) become 'data' tables (a primary key, then lots of data associated with that key) and 'link' tables (a table that has records which are pairs -- a primary key from one data table, and a primary key from another data table).

A typical query then would take a primary key from a data table, then do a 'SELECT * from MyLinks WHERE FirstKey = MyPrimaryKey'.  This returns a recordset of records from the link table. 

Now, for each record in the list, I can get whatever related data I need from the linked data table.  This can be done with nested 'SELECT' statements, by the way, or with a 'JOIN' statement.

You may have to be careful when using the 'JOIN' statements, to make sure you don't create a huge recordset which then gets 'filtered' down to the few records you want.  This is not a problem, just lowers performance.

AllanL5
Wednesday, August 18, 2004

Guys thanks. I haven't said much I am still processing.

I was thinking overnight that the single table might be the easiest method. It still bugs me that it is not foolproof, ie data can be entered into fields that have no right holding data for that type.

I do tend to overthink for such a small project, however this is less about an anticipated million sales (*how nice*), and more about my way of learning. No matter how small a project I try to do it the 'correct' way, and hence gain alot of understanding/knowledge etc.

NB Thanks for the tips of naming conventions.

Aussie chick
Wednesday, August 18, 2004

>It still bugs me that it is not foolproof, ie data can be entered >into fields that have no right holding data for that type.
>

If data only gets there from the application ie not ever directly inserted to the database, this won't be a problem

btw: i moved to canberra, so no chance of a brisbane JOS meetup in the future for me :)

Dan G
Wednesday, August 18, 2004

Yes you are right,  as long as the scripts are correctly written.....



NB. I love Canberra. I have visited every single secondhand bookstore in that town! I also found the most brilliant cafe-by-day/nightclub-by-night right next to one of the said bookstores. Huge tables, I always thought if I live in Canberra I would go study there every day!!

Aussie chick
Wednesday, August 18, 2004

*  Recent Topics

*  Fog Creek Home