Relationships and normalization

Hi everyone,
Hope ur all ok, i have some thoughts about our database design. The
database design was already constructed before i came to the company,
but we are now re-vamping the software so i would like to sort out the
database.
Here are short versions of 3 tables.
tblCompany
ID
Name
tblContact
ID
Name
CompanyID
tblProject
ID
Name
CompanyID
As you can see, a contact is apart of a company, and this rule is
always true. A contact cannot exist if they dont belong in a company.
Now to my query....
You can also see that tblProject CAN BE linked to tblCompany, but this
relationship doesnt have to exist, so a project can or cannot be linked
to a company.
Is it me, or should this type of relationship be in a separate table?
Something like tblProjectCompany, that would contain the ProjectID and
CompanyID, to the two that link together?
They have relationships like this all over the database, and we are now
trying to upgrade the software to use OOP in ASP.NET, and these tables
really make it hard to load data into an object, as you can tell each
project object, will contain a CompanyID, which just sounds wrong to
me.
Cheers for your help in advanced.
Hi
Yes , you will be better of creating a new table called tblProjectCompany
which contains ProjectID and CompanyID , you can create a primary key in
both columns assumning that one project id cannot belong to more than one
company , so it is up to you.
Remove CompanyOD from tblProject
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1161267973.119306.257200@.i3g2000cwc.googlegro ups.com...
> Hi everyone,
> Hope ur all ok, i have some thoughts about our database design. The
> database design was already constructed before i came to the company,
> but we are now re-vamping the software so i would like to sort out the
> database.
> Here are short versions of 3 tables.
> tblCompany
> ID
> Name
> tblContact
> ID
> Name
> CompanyID
> tblProject
> ID
> Name
> CompanyID
> As you can see, a contact is apart of a company, and this rule is
> always true. A contact cannot exist if they dont belong in a company.
> Now to my query....
> You can also see that tblProject CAN BE linked to tblCompany, but this
> relationship doesnt have to exist, so a project can or cannot be linked
> to a company.
> Is it me, or should this type of relationship be in a separate table?
> Something like tblProjectCompany, that would contain the ProjectID and
> CompanyID, to the two that link together?
> They have relationships like this all over the database, and we are now
> trying to upgrade the software to use OOP in ASP.NET, and these tables
> really make it hard to load data into an object, as you can tell each
> project object, will contain a CompanyID, which just sounds wrong to
> me.
> Cheers for your help in advanced.
>
|||"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1161267973.119306.257200@.i3g2000cwc.googlegro ups.com...
> Hi everyone,
> Hope ur all ok, i have some thoughts about our database design. The
> database design was already constructed before i came to the company,
> but we are now re-vamping the software so i would like to sort out the
> database.
> Here are short versions of 3 tables.
> tblCompany
> ID
> Name
> tblContact
> ID
> Name
> CompanyID
> tblProject
> ID
> Name
> CompanyID
> As you can see, a contact is apart of a company, and this rule is
> always true. A contact cannot exist if they dont belong in a company.
> Now to my query....
> You can also see that tblProject CAN BE linked to tblCompany, but this
> relationship doesnt have to exist, so a project can or cannot be linked
> to a company.
> Is it me, or should this type of relationship be in a separate table?
> Something like tblProjectCompany, that would contain the ProjectID and
> CompanyID, to the two that link together?
Nope. That is a correct relational design. Optional relationships are
correctly modeled by nullable foreign key columns. Using a separate linking
table is not terribly wrong, and you see it done sometimes for optional
One-to-Many relationships, but I don't like it because it introduces an
extraneous table into your model.
> They have relationships like this all over the database, and we are now
> trying to upgrade the software to use OOP in ASP.NET, and these tables
> really make it hard to load data into an object, as you can tell each
> project object, will contain a CompanyID, which just sounds wrong to
> me.
You can model this either as a null reference to a Company object or a
nullable int (.NET 2.0) for CompanyID.
David
|||Uri Dimant wrote:

