On Sat, Mar 02, 2002 at 10:49:44PM -0500, Russell Hires wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Well, yeah, since I've got a few active databases already, and they are
> poorly designed: beginner's bad luck.
>
> The thing I have questions about lately are keys: primary and foreign. If
> you're going to make a primary key, does it always have to be a number? Can
> it be a combination of letters and numbers?
It can be anything you want, including a combination of fields, so long
as it's unique. You might want to also consider sorting issues, since
the table will automatically be "sorted" by that key.
> And when you're using a primary
> key as a foreign key....well, that's not really a question (stream of
> conciousness)....How do you determine where to split up information? Is there
> a such thing as a primary table? I guess I'm thinking of this in a
> heirarchical manner: there is a main table, with your most basic information,
> then there's the other stuff. But is that right?
Not necessarily. You only need additional tables really to avoid
duplicate data in your original table. If you have a table that
describes car parts, you really only need one table. If you have a need
to look at automobile subsystems, then you might have a subsystem table
where each subsystem has a unique key. Then for each record in the table
of parts, it would have a "foreign" key that is that unique key for the
subsystem table. So you have a one-to-many relationship between the
subsystem and parts tables. See my prior post on "ORE&D".
> Then I've got a question
> about adding columns later on, after you've got so many records: how do you
> add them? I realize you probably don't want to put them in a new column in a
> primary table, but the whole key thing bugs me about creating the new table.
> Is this where a foreign key would be useful? (in addition to the other uses
> of foreign keys...)
>
Here's where good design in the first place pays off. You never want to
have to add columns later if you can avoid it. Depending on the DBMS
you're working with, you can add new columns/fields any time. BUT, doing
so later means that by default they will have no content for records
already in the table. You may have to go through a manual editing
process for each of the records in the existing table. You _could_
create a secondary table which only contains these additional columns
and a foreign key that refers to the original primary key. But that's a
kludgy solution.
Keys aren't really that big a deal. Just look at how you want to look up
the information. Really the prototypical example of this is invoices.
You have to have a detail table to contain all the line item
information, and you have to have a header table to contain address and
invoice number and such. But when you look up a header record, you want
to be able to capture the detail data, so you must ensure that a) you
have a unique key for your header records (usually the invoice number)
and b) you have to ensure that your detail records can be linked back to
the header records via that same invoice number which becomes the
"foreign key" in the detail table.
In things like FoxPro (where I learned a lot about databases), there is
no native enforceable relationship between primary and foreign keys, and
the keys aren't even referred to that way. You define relationships at
runtime and ensure that the keys you make at design time can support
those relationships. But with the newer DBMSes, you can design those
relationships in at design time. The DBMS will ensure that your records
adhere to those built-in relationships, or generate an error when they
don't, probably even preventing you from adding records like that.
HTH,
Paul
This archive was generated by hypermail 2.1.3 : Fri Aug 01 2014 - 17:37:52 EDT