A strange relationship...
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 LogCabintable? 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 tothe 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 employeeslive 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