> Hi
> Yes , you will be better of creating a new table called tblProjectCompany
> which contains ProjectID and CompanyID , you can create a primary key in
> both columns assumning that one project id cannot belong to more than one
> company , so it is up to you.
> Remove CompanyOD from tblProject
Uri,
Thanks for the quick reply. This may get confusing now, but within
tblProject we also have 2 ContactIDs, so tblProject looks like this
tblProject
ID
Name
CompanyID
ContactID1
ContactID2
Now, ContactID1 and ContactID2 should be contacts with the CompanyID
specified (hope this is clear lol). You have suggested moving
CompanyID into a new table called tblProjectCompany, which is good, but
what about the ContactIDs?
I am presuming that i should really create another table called
tblProjectContact and have a projectID and contactID in that table? Or
should this new table reference tblProjectCompany?
Something tells me that it should reference tblProjectCompany? I dont
know why, but hopefully you or someone here can tell me. lol
Cheers
|||If a Project is related to zero or one company, then your model is very
reasonable. It removes the 'hassle' of another table (for the sole purpose
of theoretical efficiency.)
However, it appears that you have a need for a ProjectContacts
table -instead of having multiple columns for contacts.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1161271610.913671.134410@.i42g2000cwa.googlegr oups.com...
> Uri Dimant wrote:
>
> Uri,
> Thanks for the quick reply. This may get confusing now, but within
> tblProject we also have 2 ContactIDs, so tblProject looks like this
> tblProject
> ID
> Name
> CompanyID
> ContactID1
> ContactID2
> Now, ContactID1 and ContactID2 should be contacts with the CompanyID
> specified (hope this is clear lol). You have suggested moving
> CompanyID into a new table called tblProjectCompany, which is good, but
> what about the ContactIDs?
> I am presuming that i should really create another table called
> tblProjectContact and have a projectID and contactID in that table? Or
> should this new table reference tblProjectCompany?
> Something tells me that it should reference tblProjectCompany? I dont
> know why, but hopefully you or someone here can tell me. lol
> Cheers
>
|||>> Nope. That is a correct relational design. Optional relationships are[vbcol=seagreen]
One might wonder what sort of integrity would such a nullable foreign key
constraint enforces !
Anith
|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#M2fw768GHA.2120@.TK2MSFTNGP03.phx.gbl...
> One might wonder what sort of integrity would such a nullable foreign key
> constraint enforces !
>
Is that a joke?
David
|||No David, nullable foreign keys, esp. when the keys are composite are
useless to enforce referentialy integrity. NULL references by itself is
meaningless.
Optional relationships are better represented using another table, since you
have little to loose in terms of integrity, but you have already stated your
dislike for an extra table.
Anith
|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eA2Q$Z78GHA.4012@.TK2MSFTNGP04.phx.gbl...
> No David, nullable foreign keys, esp. when the keys are composite are
> useless to enforce referentialy integrity. NULL references by itself is
> meaningless.
> Optional relationships are better represented using another table, since
> you have little to loose in terms of integrity, but you have already
> stated your dislike for an extra table.
>
Why?
Specifically in terms of "enforcing referential integrity", thy is this?
create table Address
(
ID int primary key,
AddressLine1 varchar(50),
AddressLine2 varchar(50)
)
create table Contact
(
ID int primary key,
Name varchar(50)
)
create table ContactAddress
(
ContactID int not null primary key
references Contact on delete cascade,
AddressID int not null
references Address
)
Better than
create table Address
(
id int primary key,
AddressLine1 varchar(50),
AddressLine2 varchar(50)
)
create table Contact
(
id int primary key,
Name varchar(50),
Address int null references Address
)
?
David
|||>> Specifically in terms of "enforcing referential integrity", thy is this?
In the second case, what exactly does the DBMS "enforce" when you
insert/update a NULL for the address column in the Contact table?
Anith

Relationships and normalization

