|
Home > Archive > MS SQL Server > October 2006 > Relationships and normalization
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Relationships and normalization
|
|
| Nemisis 2006-10-24, 6:38 pm |
| 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 2006-10-24, 6:38 pm |
| 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.
>
| |
| David Browne 2006-10-24, 6:38 pm |
|
"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
| |
| Nemisis 2006-10-24, 6:38 pm |
|
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
| |
| Arnie Rowland 2006-10-24, 6:38 pm |
| 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
>
| |
| Anith Sen 2006-10-24, 6:38 pm |
| >> Nope. That is a correct relational design. Optional relationships are[color=darkred]
One might wonder what sort of integrity would such a nullable foreign key
constraint enforces !
--
Anith
| |
| David Browne 2006-10-24, 6:38 pm |
|
"Anith Sen" < anith@bizdatasolutio
ns.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
| |
| Anith Sen 2006-10-24, 6:38 pm |
| 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
| |
| David Browne 2006-10-24, 6:38 pm |
|
"Anith Sen" < anith@bizdatasolutio
ns.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
| |
| Anith Sen 2006-10-24, 6:38 pm |
| >> 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
| |
| David Browne 2006-10-24, 6:38 pm |
|
"Anith Sen" < anith@bizdatasolutio
ns.com> wrote in message
news:eloWbl78GHA.2092@TK2MSFTNGP03.phx.gbl...
>
> 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
|
|
|
|
|