A strange relationship...

From: Marco <nospam_at_nospam.no>
Date: 2000/07/21
Message-ID: <39786086.420797D1_at_nospam.no>#1/1

I an E-R schema, similar to this, where «» hold the primary key:

Pub(«ID_Home»,address,town)
Employee(«ID_Employee»,Name,Role,Hairstyle) Phone(«number»,KindOfPhone)

Now, each Pub has one or more phone numbers, just like every employee may have (one-to-N).

How can I realize such a relation?

Possible solutions (?):
A]

        If I had only, say, the Pub, I would have done this way, as the sacred books say:

	Phone(«number»,KindOfPhone,Pub)
	where Pub is the foreign key to ID_Pub: a piece of cake. 
	
	So I could put these two NULLABLE attributes on the phone relation:
	
	Phone(«number»,KindOfPhone,Employee,Pub)
	but this has two major drawbacks:
		1.those two nullable fields, in the middle of a Foreign Key relation
don't sound well...
		2.What if I need to add a phone number to the brand new LogCabin

table? No, I would like to have a simple reusable solution

B]

	I could do this
	Phone(«number»,KindOfPhone,ForeignKey,KindOfForeignKey)
	KindOfForeignKey(«KOFK_ID»,Kind)
	
	And implement in software the business rule which make the db point to

the right foreign key, may it be the Pub's one or the Employee's , the LogCabin 's or what else we want.

C]

        I could use intermediate tables as in the many-to-many (N-N) relationships, i.e.:

	PhonePub(«ID_PhonePub»,Phone,Pub)
	PhoneEmployee(«ID_PhoneEmployee»,Phone,Employee)
	to add the log cabin I could just add the PhoneLogCabin table.
	
	Clumsy. But not so semantically wrong how it may seem: if two employees

live together, they could share the same Phone number. I know a man living in a Pub! Maybe the original 1-N kind was wrong? What about this solution?         

C.1] Yes, but let's say we DO have two 1-N realtionsips: substitute Phone with SocialSecurityNumber, and LogCabin & Pub with Lumberjack & Waiter.         

D]         

your help...

Faithfully,
mario. Received on Fri Jul 21 2000 - 00:00:00 CEST