Hi everyone,
Hope ur all ok, i have some thoughts about our database design. The
database design was already constructed before i came to the company,
but we are now re-vamping the software so i would like to sort out the
database.
Here are short versions of 3 tables.
tblCompany
ID
Name
tblContact
ID
Name
CompanyID
tblProject
ID
Name
CompanyID
As you can see, a contact is apart of a company, and this rule is
always true. A contact cannot exist if they dont belong in a company.
Now to my query....
You can also see that tblProject CAN BE linked to tblCompany, but this
relationship doesnt have to exist, so a project can or cannot be linked
to a company.
Is it me, or should this type of relationship be in a separate table'
Something like tblProjectCompany, that would contain the ProjectID and
CompanyID, to the two that link together'
They have relationships like this all over the database, and we are now
trying to upgrade the software to use OOP in ASP.NET, and these tables
really make it hard to load data into an object, as you can tell each
project object, will contain a CompanyID, which just sounds wrong to
me.
Cheers for your help in advanced.Hi
Yes , you will be better of creating a new table called tblProjectCompany
which contains ProjectID and CompanyID , you can create a primary key in
both columns assumning that one project id cannot belong to more than one
company , so it is up to you.
Remove CompanyOD from tblProject
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1161267973.119306.257200@.i3g2000cwc.googlegroups.com...
> Hi everyone,
> Hope ur all ok, i have some thoughts about our database design. The
> database design was already constructed before i came to the company,
> but we are now re-vamping the software so i would like to sort out the
> database.
> Here are short versions of 3 tables.
> tblCompany
> ID
> Name
> tblContact
> ID
> Name
> CompanyID
> tblProject
> ID
> Name
> CompanyID
> As you can see, a contact is apart of a company, and this rule is
> always true. A contact cannot exist if they dont belong in a company.
> Now to my query....
> You can also see that tblProject CAN BE linked to tblCompany, but this
> relationship doesnt have to exist, so a project can or cannot be linked
> to a company.
> Is it me, or should this type of relationship be in a separate table'
> Something like tblProjectCompany, that would contain the ProjectID and
> CompanyID, to the two that link together'
> They have relationships like this all over the database, and we are now
> trying to upgrade the software to use OOP in ASP.NET, and these tables
> really make it hard to load data into an object, as you can tell each
> project object, will contain a CompanyID, which just sounds wrong to
> me.
> Cheers for your help in advanced.
>|||Uri Dimant wrote:
> Hi
> Yes , you will be better of creating a new table called tblProjectCompany
> which contains ProjectID and CompanyID , you can create a primary key in
> both columns assumning that one project id cannot belong to more than one
> company , so it is up to you.
> Remove CompanyOD from tblProject
Uri,
Thanks for the quick reply. This may get confusing now, but within
tblProject we also have 2 ContactIDs, so tblProject looks like this
tblProject
ID
Name
CompanyID
ContactID1
ContactID2
Now, ContactID1 and ContactID2 should be contacts with the CompanyID
specified (hope this is clear lol). You have suggested moving
CompanyID into a new table called tblProjectCompany, which is good, but
what about the ContactIDs?
I am presuming that i should really create another table called
tblProjectContact and have a projectID and contactID in that table? Or
should this new table reference tblProjectCompany'
Something tells me that it should reference tblProjectCompany? I dont
know why, but hopefully you or someone here can tell me. lol
Cheers|||"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1161267973.119306.257200@.i3g2000cwc.googlegroups.com...
> Hi everyone,
> Hope ur all ok, i have some thoughts about our database design. The
> database design was already constructed before i came to the company,
> but we are now re-vamping the software so i would like to sort out the
> database.
> Here are short versions of 3 tables.
> tblCompany
> ID
> Name
> tblContact
> ID
> Name
> CompanyID
> tblProject
> ID
> Name
> CompanyID
> As you can see, a contact is apart of a company, and this rule is
> always true. A contact cannot exist if they dont belong in a company.
> Now to my query....
> You can also see that tblProject CAN BE linked to tblCompany, but this
> relationship doesnt have to exist, so a project can or cannot be linked
> to a company.
> Is it me, or should this type of relationship be in a separate table'
> Something like tblProjectCompany, that would contain the ProjectID and
> CompanyID, to the two that link together'
Nope. That is a correct relational design. Optional relationships are
correctly modeled by nullable foreign key columns. Using a separate linking
table is not terribly wrong, and you see it done sometimes for optional
One-to-Many relationships, but I don't like it because it introduces an
extraneous table into your model.
> They have relationships like this all over the database, and we are now
> trying to upgrade the software to use OOP in ASP.NET, and these tables
> really make it hard to load data into an object, as you can tell each
> project object, will contain a CompanyID, which just sounds wrong to
> me.
You can model this either as a null reference to a Company object or a
nullable int (.NET 2.0) for CompanyID.
David|||If a Project is related to zero or one company, then your model is very
reasonable. It removes the 'hassle' of another table (for the sole purpose
of theoretical efficiency.)
However, it appears that you have a need for a ProjectContacts
table -instead of having multiple columns for contacts.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1161271610.913671.134410@.i42g2000cwa.googlegroups.com...
> Uri Dimant wrote:
>> Hi
>> Yes , you will be better of creating a new table called
>> tblProjectCompany
>> which contains ProjectID and CompanyID , you can create a primary key in
>> both columns assumning that one project id cannot belong to more than
>> one
>> company , so it is up to you.
>> Remove CompanyOD from tblProject
>
> Uri,
> Thanks for the quick reply. This may get confusing now, but within
> tblProject we also have 2 ContactIDs, so tblProject looks like this
> tblProject
> ID
> Name
> CompanyID
> ContactID1
> ContactID2
> Now, ContactID1 and ContactID2 should be contacts with the CompanyID
> specified (hope this is clear lol). You have suggested moving
> CompanyID into a new table called tblProjectCompany, which is good, but
> what about the ContactIDs?
> I am presuming that i should really create another table called
> tblProjectContact and have a projectID and contactID in that table? Or
> should this new table reference tblProjectCompany'
> Something tells me that it should reference tblProjectCompany? I dont
> know why, but hopefully you or someone here can tell me. lol
> Cheers
>|||>> Nope. That is a correct relational design. Optional relationships are
>> correctly modeled by nullable foreign key columns.
One might wonder what sort of integrity would such a nullable foreign key
constraint enforces !
--
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#M2fw768GHA.2120@.TK2MSFTNGP03.phx.gbl...
>> Nope. That is a correct relational design. Optional relationships are
>> correctly modeled by nullable foreign key columns.
> One might wonder what sort of integrity would such a nullable foreign key
> constraint enforces !
>
Is that a joke?
David|||No David, nullable foreign keys, esp. when the keys are composite are
useless to enforce referentialy integrity. NULL references by itself is
meaningless.
Optional relationships are better represented using another table, since you
have little to loose in terms of integrity, but you have already stated your
dislike for an extra table.
--
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eA2Q$Z78GHA.4012@.TK2MSFTNGP04.phx.gbl...
> No David, nullable foreign keys, esp. when the keys are composite are
> useless to enforce referentialy integrity. NULL references by itself is
> meaningless.
> Optional relationships are better represented using another table, since
> you have little to loose in terms of integrity, but you have already
> stated your dislike for an extra table.
>
Why?
Specifically in terms of "enforcing referential integrity", thy is this?
create table Address
(
ID int primary key,
AddressLine1 varchar(50),
AddressLine2 varchar(50)
)
create table Contact
(
ID int primary key,
Name varchar(50)
)
create table ContactAddress
(
ContactID int not null primary key
references Contact on delete cascade,
AddressID int not null
references Address
)
Better than
create table Address
(
id int primary key,
AddressLine1 varchar(50),
AddressLine2 varchar(50)
)
create table Contact
(
id int primary key,
Name varchar(50),
Address int null references Address
)
?
David|||>> Specifically in terms of "enforcing referential integrity", thy is this?
In the second case, what exactly does the DBMS "enforce" when you
insert/update a NULL for the address column in the Contact table?
--
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eloWbl78GHA.2092@.TK2MSFTNGP03.phx.gbl...
>> Specifically in terms of "enforcing referential integrity", thy is this?
> In the second case, what exactly does the DBMS "enforce" when you
> insert/update a NULL for the address column in the Contact table?
>
Exactly the same thing it "enforces" when you delete a row from the
ContactAddress linking table.
And you didn't answer my question.
David

