[prog] database schema

[Ines Sombra]

Yes you are correct about the composite primary key term.
I have some suggestions about the composite key of the assignations 
table, so here they go:

You need a primary key of 3 elements (user_id, laptop_id, out_date) in 
order to allow users to check the same laptop several times. With only 
user_id and laptop_id a user will be allowed to check only one laptop 
ever and subsequent request would fail.

This is a very basic schema of an over simplified laptop, users and 
assignations. I have also attached an ERD picture to illustrate how this 
situation would be modeled.


CREATE TABLE Users (
        User_id              int NOT NULL,
        First_name           varchar(20) NULL,
        Middle_name          varchar(20) NULL,
        Last_name            varchar(20) NULL,
        Company_id           varchar(20) NULL,
        Email                varchar(20) NULL,
        etc                  varchar(20) NULL,
        PRIMARY KEY (User_id)
)
go


CREATE TABLE Laptop (
        Laptop_id            int NOT NULL,
        Laptop_name          varchar(20) NULL,
        Make                 varchar(20) NULL,
        Vendor               varchar(20) NULL,
        Model                varchar(20) NULL,
        Description          varchar(20) NULL,
        etc                  varchar(20) NULL,
        PRIMARY KEY (Laptop_id)
)
go


CREATE TABLE UserLaptop (
        Machine_id           int NOT NULL,
        User_id              int NOT NULL,
        Out_date             datetime NOT NULL,
        To_date              datetime NULL,
        Description          varchar(20) NULL,
        PRIMARY KEY (Machine_id, User_id, Out_date),
        FOREIGN KEY (User_id)
                              REFERENCES Users,
        FOREIGN KEY (Machine_id)
                              REFERENCES Laptop
)
go


Ines



Wolf Rising wrote:
> It's that UserID and techID together form the primary key, I didn't know how
> exactly to display that, I should have thought to put parenthesis around it,
> sorry. So anywhere I listed more than one PK in a table, I meant it as
> ( I think this
> is the correct term) a composite primary key.
> 
> Thanks :-)
> 
> On 6/2/05, Jacinta Richardson <jarich at perltraining.com.au> wrote:
> 
>>Wolf Rising wrote:
>>
>>
>>>Machine Assigned
>>>userID               int   PK
>>>techID               int   PK
>>>issueDate            date
>>>returnDate           date
>>
>>I have a question about your syntax here.  Are you saying that userID and techID
>>are *independant* primary keys to this table or are you saying that userID &&
>>techID together form the primary key?  I'm pretty sure it's the second.
>>
>>Usually it's easier to explain this by writing:
>>
>># Two-field primary key
>>
>>Machine Assigned
>> > userID               int
>> > techID               int
>> > issueDate            date
>> > returnDate           date
>>primary key (userID, techID)
>>
>># Two independant primary keys
>>
>>Machine Assigned
>> > userID               int
>> > techID               int
>> > issueDate            date
>> > returnDate           date
>>primary key (userID)
>>primary key (techID)
>>
>>
>>Likewise for your other tables, if you could clarify this for me I'll be happy
>>to discuss 3rd normal form and these table with you.
>>
>>All the best,
>>
>>     Jacinta
>>
>>--
>>    ("`-''-/").___..--''"`-._          |  Jacinta Richardson         |
>>     `6_ 6  )   `-.  (     ).`-.__.`)  |  Perl Training Australia    |
>>     (_Y_.)'  ._   )  `._ `. ``-..-'   |      +61 3 9354 6001        |
>>   _..`--'_..-_/  /--'_.' ,'           | contact at perltraining.com.au |
>>  (il),-''  (li),'  ((!.-'             |   www.perltraining.com.au   |
>>
>>
>>_______________________________________________
>>Programming mailing list
>>Programming at linuxchix.org
>>http://mailman.linuxchix.org/mailman/listinfo/programming
>>
> 
> _______________________________________________
> Programming mailing list
> Programming at linuxchix.org
> http://mailman.linuxchix.org/mailman/listinfo/programming

-- 
****************************************************
  (o_  (define (Pix-signature foo)(cond ((zero? foo)())
  //\  (else (cons 'IS-THIS-ANNOYING??? (Pix-signature (- foo 1))))))
  V_/_ (Pix-signature 42)
  ****************************************************