[prog] database schema

[Meredith L. Patterson]

Wolf Rising wrote:
> below is the database design I've come up with, would anyone be
> able to tell me if what I've got is acceptable or even close to being
> in third normal form?

Third normal form requires that each table only have one primary key.
Thus, your "User", "Tech", "Machine" and "Applications" tables are in
3NF, but your others aren't.

> Notes
> noteID                   int                        PK
> techID                   int
> note                       text
> timeStamp           timestamp
> created                 timestamp           PK

This table is not in 3NF because it has both "noteID" and "created" as
primary keys. Did you have a particular reason for using the timestamp
as a primary key? If you want to be able to sort quickly based on note
creation time, you could just make "created" an index.

You might also want to consider making "techID" a foreign key to the
Tech table.

> Machine Assigned
> userID                   int   PK
> techID                    int PK
> issueDate             date
> returnDate           date

See below for remarks on 2NF vs 3NF.

One question, though ... where are you tracking who has which machine?
Each tuple in this schema says that some tech assigned something to some
user and that it was issued on some date and returned on some date, but
it doesn't track *which* machine was issued.

> Installed Apps
> machineID             int                PK
> appID                      int                PK
> userInstalled         enum
> date                         date

I'd leave this table as it is, to be honest. This table is in second
normal form (each field in the multiple-field primary key is directly
related to the aggregate primary key; in other words, you're not
uselessly replicating any information), but not 3NF.

That may be okay, though. 2NF is commonly used in situations where you
have two keys which relate to one another (in this case, "which machine
has which apps installed on it" or "which app is on which machines") and
those keys are also the primary keys of other informational tables.
(Sometimes you'll hear these sorts of tables referred to as "link tables".)

HTH,
--mlp