[Frunkie]

Hello everyone,

Is it ok to use one table such as phone and share that table for other entitles that also use a phone? I see it as a M:M relationship; a customer, for example can have many phones and a phone can be used by many people.

I only wanted to model it like this to cut down on the number of tables in the design. The importance of the phone numbers themselves is not important but the number of phone numbers a person has is.

I just created a join table between the complainant table and phone and then another join table between employee and phone. I would like to know if this is an acceptable design.

I have included a pic.

Thanks for the help.

~Frank

[r937]

the design looks fine, but in my opinion, it's overkill

i would keep the employee_phone table, as it is in a one-to-many relationship with employee

similarly, i would keep the complainant_phone table, as it is in a one-to-many relationship with complainant

but i would ditch the phone_type and phone tables

[Frunkie]

Originally Posted by r937

the design looks fine, but in my opinion, it's overkill

i would keep the employee_phone table, as it is in a one-to-many relationship with employee

similarly, i would keep the complainant_phone table, as it is in a one-to-many relationship with complainant

but i would ditch the phone_type and phone tables

Thanks Rudy, I was thinking the same thing but I just wanted to be sure.

[blindman]

Ditto.
I like the way you are thinking, but in this case it is overkill. I would only do this if the phone itself had additional attributes that I need to track (make, model, location, service record, etc). If you all want is the number, then you are causing unnecessary overhead when someone changes their phone number, because you will need to verify whether that number has changed for all users of that phone, or whether the person is simply using a different phone that now needs to be entered into the system.

[Frunkie]

Thanks for the input blindman. Yep, all I need is just the number and the type of phone it is such as cellular, office, etc. I think Rudy may have missed that in my post but I took his advice and seperated the common phone table. I am however sharing the phoneType table which is common to both the customer and complainant. The phoneType table will hold, cellular, office, etc. I think that would be ok like that.

My thoughts in doing the design like that were to end up with a design that had less tables. Also, I am starting to look at things in terms of objects instead which helps me, plus I quickly began to understand that the more tables I create, the more application code I have to write.

[r937]

do you have a surname table? if not, why not?

then answer the related questions... do you have a phone table? if so, why?

[Frunkie]

Originally Posted by r937

do you have a surname table? if not, why not?

No, no surname table. I always found it pointless to track that Jim Jones is a "Mister" and Sara Johnson is a "Ms." However, I could possibly see the value in a surname table if the database was designed to track mailing lists or that sort of thing.

Originally Posted by r937

then answer the related questions... do you have a phone table? if so, why?

I have the phone table because I wanted to track many different phone numbers of customers and complainants. Each type of person I would imagine could have a cellular, home, other and office phone. Without the type table, I can't see how I would add 3 or 4 different types of phones. Well... thats not true.. I could use area code and phone number as the PK but I wouldn't know what types of phones they were.

Am I wrong?

[r937]

i think you missed my point, but admittedly it wasn't very clear

it's not about the person's salutation, nor about whether the person is on a mailing list (which would require complete identification of the person, not just the surname)

if you have a person table, do you extract the surname out into a separate table?

CREATE TABLE surnames
( surname_id INTEGER NOT NULL PRIMARY KEY
, surname VARCHAR(37) NOT NULL
)
INSERT INTO surnames VALUES ( 1, 'Smith' )
INSERT INTO surnames VALUES ( 2, 'Jones' )
INSERT INTO surnames VALUES ( 3, 'Eck Settera' )

CREATE TABLE persons
( person_id INTEGER NOT NULL PRIMARY KEY
, givenname VARCHAR(37) NOT NULL
, surname_id INTEGER NOT NULL REFERENCES surnames ( surname_id )
, ...
)

reason why you should: many people can share a surname

reason why you should not: it would be silly overkill



your scenario with the phones is exactly the same, except the overkill isn't so obviously silly

i see surname as an attribute of person, not as a separate entity

this goes back to the age-old question of data modelling: what is an entity, and what is an attribute?

an entity is something that has identity in its own right

certainly, it might be quite acceptable that surnames have identity in their own right... for example, in genealogy database

but in normal business applications, you just wouldn't do it

so, what makes your phones so special that they need to be managed independently of the people that own them?

[mike_bike_kite]

Originally Posted by fjm1967

I only wanted to model it like this to cut down on the number of tables in the design.

I'd get rid of all the phone tables (goes from 6 tables to 2) and just have the following simple fields in employee and complainant :

• phone_work
• phone_mobile
• phone_home
• phone_fax

You'd get pretty much the same functionality except you wouldn't be able to add new types of phone numbers on the fly but having 2 tables rather than 6 means your coding effort is going to reduce. How often are you planning on adding new types of phone?

I also don't see how the relationship works between the employee and the complainant in your original diagram - how do you know what the complainant is complaining about (& if he has multiple complaints) - shouldn't there be a complaint table in the middle somewhere?

Is complainant a real word?

Mike

[r937]

Originally Posted by mike_bike_kite

You'd get pretty much the same functionality except you wouldn't be able to add new types of phone numbers on the fly

nor two mobile numbers

that design is over-simplified (and, as you pointed out, problematic)

nothing wrong with the one-to-many relationships

employees
employeephones
complainants
complainantphones

[pootle flump]

Anyone else find Mike's suggestion hilarious - not so much the suggestion per se but the context: that Mr EAV himself proposed it

[blindman]

Originally Posted by fjm1967

My thoughts in doing the design like that were to end up with a design that had less tables.

Fewer tables is not necessarily a good thing. It can actually slow down your application as it has to search through larger amounts of data in each table, or apply additional filters.

[blindman]

Originally Posted by pootle flump

Anyone else find Mike's suggestion hilarious - not so much the suggestion per se but the context: that Mr EAV himself proposed it

Yes, there was a certain irony in that, wasn't there?

[Frunkie]

I have a confession Rudy and I am embarrased to admit it but I was confusing surname with salutation. ahhh.. Ok, thats out of the way.

Originally Posted by r937

if you have a person table, do you extract the surname out into a separate table?

Absolutely not. I agree with you 100%. A person's surname is part of the identity of the table. I couldn't imagine doing that.

Originally Posted by r937

so, what makes your phones so special that they need to be managed independently of the people that own them?

Well, the only other reason was to guarantee data integrety. I thought I could use that phone_type table as a lookup table. I was actually thinking that maybe that would be better accomplished in the application code rather than adding a lookup table. What do you think?

[Frunkie]

Originally Posted by mike_bike_kite

I'd get rid of all the phone tables (goes from 6 tables to 2) and just have the following simple fields in employee and complainant :

• phone_work
• phone_mobile
• phone_home
• phone_fax

You'd get pretty much the same functionality except you wouldn't be able to add new types of phone numbers on the fly but having 2 tables rather than 6 means your coding effort is going to reduce. How often are you planning on adding new types of phone?

I also don't see how the relationship works between the employee and the complainant in your original diagram - how do you know what the complainant is complaining about (& if he has multiple complaints) - shouldn't there be a complaint table in the middle somewhere?

Is complainant a real word?

Mike

Mike, I'm not sure that design even qualifies as 1NF.

I have a complainant table actually below what I posted. I just cropped the image so that it wouldn't be so large. Complainant is a word that I believe exists in the industry where it is used I think.