I am in the process of creating a custom CRM app for the company I work for right now. This question seems obvious, but the more I keep thinking about it the harder it seems to be. A contact database seems pretty simple, however normalizing it can get pretty complicated it seems.

Would it be proper to have many to many relationships or to duplicate data? A phone number can belong to more than one person in a database, a husband and wife or thier children. Same with their address. Or a person can have a work and home address etc. Of course we all have atleast three phone numbers now, home, cell and work. If not count yourself lucky. I will be working in MySQL 5 and would like to know how I should go about normalizing for this situation. The many to many route or just having duplicate data in my database?

You would think that there should just be some default schema's out there for routinely used database tables. It would be nice to just borrow one, but still. I really just want to do this right one time, not keep having to come back and redo. SO any help is greatly appreciated.

Thanks,

Fred

I'm a pretty strict adherent to normalized databases, and even I would not throw fits over duplicated phone numbers. Also bear in mind that a single phone may server for both voice and fax.

there is no benefit to be attained by "normalizing" phone numbers into a separate table

how come nobody ever asks whether it makes sense to normalize first names? it's the same thing

thus, you would only ever record "John" once, and every John would have a FK to that row in the firstnames table

normalized? yes, but so is the name itself

I "normalize" phone numbers into a separate table, because a person may have an indeterminate number of phone numbers. But I don't "normalize" the phone number table to make each phone number unique. Typically, I make the combination of Person/PhoneType/PhoneNumber a unique composite key.

That is another reason I would like to have a seperate table for the phone numbers, however, I do not know if it is worth the trouble. Although now the more I think about it the more I believe I will need a seperate table for the addresses also. Since I will potentially be tieing multiple phone numbers specifically to addresses. (Long Story, has to do with reverse searching addresses for phone numbers)

However, do you store a number or the actual type of the phone in the database?

I store both the number and the type, as you can see from my suggested composite key.

I store locations as a separate table, normalized, because many people may work at a single location but unlike phone numbers, geographic locations are not ephemeral.

You could have a surrogate key to the Firstnames lookup table. That way if ever you had to change everyone named "John" to "Jim", you would only have to change the value of one row in the lookup table.

But where do ya stop? Same for surnames and middle names? Birthdays?

pootle flump ur codings are working excelent.

For constants, you could have a surrogate key to the Constants lookup table. That way if ever you had to change the value of pi, for example, you would only have to change the value of one row in the lookup table.

That sounds like a stock phrase pulled out of the Rudy Limeback Final Word in Database Principles cupboard

pootle flump ur codings are working excelent.

wish i could lay claim to having come up with it myself, but i cannot

don't mind repeating it, though

I think you should have a table of phone number digits (0-9), each with a surrogate uniqueidentifier primary key. Then you would store the phone number as 10 separate columns of guids as foreign keys to the phone digits table.
This makes sense, because if you ever have to change all the "6"s to, say, "4"s, you would only have to change the phone digits table rather than all the individual phone numbers. A great savings in efficiency.

even the digits themselves aren't safe, because after all, what are they except encodings of 0s and 1s

lotsa duplication there!! normalize those suckers too, i say

Except for the fact that PI is a derived value. Which would be a recursive many to many relation between measurment. The recursive duple would point to the Radius and the Circumference of the measurement table for object type of circle. The value of PI would be derived from the ratio of these values. That way, if you wanted to change the value of PI to something more simple... lets say 3 to make calcluations easy, then you would just manipulate the values of the measurements of the diameter and the circumfrence of a circle and changing the value of PI would be simple.

You could also make the phone number table recursive. This way you could have parent and child digits.