Relationships and normalization

Hi everyone,
Hope ur all ok, i have some thoughts about our database design. The
database design was already constructed before i came to the company,
but we are now re-vamping the software so i would like to sort out the
database.
Here are short versions of 3 tables.
tblCompany
ID
Name
tblContact
ID
Name
CompanyID
tblProject
ID
Name
CompanyID
As you can see, a contact is apart of a company, and this rule is
always true. A contact cannot exist if they dont belong in a company.
Now to my query....
You can also see that tblProject CAN BE linked to tblCompany, but this
relationship doesnt have to exist, so a project can or cannot be linked
to a company.
Is it me, or should this type of relationship be in a separate table'
Something like tblProjectCompany, that would contain the ProjectID and
CompanyID, to the two that link together'
They have relationships like this all over the database, and we are now
trying to upgrade the software to use OOP in ASP.NET, and these tables
really make it hard to load data into an object, as you can tell each
project object, will contain a CompanyID, which just sounds wrong to
me.
Cheers for your help in advanced.Hi
Yes , you will be better of creating a new table called tblProjectCompany
which contains ProjectID and CompanyID , you can create a primary key in
both columns assumning that one project id cannot belong to more than one
company , so it is up to you.
Remove CompanyOD from tblProject
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1161267973.119306.257200@.i3g2000cwc.googlegroups.com...
> Hi everyone,
> Hope ur all ok, i have some thoughts about our database design. The
> database design was already constructed before i came to the company,
> but we are now re-vamping the software so i would like to sort out the
> database.
> Here are short versions of 3 tables.
> tblCompany
> ID
> Name
> tblContact
> ID
> Name
> CompanyID
> tblProject
> ID
> Name
> CompanyID
> As you can see, a contact is apart of a company, and this rule is
> always true. A contact cannot exist if they dont belong in a company.
> Now to my query....
> You can also see that tblProject CAN BE linked to tblCompany, but this
> relationship doesnt have to exist, so a project can or cannot be linked
> to a company.
> Is it me, or should this type of relationship be in a separate table'
> Something like tblProjectCompany, that would contain the ProjectID and
> CompanyID, to the two that link together'
> They have relationships like this all over the database, and we are now
> trying to upgrade the software to use OOP in ASP.NET, and these tables
> really make it hard to load data into an object, as you can tell each
> project object, will contain a CompanyID, which just sounds wrong to
> me.
> Cheers for your help in advanced.
>|||"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1161267973.119306.257200@.i3g2000cwc.googlegroups.com...
> Hi everyone,
> Hope ur all ok, i have some thoughts about our database design. The
> database design was already constructed before i came to the company,
> but we are now re-vamping the software so i would like to sort out the
> database.
> Here are short versions of 3 tables.
> tblCompany
> ID
> Name
> tblContact
> ID
> Name
> CompanyID
> tblProject
> ID
> Name
> CompanyID
> As you can see, a contact is apart of a company, and this rule is
> always true. A contact cannot exist if they dont belong in a company.
> Now to my query....
> You can also see that tblProject CAN BE linked to tblCompany, but this
> relationship doesnt have to exist, so a project can or cannot be linked
> to a company.
> Is it me, or should this type of relationship be in a separate table'
> Something like tblProjectCompany, that would contain the ProjectID and
> CompanyID, to the two that link together'
Nope. That is a correct relational design. Optional relationships are
correctly modeled by nullable foreign key columns. Using a separate linking
table is not terribly wrong, and you see it done sometimes for optional
One-to-Many relationships, but I don't like it because it introduces an
extraneous table into your model.
> They have relationships like this all over the database, and we are now
> trying to upgrade the software to use OOP in ASP.NET, and these tables
> really make it hard to load data into an object, as you can tell each
> project object, will contain a CompanyID, which just sounds wrong to
> me.
You can model this either as a null reference to a Company object or a
nullable int (.NET 2.0) for CompanyID.
David|||Uri Dimant wrote:

