Help with Best practie and design

HI all,
I covert your input.
I am designing a claims database. I am looking at have a common claims
header table, that will keep all common thing to every claim, and then
seperate tables depending on the type/class of claim. So I could have a
motor, marine, asset for example. Reason being that there is information
that is not common between these classes, Eg. driver ID is only required for
motor, vessel name is only required for Marine etc. I don't want to have one
large claims table, with as many columns as there are different permutaions
of types of data for each claim class
My question is what is the best way to set this up. How wold I have a parent
common claims table, with say, a motor, marine, asset child table. How would
I relate these three table to the parent common claims table, because
obviously there will be rows in the parent that only have one corresponding
row in one of the other chiold tables, and never more than one corresponding
table.
Am I making this clear
THanks
RobertRobert
CREATE TABLE Parent_Claims
(
ParentId INT NOT NULL PRIMARY KEY,
ParentName VARCHAR(n) NOT NULL
....
)
CREATE TABLE Type_Claims
(
TypeId INT NOT NULL PRIMARY KEY,
TypeDescr VARCHAR(n) NOT NULL
...
)
CREATE TABLE Parent_Types
(
PT_ID INT NOT NULL PRIMARY KEY,
ParentID INT NOT NULL FOREIGN KEY REFERENCES Parent_Claims (ParentId ),
TypeId INT NOT NULL FOREIGN KEY REFERENCES Type_Claims(TypeId ),
)
Note: I don't know your business requirements hence tried to give a common
solution
"Robert Bravery" <me@.u.com> wrote in message
news:eOj7Ub6NGHA.2916@.tk2msftngp13.phx.gbl...
> HI all,
> I covert your input.
> I am designing a claims database. I am looking at have a common claims
> header table, that will keep all common thing to every claim, and then
> seperate tables depending on the type/class of claim. So I could have a
> motor, marine, asset for example. Reason being that there is information
> that is not common between these classes, Eg. driver ID is only required
> for
> motor, vessel name is only required for Marine etc. I don't want to have
> one
> large claims table, with as many columns as there are different
> permutaions
> of types of data for each claim class
> My question is what is the best way to set this up. How wold I have a
> parent
> common claims table, with say, a motor, marine, asset child table. How
> would
> I relate these three table to the parent common claims table, because
> obviously there will be rows in the parent that only have one
> corresponding
> row in one of the other chiold tables, and never more than one
> corresponding
> table.
> Am I making this clear
> THanks
> Robert
>|||Hi Uri,
Thanks
Yes I got that. But now added those two extra tables, that are not common,
how would I relate them to the commaon parent clain;
CREATE TABLE [dbo].[Marine] (
[MarineID] [int] NOT NULL ,
[VesselName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Motor] (
[MotorID] [int] NOT NULL ,
[DriverID] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Parent_Claims] (
[ParentId] [int] NOT NULL ,
[ParentName] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Type_Claims] (
[TypeId] [int] NOT NULL ,
[TypeDescr] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Parent_Types] (
[PT_ID] [int] NOT NULL ,
[ParentID] [int] NOT NULL ,
[TypeId] [int] NOT NULL
) ON [PRIMARY]
GO
THanks
Robert
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#H#OXu6NGHA.208@.tk2msftngp13.phx.gbl...
> Robert
>
> CREATE TABLE Parent_Claims
> (
> ParentId INT NOT NULL PRIMARY KEY,
> ParentName VARCHAR(n) NOT NULL
> .....
> )
> CREATE TABLE Type_Claims
> (
> TypeId INT NOT NULL PRIMARY KEY,
> TypeDescr VARCHAR(n) NOT NULL
> ....
> )
> CREATE TABLE Parent_Types
> (
> PT_ID INT NOT NULL PRIMARY KEY,
> ParentID INT NOT NULL FOREIGN KEY REFERENCES Parent_Claims (ParentId ),
> TypeId INT NOT NULL FOREIGN KEY REFERENCES Type_Claims(TypeId ),
> )
>
> Note: I don't know your business requirements hence tried to give a
common
> solution
>
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:eOj7Ub6NGHA.2916@.tk2msftngp13.phx.gbl...
>|||> My question is what is the best way to set this up. How wold I have a
> parent
> common claims table, with say, a motor, marine, asset child table. How
> would
> I relate these three table to the parent common claims table, because
> obviously there will be rows in the parent that only have one
> corresponding
> row in one of the other chiold tables, and never more than one
> corresponding
> table.
> Am I making this clear
create table claim (
claim_id int not null,
...
constraint [pk_claim] primary key nonclustered
(claim_id)
)
create table motor_claim (
claim_id not null,
...
constraint [pk_motor_claim] primary key nonclustered
(claim_id)
)
alter table motor_claim
add constraint [fk_blah] foreign key (claim_id)
references dbo.claim (claim_id)
This supports a 1-to-1 relationship between claim and any particular child
type table. It does NOT limit a claim to having a single row in a single
child table. For that, you would need another constrainting device (e.g.,
trigger, indexed view).|||Look up some of my old postings about "faking" class hierarchies in
SQL. Then read why this is a horrible approach to RDBMS.
Also, there is no such data element as a "type_id". An attribute can
be a *particular* kind of identifier or a *particular* kind of type.
It cannot be both and it cannot be so vague. Think about it for a
second.|||--CELKO-- wrote:
> Look up some of my old postings about "faking" class hierarchies in
> SQL. Then read why this is a horrible approach to RDBMS.
Do you mean your "CREATE TABLE Vehicles" postings? Where can I read
your critique of this approach?
Thanks,
Jamie.