To normalize or not to normalize? 3

[Qik3Coder]

So, I'm defining a structure that is going to be my focus for quite some time, so I want it to be supportable.

I have various entities with explicit relationships.
I'm defining the relations separate from the objects so that I can re-use the base components.

This would look like

Person --- PersonAddressRelationship --- Address
Person --- PersonPhoneRelationship --- Phone

PersonAddressRelationship looks like:
PAR_ID
PersonID
AddressID
RelationshipType

My question is:

Do I further normalize this structure so that all the relationships are in the same table, which some keys that define what entities they are for?


Person --- Relationship --- Address
Person --- Relationship --- Phone

Relationship looks like:
RelationshipID
SourceTableID
SourceTableKeyID
TargetTableID
TargetTableKeyID
RelationshipType

Right now I'm leaning towards explicit tables, because overly normalized structures are sometimes painful to code against.

TIA,
Lodlaiden

You've got questions and source code. We want both!

 

[Peager]

It looks like you are trying to support a 'many-to-many' relationship structure. I would go with the first choice as, even though it will require more 'relationship' tables, it will be cleaner to code against. I believe you will also find it cleaner to implement relational integrity with that structure as well. (If you are not planning using SQL relational integrity in your design I would strongly encourage it.) When naming your relating fields, you might want to use field names something like 'tablename_id' in both the parent and the child rather than just ID. As you get a lot of tables in a design, it's easy to get confused when you use names that are dissimilar between the parent and child.

 

[Qik3Coder]

Normally the relationships will be 1:many, though the child may exists multiple times (in the relationship table(s)) with different relationship types to different parents.

Standard Parent Child Normalization:
take a #10 25mm hex head bolt. It exists as a child/required/ancillary component to many parts in your car, but the system will only define the particular part 1 time.

My situation (faux data model):
Joe Employee
[tab]Printer Tickets - Printer #3 (Relationship Type 1)
[tab]DB Tickets - dbSomeSuch (Relationship Type 1)
[tab]Reports to - Sam Manager (Relationship Type 2)
[tab]Expertise - Forum Posting (Relationship Type 3)

The support thing is what I am most concerned about. I, and several others are going to be enhancing and fine tuning this thing for a while.

You've got questions and source code. We want both!

 

[Olaf Doschke]

Hi Lodlaiden,

RelationshipID
SourceTableID
SourceTableKeyID
TargetTableID
TargetTableKeyID
RelationshipType

It's a no go to let the same field be the foreign key of several tables per definition of source/target table ids and/or relationshiptype.

This is nowhere describes in any database theory I know. You can't define referential integrity in which the same field points to different tables in different records. This is not a normalisation form, this is simply dirty design.

Bye, Olaf.

 

[gmmastros]

Lod,

For what it's worth, I always opt for option 1 (separate relations tables).

For example, I have a student table, and an Address table. I also have a StudentAddress table with StudentId, AddressId, Description. The Primary Key for this table is StudentId and AddressId. You probably don't need a unique id column for the table because the combination of the other columns will be unique (unless you want to allow duplicates).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Qik3Coder]

Olaf,
I was indicating that I could reduce the total table imprint, by only having 1 relationship object/type.
I know I can't add multiple key relationships to a single column on a single table. Could probably add a check constraint calling a user defined function though... (j/k)

gmm,
I may have duplicates, that's what the RelationshipType is for. It mimics your Description field.

Because of the type of app I'm working on, the relationships are fixed, so I don't want the users typing "Personal" instead of "Home", or "Cell" instead of "Mobile"
I could get away with a unique key across all columns, but then I'll have some helpdesk tech with 2 Work phones.

Thanks for the input. Just wanted a sanity check.
Lodlaiden

You've got questions and source code. We want both!