> Hi
> Yes , you will be better of creating a new table called tblProjectCompany
> which contains ProjectID and CompanyID , you can create a primary key in
> both columns assumning that one project id cannot belong to more than one
> company , so it is up to you.
> Remove CompanyOD from tblProject
Uri,
Thanks for the quick reply. This may get confusing now, but within
tblProject we also have 2 ContactIDs, so tblProject looks like this
tblProject
ID
Name
CompanyID
ContactID1
ContactID2
Now, ContactID1 and ContactID2 should be contacts with the CompanyID
specified (hope this is clear lol). You have suggested moving
CompanyID into a new table called tblProjectCompany, which is good, but
what about the ContactIDs?
I am presuming that i should really create another table called
tblProjectContact and have a projectID and contactID in that table? Or
should this new table reference tblProjectCompany'
Something tells me that it should reference tblProjectCompany? I dont
know why, but hopefully you or someone here can tell me. lol
Cheers|||If a Project is related to zero or one company, then your model is very
reasonable. It removes the 'hassle' of another table (for the sole purpose
of theoretical efficiency.)
However, it appears that you have a need for a ProjectContacts
table -instead of having multiple columns for contacts.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1161271610.913671.134410@.i42g2000cwa.googlegroups.com...
> Uri Dimant wrote:
>
>
> Uri,
> Thanks for the quick reply. This may get confusing now, but within
> tblProject we also have 2 ContactIDs, so tblProject looks like this
> tblProject
> ID
> Name
> CompanyID
> ContactID1
> ContactID2
> Now, ContactID1 and ContactID2 should be contacts with the CompanyID
> specified (hope this is clear lol). You have suggested moving
> CompanyID into a new table called tblProjectCompany, which is good, but
> what about the ContactIDs?
> I am presuming that i should really create another table called
> tblProjectContact and have a projectID and contactID in that table? Or
> should this new table reference tblProjectCompany'
> Something tells me that it should reference tblProjectCompany? I dont
> know why, but hopefully you or someone here can tell me. lol
> Cheers
>|||>> Nope. That is a correct relational design. Optional relationships are[vbcol=seagreen]
One might wonder what sort of integrity would such a nullable foreign key
constraint enforces !
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#M2fw768GHA.2120@.TK2MSFTNGP03.phx.gbl...
> One might wonder what sort of integrity would such a nullable foreign key
> constraint enforces !
>
Is that a joke?
David|||No David, nullable foreign keys, esp. when the keys are composite are
useless to enforce referentialy integrity. NULL references by itself is
meaningless.
Optional relationships are better represented using another table, since you
have little to loose in terms of integrity, but you have already stated your
dislike for an extra table.
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eA2Q$Z78GHA.4012@.TK2MSFTNGP04.phx.gbl...
> No David, nullable foreign keys, esp. when the keys are composite are
> useless to enforce referentialy integrity. NULL references by itself is
> meaningless.
> Optional relationships are better represented using another table, since
> you have little to loose in terms of integrity, but you have already
> stated your dislike for an extra table.
>
Why?
Specifically in terms of "enforcing referential integrity", thy is this?
create table Address
(
ID int primary key,
AddressLine1 varchar(50),
AddressLine2 varchar(50)
)
create table Contact
(
ID int primary key,
Name varchar(50)
)
create table ContactAddress
(
ContactID int not null primary key
references Contact on delete cascade,
AddressID int not null
references Address
)
Better than
create table Address
(
id int primary key,
AddressLine1 varchar(50),
AddressLine2 varchar(50)
)
create table Contact
(
id int primary key,
Name varchar(50),
Address int null references Address
)
?
David|||>> Specifically in terms of "enforcing referential integrity", thy is this?
In the second case, what exactly does the DBMS "enforce" when you
insert/update a NULL for the address column in the Contact table?
Anith

