Database Design Problem

I have to keep detail of detail gender based DB like this where there may be
a number of incidents per state
Create table gender
(
State_id
Male_killed
Male_injured
male_affected
Female_killed
female_affected
female_injured
Child_killed
Child_injured
child_affected
Date
Incident_type
remarks
)
All these are numbers and can be 2 male_killed and 2 female injured in the
same incident
so what is the best way to desing a DB for this type I cannot think anything
other than putting these all into one table.
Depending on your needs you might come up with something like this...
Create table gender (gender_cd char(1) not null, gender_name varchar(8) not
null) --m, male f, female
Create table persontype( person_cd char(1) not null, person_name varchar(8)
not null) -A, Adult C,Child
Create table incidenttype(incident_id int identity(1,1) not null,
incident_name varchar(24) not null) --whatever
Create table resulttype(result_cd char(1) not null, resultname varchar(12)
not null) -- K, Killed I, Injured A, Affected
Create table states(state_cd char(2) not null, statename varchar(24) not
null) --NC, North Carolina
Create table things(id int identity(1,1) not null,
state_cd char(2) not null references
states(state_cd),
incident_id int not null references
incidenttype(incident_id),
thing_dt datetime not null,
remarks varchar(2000) null)
Create table thingdetail (id int identity(1,1) not null,
thing int not null references things(id),
gender_cd char(1) not null references
gender(gender_cd),
person_cd char(1) not null references
persontype(person_cd),
result_cd char(1) not null references
resulttype(result_cd),
NumberOfPeople int not null)
Although I did this quick and dirty, I think it is in third normal form...
You may not need this much normalization, but this is a beginning..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Anup" <anup_pokhrel@.hotmail.com> wrote in message
news:e$dztHBWEHA.2408@.tk2msftngp13.phx.gbl...
> I have to keep detail of detail gender based DB like this where there may
be
> a number of incidents per state
> Create table gender
> (
> State_id
> Male_killed
> Male_injured
> male_affected
> Female_killed
> female_affected
> female_injured
> Child_killed
> Child_injured
> child_affected
> Date
> Incident_type
> remarks
> )
> All these are numbers and can be 2 male_killed and 2 female injured in the
> same incident
> so what is the best way to desing a DB for this type I cannot think
anything
> other than putting these all into one table.
>
|||Based on your narrative and sample schema, one has to guess your conceptual
model, the attributes and the functional dependencies that exist among them
before suggesting something meaningful. In general, requesting solutions for
design problems in newsgroup may not be a worthwhile effort since there is a
fair chance that the problem could be misunderstood, miscommunicated or
misread and so are the suggested solutions.
With such a caveat, here is a try:
CREATE TABLE Incidents(
Incident_id INT NOT NULL PRIMARY KEY,
State CHAR(2) NOT NULL
REFERENCES States( State ),
Incident_date DATETIME NOT NULL,
Inident_type INT NOT NULL CHECK (...) -- or reference it
Remarks VARCHAR(8000) NOT NULL
DEFAULT '--NA--'
UNIQUE ( State, Incident_date, Inident_type) );
CREATE TABLE Details (
Incident_id INT NOT NULL
REFERENCES Incidents ( Incident_id )
Outcome VARCHAR(10) NOT NULL
CHECK ( Outcome IN ( 'Injured', 'Affected', 'Killed',
'Unknown' ) ) -- or reference it
Person_type VARCHAR(6) NOT NULL
CHECK Person_type IN ( 'Man', 'Women', 'Child', 'Unknown' ) )
-- or reference it
Incident_count INT NOT NULL DEFAULT(0)
PRIMARY KEY ( Incident_id, Outcome, Person_type ) ) ;
The check constraint values, if are of a higher number, can be substituted
with a referenced table with corresponding foriegn key constraint.
Anith
|||thnaks
but the only problem is that I have a file with 1000 records that comes in
and I am importing through excel so any suggestions asto how to handle it if
I am to adopt the above design considerations
Anup Help is awaited
thanks
anup
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uRQ16uFWEHA.2928@.tk2msftngp13.phx.gbl...
> Based on your narrative and sample schema, one has to guess your
conceptual
> model, the attributes and the functional dependencies that exist among
them
> before suggesting something meaningful. In general, requesting solutions
for
> design problems in newsgroup may not be a worthwhile effort since there is
a
> fair chance that the problem could be misunderstood, miscommunicated or
> misread and so are the suggested solutions.
> With such a caveat, here is a try:
> CREATE TABLE Incidents(
> Incident_id INT NOT NULL PRIMARY KEY,
> State CHAR(2) NOT NULL
> REFERENCES States( State ),
> Incident_date DATETIME NOT NULL,
> Inident_type INT NOT NULL CHECK (...) -- or reference it
> Remarks VARCHAR(8000) NOT NULL
> DEFAULT '--NA--'
> UNIQUE ( State, Incident_date, Inident_type) );
> CREATE TABLE Details (
> Incident_id INT NOT NULL
> REFERENCES Incidents ( Incident_id )
> Outcome VARCHAR(10) NOT NULL
> CHECK ( Outcome IN ( 'Injured', 'Affected', 'Killed',
> 'Unknown' ) ) -- or reference it
> Person_type VARCHAR(6) NOT NULL
> CHECK Person_type IN ( 'Man', 'Women', 'Child', 'Unknown' ) )
> -- or reference it
> Incident_count INT NOT NULL DEFAULT(0)
> PRIMARY KEY ( Incident_id, Outcome, Person_type ) ) ;
> The check constraint values, if are of a higher number, can be substituted
> with a referenced table with corresponding foriegn key constraint.
> --
> Anith
>
|||You have a couple of workarounds. Have the data Bulk inserted or DTS'ed to a
staging table with the same structure as the spreadsheet. Then you can write
up a simple script to populate the data into well normalized tables as
mentioned.
Another option is to use a client programming language like C or VB to read
the file & split the data accordingly. Then you can do the inserts directly
into the tables from the program.
Again, to reiterate the general sentiment, the table structures posted as
the response to your initial post is just a guildline and there is little
chance that your business model conforms to the assumptions we made while
posting such a schema.
Anith