Multiple relationships defined to one table

[NuJoizey]

Hi - I have a question very similar to this one:

thread183-1373478

I have a field called "creatorID" and a field called "editorID" in tblPlacement each of which contain numeric values corresponding to "employeeID" in tblEmployee.

The problem is that currently there is no integrity enforced between the two tblEmployee and tblPlacement.

This means if someone deletes an employee leaving "creatorID" in tblPlacement an orphan, my application bombs out, and I have to manually go into update the offending record to a valid employeeID before it works again.

Creating a relationship from tblEmployee.employeeID > tblPlacement.creatorID and THEN AGAIN from tblEmployee.employeeID to tblPlacement.editorID seems not to be a good idea - or is it? - Or could this be accomplished by writing validation code or a trigger of some sort? It seems that with proper DB design, I shouldn't have to write validation code, should I?

What is the proper design to enforce integrity here?

 

[LNBruno]

Create a DELETE trigger for tblEmployee.


< M!ke >
Acupuncture Development: a jab well done.

 

[NuJoizey]

ok. i'll try that - thanks for the tip.

 

[NuJoizey]

just out of curiosity, what's wrong with doing the multiple join scenario? I've never seen it done, but I wonder what the rationale is.

 

[LNBruno]

Okay, first, I should finish my first cup of coffee before responding.

The DELETE trigger is ONLY okay if you don't mind losing the records in tblPlacement. I'm thinking now that probably isn't what you want. Some kind of business logic could be implemented to change the ID on tblPlacement for the to-be-deleted employee ID to some "un-associated" standard (null? fake id?) - or validation code which checks for the existance of the ID on tblPlacement and prompts the user to select a new ID to replace it if found and about to be deleted.

And, I think I need another cup of coffee because I have no idea how a multiple-join scenario fits into this posting...


< M!ke >
Acupuncture Development: a jab well done.

 

[NuJoizey]

thanks - i do understand the ramifications of the delete trigger, I'm trying to see if there is a "best practice"

i guess it's really not a mulitple join, technically it's defining more than one relationship between two entities, joining on more than one field between the two entities.

This is relevant because in the scenario an employee can a either a creator, and editor, or both within a particlar placement. The only way to force referential integrity between employee entity and the placement entity on these to fields is to either define a relationship between them, or write some sort of trigger, as you said, that deletes or alters the table to point to a "dummy" value if the employee gets deleted. It seems that either would work, yet the multiple relationship thing is kind of oddball.

I googled "multiple relationships" entity and found this paper that shows that it is legitmate - but the question remains - is it a good idea?

http://www.library.cornell.edu/elicensestudy/dlfdeliverables/fallforum2003/ERD_final.doc

 

[LNBruno]

Excellent paper. Thanks for sharing.

I think much of the problem is how "entities" are defined. For example, a company has customers, so you'd expect to have a customer table. Typically, it will contain the basic "name-address-city-state-zip-phone-fax" columns.

And then you get to the issue of different types of addresses: mailing, ship to, bill to...which opens the door for the "multiple relationships."

But what about the customer who owns/operates more than one business? Same customer name...but what about the addresses? What would you do if the mailing and bill to addresses were the same for both businesses, but the ship to were different?

That's just a simple example, but indicates why "the question remains."

< M!ke >
Acupuncture Development: a jab well done.

 

[NuJoizey]

well, in that case, I would create separate tables for addresses - that would be a firm 1>many relationship.

What I am dealing I think pretty much relates to "multiple relationships between entites" section, in which they write:

The diagram above indicates that an employee may be the salesperson assigned to zero or many clients, and an employee may be the customer service representative for zero or many clients. Each client has exactly one salesperson and exactly one customer service representative. Each client’s salesperson may or may not be the same employee as the client’s customer service representative; each relationship is treated independently

This is what is going on in my db with creator and editor, where each placement can have one and only one, and the creator and editor "IS-A" Employee

It just that it looks kind of funny to do it that way. I know "looking funny" is not a reason to do or not to do something, still, there's something to that rationale sometimes...

 

[JasonNevin]

I have a similar problem where I have three tables (tbl_user, tbl_site and tbl_service_centre) each of which has a unique set of fields but also a common set of address fields.

Surely the correct way to implement this model would be to have a single address table (tbl_address) with a one-to-one relationship to each of the others. If so how would I implement this using SQL Server? Would tbl_address have three foreign key fields (FK_tbl_user, FK_tbl_site and FK_service_centre) or would each my original tables habe a foreign key field pointing at tbl_address.

 

[r937]

Surely the correct way to implement this model would be to have a single address table (tbl_address) with a one-to-one relationship to each of the others.

that's not necessarily so


the rule of thumb regarding whether something should be an entity, and hence table: if you are interested in that thing regardless of its relationships

are you interested in a given address even if it has no user, no site, and no service centre? probably not

this indicates that address should be a non-key attribute of the other entities

yes, removing "redundancy" is one of the objectives that people have when designing databases

but just because two entities have an address, doesn't automatically mean that you have to extract the address into its own table

and besides, you aren't saving anything -- chances are that you don't have too many users, sites, and service centres sharing the same address, so there is no "redundancy"

another example: i betcha every employee database you ever saw has more than one employee with the firstname John -- is this enough reason to create a firstname table?



r937.com | rudy.ca

 

[JasonNevin]

I agree with all your points but still somehow it seems inefficient. Is it in any way inefficient i.e is it detrimental to the performance of the database? Is it even possible to build this model using standard SQL server methodlogy?

 

[r937]

seems inefficient" leads to premature optimization (do a search on that phrase -- it is to be avoided)

"standard sql server methodology?" i must've missed that meeting, and i didn't get the email either

