Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
insar
Starting Member
1 Post |
Posted - 2004-06-09 : 07:58:52
|
[left]we have in our database a table for terror casualties data.we fill in this table with data we get from hospitals in case there was a terrorist attack in town. When a casualty has not provided identification number, the hospital automatically retrieves the casualty file number as identification number.one of the aims of our db application is to identify all casualties.we are now designing the table for acsualties and we hesitate with the primary key:create table casualties(casualtiesId varchar(9) not null,/*fileno or identification number!!!*/ constraint CasualtyEventCode int not null constraint casualtyEventCode_fk foreign key references person (IdNum), constraint IdEventCode_pk primary key(casualtiesId, casualtiesEventCode), CasualtyIdStatus binary, casualtyHealthStatus nvarchar(30), casualtyAdmissionDate DateTime, casualtyReleaseDate datetime, casualtyHospitalCode int constraint casualtyHospitalCode_fk foreign key references hospital(HospitalCode), casualtyIntermediaryID varchar(9), casualtyNote nvarchar(100));create table person(FirstName nVarChar(25), LastName nVarChar(25), IdNum varchar(9), ChildrenNo smallint, Gender smallint, FamilyStat int, Profession nVarChar(25), BirthDate DateTime, ImmigrationDate DateTime, Preligion int, Citizenship nVarChar(20), constraint IdNum_pk primary key(IdNum), constraint Gender_ck check ((Gender>0) and (Gender<4)), constraint FamilyStat_ck check ((FamilyStat>=0) and (FamilyStat<5)), constraint FamilyStat_fk foreign key(FamilyStat) references FamilyStatus(FamilyStatusNum), constraint Preligion_ck check ((Preligion>=0) and (Preligion<6)), constraint Preligion_fk foreign key(Preligion) references Religion(ReligionNum));in the meantime we decided to define a composite primary key for casualties. please note that the casualty's identification number is a foreign key from table person. The problem is that in case the casualty was given hospital file number instead of real identifiation number it is NOT A FOREIGN KEY.How can we come out of this maze??? |
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-06-09 : 19:24:24
|
It's a sad state of affairs when you need a database for this type of thing. |
|
|
CtrlAltDel
Starting Member
17 Posts |
Posted - 2004-06-10 : 07:41:04
|
Would this be a good place to use a surrogate key? (e.g. an identity field?) Since there doesn't appear to be a clear-cut choice? Additionally, if you used something like the person's id number, what happens if they get caught in multiple attacks? You wouldn't then be able to have their id as the primary key in the casualties table, as primary keys can't contain duplicates. Or perhaps the casualty file number - does each casualty get a unique file number?quote: It's a sad state of affairs when you need a database for this type of thing.
Too right, and my above reasoning would really suck when you have to consider the poor person who gets caught in multiple terrorist attacks...--"Life is like a sewer - what you get out of it depends on what you put into it"; Tom Lehrer |
|
|
|
|
|
|
|