Relational Desing Question

I am trying to desing a set of tables but I have come up against a
normalization problem and could use a spot of help.
Here is what I want to acheive -
TableDevices
--
DeviceId (PK), DeviceType
TableDeviceTypeADetails
---
DeviceId (PK), details columns.....
TableDeviceTypeBDetails
---
DeviceId (PK), details columns......
Basically, I want to create a master table to hold a reference to all
devices - of which can be of different types. The primary key is the
DeviceId. Each Device has a unique setup structure thus I want to
create a set of config tables for each device type and insert the
device details in the corresponding tables for that type yet uniquely
identified by the DeviceId in the master table.
The problem is that I get a one to one reltaionship between the master
table and each of the device setup tables.
This has to be a common problem and I cannot seem to find the correct
design pattern for this situation.
Thanks.On 21 Apr 2006 14:26:41 -0700, dubian wrote:
(snip)
>This has to be a common problem and I cannot seem to find the correct
>design pattern for this situation.
Hi Dubian,
There are two possible answers. (Actually, there are more - but these
are the two I'd recommend).
The fist is the simplest. (Note: in the DDL, I have to make assumptions
for datatypes - you'll have to change them to the correct types).
CREATE TABLE Devices
(DeviceId int NOT NULL,
DeviceType char(1) NOT NULL,
-- Other columns,
PRIMARY KEY (DeviceId),
CHECK (DeviceType IN ('A', 'B', 'C')
)
--
CREATE TABLE DeviceTypeADetails
(DeviceId int NOT NULL,
-- Other columns,
PRIMARY KEY (DeviceId),
FOREIGN KEY (DeviceId)
REFERENCES Devices (DeviceId)
)
and the same idea for the other device types.
And here's the second one. It's a little more comlpicated, but allso
more robust since it checks that you don't add type B's details for a
type A device (you can do that in the first version).
CREATE TABLE Devices
(DeviceId int NOT NULL,
DeviceType char(1) NOT NULL,
-- Other columns,
PRIMARY KEY (DeviceId),
UNIQUE (DeviceId, DeviceType),
CHECK (DeviceType IN ('A', 'B', 'C')
)
--
CREATE TABLE DeviceTypeADetails
(DeviceId int NOT NULL,
DeviceType char(1) NOT NULL DEFAULT 'A',
-- Other columns,
PRIMARY KEY (DeviceId),
CHECK (DeviceType = 'A')
FOREIGN KEY (DeviceId, DeviceType)
REFERENCES Devices (DeviceId, DeviceType)
)
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:5hli4292305ota0sujb7b9233g6r6bd82m@.4ax.com...
> And here's the second one. It's a little more comlpicated, but allso
> more robust since it checks that you don't add type B's details for a
> type A device (you can do that in the first version).
> CREATE TABLE Devices
> (DeviceId int NOT NULL,
> DeviceType char(1) NOT NULL,
> -- Other columns,
> PRIMARY KEY (DeviceId),
> UNIQUE (DeviceId, DeviceType),
> CHECK (DeviceType IN ('A', 'B', 'C')
> )
> --
> CREATE TABLE DeviceTypeADetails
> (DeviceId int NOT NULL,
> DeviceType char(1) NOT NULL DEFAULT 'A',
> -- Other columns,
> PRIMARY KEY (DeviceId),
> CHECK (DeviceType = 'A')
> FOREIGN KEY (DeviceId, DeviceType)
> REFERENCES Devices (DeviceId, DeviceType)
> )
Hugo,
Although this design is very familiar I've been unable to find a reference
for it in any textbook. Most examples (Pascal's and Date's for example)
don't mention any constraint at all or they just imply that the DBMS
enforces the rule of mutual exclusivity. Do you know of a published source
for the technique of using a compound foreign key and a unique constraint?
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Fri, 21 Apr 2006 23:26:44 +0100, David Portas wrote:
(snip)
> Do you know of a published source
>for the technique of using a compound foreign key and a unique constraint?
Hi David,
No. Sorry.
I've picked this up from one of Joe Celko's posts in the usenet groups
and memorized it in my list of usefull techniques. :-)
--
Hugo Kornelis, SQL Server MVP|||David Portas wrote:
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:5hli4292305ota0sujb7b9233g6r6bd82m@.4ax.com...
> > And here's the second one. It's a little more comlpicated, but allso
> > more robust since it checks that you don't add type B's details for a
> > type A device (you can do that in the first version).
> >
> > CREATE TABLE Devices
> > (DeviceId int NOT NULL,
> > DeviceType char(1) NOT NULL,
> > -- Other columns,
> > PRIMARY KEY (DeviceId),
> > UNIQUE (DeviceId, DeviceType),
> > CHECK (DeviceType IN ('A', 'B', 'C')
> > )
> > --
> > CREATE TABLE DeviceTypeADetails
> > (DeviceId int NOT NULL,
> > DeviceType char(1) NOT NULL DEFAULT 'A',
> > -- Other columns,
> > PRIMARY KEY (DeviceId),
> > CHECK (DeviceType = 'A')
> > FOREIGN KEY (DeviceId, DeviceType)
> > REFERENCES Devices (DeviceId, DeviceType)
> > )
> Hugo,
> Although this design is very familiar I've been unable to find a reference
> for it in any textbook. Most examples (Pascal's and Date's for example)
> don't mention any constraint at all or they just imply that the DBMS
> enforces the rule of mutual exclusivity. Do you know of a published source
> for the technique of using a compound foreign key and a unique constraint?
> --
> David Portas, SQL Server MVP
David,
I am not sure what you are looking for specifically, but just in case
you don't know: a foreign key must reference a unique (compound) key,
either with a UNIQUE constraint or PRIMARY KEY constraint. SQL-Server's
implementation will probably allow any uniquely indexed column(s).
The mutual exclusivity can be proven by the fact that the single column
(in this case DeviceId) is unique by definition, which guarantees that
there can be only one DeviceType value for that DeviceId.
Therefore, the referenced table does not need a check constraint. But to
get this to work, the referencing table does need a check constraint
with only one allowed value (and should not allow NULL).
There have occasions where I have advised this technique as far back as
2001 (maybe earlier).
HTH,
Gert-Jan|||Thanks Hugo - I'll give it a go.