;-)

r937.com | rudy.ca

 

[JasonNevin]

OK. points taken. Thanks.

 

[ESquared]

First of all, you should always use referential integrity to avoid meaningless values or orphaned rows being in the database. If you have to set all the references to an employee to null or the "no employee" code in another table before you can delete the employee that is GOOD. But there's a lot more to comment on.

• You shouldn't delete employees, especially if there is any historical or transactional information anywhere in the database that references the employeeid. If you want to blank out the name and make the employee identity-less, fine, if that's okay by your business rules. But you should be marking the employee as inactive, not deleting it.

• You should look up the supertype/subtype table method. You use it when different things share common information or need to relate to each other in interchangeable ways. For example, it's often used with employee/company/customer/vendor problems. Instead, you look at roles and types, splitting it out into person/company operating in roles employee/customer/salesrep and customer/vendor/reseller/etc. People and companies are all simply parties (it is recommended to avoid the word "entity" since it has other special meanings). You have one main Party table that has all columns in common between the types and is the "parent". Each new party is created first in this table. Then, each party type also has its own table with a 1 to zero-or-one relationship from the party table. Here's an example:

[tt]Parties
PartyID Type Name
1 Person Joe Blow
2 Company ACE Products
3 Person Jane Doe
4 Person Frank Billingsly
5 Company XYZ Design

Persons
PartyID DOB SSN ...
1 1/2/1934 123-45-678
3 4/5/1967 234-56-789
4 6/7/1989 345-67-890

Companies
PartyID TIN
2 987-65-4321
5 876-54-3210[/tt]

Instead of Persons starting at 1 and counting upward, using each number, Parties does this. And some of them are persons thus showing up in the persons table, and some are companies thus showing up in the companies table.

I don't really think of these as child tables, in any sense below the main table. To me they are peer tables, existing to the right of the main table, as extensions of the columns describing that single party. Companies and Persons are here mutually exclusive (although they don't have to be, if for some reason you needed to relate to a party in both ways).

You can add a sub-subtype set of tables as well. This might be the roles: Customers, Employees, Vendors, and so on. Each table has information in it that is specific only to that role, but if a party performs in more than one role you add it to each table now. What if your company wants to give special discounts to employees... how does it know that X customer is in fact Z employee?

What if you have a company that is both a Vendor and a Supplier? This happens all the time. Do you have to maintain two separate complete sets of information about them, plus a mapping table to show that they are the same?

One small issue: In the Persons table, note that you can call the ID column PersonID or PartyID. You have to decide which makes more sense to you. PartyID is what it REALLY is, but it could be confusing to someone to have that in the Persons table. But using PersonID can be just as confusing that it is really the exact same value as the PartyID. Experientially I find it better to use the same name, even though that breaks my preferred convention of using the name of the table as the name of the column. Treating it as a party throughout the database actually helps structure my thinking about it, too, so I get away from thinking of roles as entities.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[ESquared]

It looks like the article that you linked already discusses subtypes. Sorry to waste space, although perhaps the example will help someone. As for your original question:

Creating a relationship from tblEmployee.employeeID > tblPlacement.creatorID and THEN AGAIN from tblEmployee.employeeID to tblPlacement.editorID seems not to be a good idea - or is it?

I think it's a good idea to have referential integrity, even twice to the same table. Why not?

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[NuJoizey]

I think it's a good idea to have referential integrity, even twice to the same table. Why not?

thank you for your input on that one.

continuing on...

ok so you're defining an "is a" relationship between parties and persons and companies - but then shouldn't each of "persons" and "companies" include a party ID? that would make them "parent/child" - you said that you don't think of them this way, but i don't understand why you would say that.

 

[ESquared]

It's just the way I personally structure my mental spatial model for these and probably has no validity to anyone else. It makes more sense to me. They all do have a PartyID, but like in that article a party can't be in both tables at the same time, only one or the other. The subtype tables exist to extend the information about the party itself on a one-to-one basis--and the columns would be in the party table in the first place if there was only that type of party. Child/detail tables to me generally involve one-to-many relationships or a lack of dependency of the parent on the child.

Maybe an example is in order of how I see the supertypes & subtypes logically as one big table with missing columns depending on party type. If you did put this in one big table, you'd have to put NULLs in the empty spaces. Using the supertype/subtype model, the spaces just don't exist at all:

[tt]{[blue]Parties_________________________[/blue]} {[blue]Persons___________[/blue]} {[blue]Companies[/blue]}
PartyID Type Name DOB SSN TIN
1 Person Joe Blow 1/2/1934 123-45-678 [COLOR=white black] [/color]
2 Company ACE Products [COLOR=white black] [/color] 987-65-4321
3 Person Jane Doe 4/5/1967 234-56-789 [COLOR=white black] [/color]
4 Person Frank Billingsly 6/7/1989 345-67-890 [COLOR=white black] [/color]
5 Company XYZ Design [COLOR=white black] [/color] 876-54-3210[/tt]

I visualize them as not children, but peers. Children would be:

[tt][blue]Orders[/blue]
• OrderID OrderDate
+---LineNumber Product
• 1 8/15/2007
+--- 1 Toothbrush
+--- 2 Toothpaste
• 2 8/16/2007
+--- 1 Everlasting Gobstoppers[/tt]

SSN/TIN may actually be a bad example as they are basically the same thing. Perhaps move it to the party table with another column saying which one it is (individuals can have TINs as well as companies, now that I think about it). But imagine columns that only have to do with people or only with companies, but not both. How about... eye color and incorporation status.

DOB is actually another bad example as companies have a "birthdate" as well, at least a founding date, so these could be combined in the Party table. But I hope you get the idea.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]