Cross Referencing The Same Table

Hi all,
I have a PERSON table that stores personal info, and a RELATIONSHIP table
which just has a list of relationships.
Now I want to be able to define a family by means of a crossref table
between PERSON and PERSON using the RELATIONSHIP.
PERSON
PersonID Name Sex Birthdate
1 FRED M 1956/6/29
2 WILMA F 1959/4/18
3 PEBBLES F 1985/9/14
RELATIONSHIP
RelationshipID RelationshipText
1 SPOUSE
2 CHILD
3 PARENT
First off, is it best to create a record for every PERSON and different
RELATIONSHIP? That would mean the following is correct:
PERSON_PERSON
PersonID PersonID RelationshipID
1 2 1
2 1 1
1 3 2
2 3 2
3 1 3
3 2 3
This creates quite a few records... And if I add a 4th type:
4 SIBLING
And add a 4th child, "SANDY"
Then we get:
PERSON_PERSON
PersonID PersonID RelationshipID
1 2 1
2 1 1
1 3 2
1 4 2
2 3 2
2 4 2
3 1 3
3 2 3
3 4 4
3 1 3
3 2 3
3 3 4
Any thoughts on this? Should I just add a ReverseRelationshipID that
identifies what relationship is the inverse of another?Andrew
It seems that the relationship between two tabls is many-to-many , thus you
attempt to create a "junction table" , am I right?
Take a look at NorthWind database shipped by MS and relation between
Orders --Products and a junction table [Order Details
]
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OQwi81IgGHA.3996@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> I have a PERSON table that stores personal info, and a RELATIONSHIP table
> which just has a list of relationships.
> Now I want to be able to define a family by means of a crossref table
> between PERSON and PERSON using the RELATIONSHIP.
> PERSON
> PersonID Name Sex Birthdate
> 1 FRED M 1956/6/29
> 2 WILMA F 1959/4/18
> 3 PEBBLES F 1985/9/14
> RELATIONSHIP
> RelationshipID RelationshipText
> 1 SPOUSE
> 2 CHILD
> 3 PARENT
> First off, is it best to create a record for every PERSON and different
> RELATIONSHIP? That would mean the following is correct:
> PERSON_PERSON
> PersonID PersonID RelationshipID
> 1 2 1
> 2 1 1
> 1 3 2
> 2 3 2
> 3 1 3
> 3 2 3
> This creates quite a few records... And if I add a 4th type:
> 4 SIBLING
> And add a 4th child, "SANDY"
> Then we get:
> PERSON_PERSON
> PersonID PersonID RelationshipID
> 1 2 1
> 2 1 1
> 1 3 2
> 1 4 2
> 2 3 2
> 2 4 2
> 3 1 3
> 3 2 3
> 3 4 4
> 3 1 3
> 3 2 3
> 3 3 4
> Any thoughts on this? Should I just add a ReverseRelationshipID that
> identifies what relationship is the inverse of another?
>|||I know the relationship is many-to-many, and don't have a visualisation
problem when it's between 2 different tables, such as orders/products, but
in this case both sides of the cross-reference are the same table.
For example, the Northwind Order Details table has OrderID and ProductID...
But if I want to be consistant then my Family x-ref table would be PersonID
and PersonID. Can't do that.
So what I have done for the time being is create a Family table with a PK of
FamilyID and a FK of PersonID (which is where the family members name, sex
and birthdate would come from), and have a new x-ref table Person_Family
linking Person and Family, and including the RelationshipID.
This adds an extra table into the mix, so joins get a tad more complicated,
but it normalises OK.
In most cases I'm only interested in family of a certain person (namely an
employee).
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e7nZTQJgGHA.4004@.TK2MSFTNGP04.phx.gbl...
> Andrew
> It seems that the relationship between two tabls is many-to-many , thus
> you attempt to create a "junction table" , am I right?
> Take a look at NorthWind database shipped by MS and relation between
> Orders --Products and a junction table [Order Details
>
> ]
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OQwi81IgGHA.3996@.TK2MSFTNGP03.phx.gbl...
>|||Andrew
Well , have you conisdered creating a "tree" implemenation table
I took this example for Employees , however if it suites you can modified
it. Actually , I tried to give you an idea that may help you ,since i do
not know your business requirements
CREATE TABLE Employees
(
empid int NOT NULL, ( a person (personid))
mgrid int NULL, --a manager ( could be parent)
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OrYMkWKgGHA.4304@.TK2MSFTNGP05.phx.gbl...
>I know the relationship is many-to-many, and don't have a visualisation
>problem when it's between 2 different tables, such as orders/products, but
>in this case both sides of the cross-reference are the same table.
> For example, the Northwind Order Details table has OrderID and
> ProductID... But if I want to be consistant then my Family x-ref table
> would be PersonID and PersonID. Can't do that.
> So what I have done for the time being is create a Family table with a PK
> of FamilyID and a FK of PersonID (which is where the family members name,
> sex and birthdate would come from), and have a new x-ref table
> Person_Family linking Person and Family, and including the RelationshipID.
> This adds an extra table into the mix, so joins get a tad more
> complicated, but it normalises OK.
> In most cases I'm only interested in family of a certain person (namely an
> employee).
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e7nZTQJgGHA.4004@.TK2MSFTNGP04.phx.gbl...
>|||I use something similiar for handling positions in the company, but for
family I have to include not only parents, but spouse, kids, grandkids and
anyone else that could be considered a dependent of the employee, as that
has an affect on their tax.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23pquoeKgGHA.2040@.TK2MSFTNGP03.phx.gbl...
> Andrew
> Well , have you conisdered creating a "tree" implemenation table
> I took this example for Employees , however if it suites you can modified
> it. Actually , I tried to give you an idea that may help you ,since i do
> not know your business requirements
> CREATE TABLE Employees
> (
> empid int NOT NULL, ( a person (personid))
> mgrid int NULL, --a manager ( could be parent)
> empname varchar(25) NOT NULL,
> salary money NOT NULL,
> CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
> CONSTRAINT FK_Employees_mgrid_empid
> FOREIGN KEY(mgrid)
> REFERENCES Employees(empid)
> )
> CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
> INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
> INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
> INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
> INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
> INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
> INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
> INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
> INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
> INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
> INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
> INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
> INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
> INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
> INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
> GO
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OrYMkWKgGHA.4304@.TK2MSFTNGP05.phx.gbl...
>|||I would use an Employees table and a Dependents table.
While it is true that all of these folks are people, I would consider them
different entities. Dependents and emplyees, should not, in my opinion, be
in the same table. An employee that happens to be another employee's
dependent would exist as two seperate entities, and employee and a
dependent, and the two would not be associated.
So I would ditch the people table entirely, and create a dependents table
with (employeeID, dependentID) as the primary key. Store all your dependent
information there.
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:uNl%23$ALgGHA.4464@.TK2MSFTNGP04.phx.gbl...
> I use something similiar for handling positions in the company, but for
> family I have to include not only parents, but spouse, kids, grandkids and
> anyone else that could be considered a dependent of the employee, as that
> has an affect on their tax.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23pquoeKgGHA.2040@.TK2MSFTNGP03.phx.gbl...
modified
do
but
PK
name,
thus
message
different
>|||That's the way I had it before when it was just employees, but I'm going for
full normalisation now as I also have people that are customer/vendor
contacts, people that are candidates, etc.
Which is why I'm going for a person table, rather than 6 or more tables with
firstname, lastname, and fullname.
Maybe, down the road, after the design is done and we are doing testing, we
might denormalise portions of the database to improve performance, but I'd
rather not take the easy route at the beginning and find I lose flexibility
and functionality later on.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:e7kJKYNgGHA.3588@.TK2MSFTNGP04.phx.gbl...
>I would use an Employees table and a Dependents table.
> While it is true that all of these folks are people, I would consider them
> different entities. Dependents and emplyees, should not, in my opinion,
> be
> in the same table. An employee that happens to be another employee's
> dependent would exist as two seperate entities, and employee and a
> dependent, and the two would not be associated.
> So I would ditch the people table entirely, and create a dependents table
> with (employeeID, dependentID) as the primary key. Store all your
> dependent
> information there.
>
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:uNl%23$ALgGHA.4464@.TK2MSFTNGP04.phx.gbl...
> modified
> do
> but
> PK
> name,
> thus
> message
> different
>|||Actually, I think you are losing flexibility, functionality, and
maintainability now. Just because several entities share certain attributes
does not mean you should put them in the same table. I think you are
confusing good object oriented design with database normalization. The two
are quite different. You are going to have different rules surrounding
customers, contacts, dependents, and employees, and you will be able to
enforce these much simpler and more dependably if you treat them as separate
entities to begin with.
Your database will be properly normalized by having 6 different tables for
the different entities rather than trying to squeeze them all together into
a single table. Customers, employees, and dependents really have nothing in
common by a business standpoint.
I know I have seen articles discussing this approach, unfortunately I can
not seem to track any of them down at the moment.
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:e3aM7drgGHA.4404@.TK2MSFTNGP05.phx.gbl...
> That's the way I had it before when it was just employees, but I'm going
for
> full normalisation now as I also have people that are customer/vendor
> contacts, people that are candidates, etc.
> Which is why I'm going for a person table, rather than 6 or more tables
with
> firstname, lastname, and fullname.
> Maybe, down the road, after the design is done and we are doing testing,
we
> might denormalise portions of the database to improve performance, but I'd
> rather not take the easy route at the beginning and find I lose
flexibility
> and functionality later on.
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:e7kJKYNgGHA.3588@.TK2MSFTNGP04.phx.gbl...
them
table
that
i
message
visualisation
orders/products,
table
a
between
RELATIONSHIP
that
>|||I ran across some articles, and a couple of old SQL Server books, and yes,
I'm confusing OOD with database design. While at first glance it seems to
make sense, further research shows that, in this instance, it is better to
use seperate entities as you described.
Heck. I do it that way normally, but I was blinded by the idea of a person
as the primary entity, since all our products center around people
management.
I had thought it was going to create more work for me linking the various
people enties to the addresses, but a closer look shows that only employees
*need* to have more than one address.
Still, this is a huge undertaking, but at least now I can look at it
sensibly.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:u0dF31%23gGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Actually, I think you are losing flexibility, functionality, and
> maintainability now. Just because several entities share certain
> attributes
> does not mean you should put them in the same table. I think you are
> confusing good object oriented design with database normalization. The
> two
> are quite different. You are going to have different rules surrounding
> customers, contacts, dependents, and employees, and you will be able to
> enforce these much simpler and more dependably if you treat them as
> separate
> entities to begin with.
> Your database will be properly normalized by having 6 different tables for
> the different entities rather than trying to squeeze them all together
> into
> a single table. Customers, employees, and dependents really have nothing
> in
> common by a business standpoint.
> I know I have seen articles discussing this approach, unfortunately I can
> not seem to track any of them down at the moment.
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:e3aM7drgGHA.4404@.TK2MSFTNGP05.phx.gbl...
> for
> with
> we
> flexibility
> them
> table
> that
> i
> message
> visualisation
> orders/products,
> table
> a
> between
> RELATIONSHIP
> that
>|||Hmm. What about a Person supertype?
I would still have seperate enties for Employee, Candidate, Customer
Contact, Vendor Contact, User and Dependent but the data they share
(firstname, lastname, fullname, sex, address, email, phone) would only be in
Person.