normalization of complex tables

cldeans

Firstly, I want to say how great I think the article on normalization is - for the most part I found it very clear. There were a few parts where I wasnt completely sure what was intended, but for the most part, it was great.

One area that I found hard to understand was the final stage 5 of normalization .. where it was suggested that the initial table be reconstructed from the normalised information .. i am not sure what is intended here, and hopefully someone can help, becs I think that my tables need this final stage to work efficiently.

So .. to the problem .. I have a very large table of data on different organisations and their contact detail, with a lot of fields that will not necessarily be used by every organisation, but with sufficient numbers requiring them that they need to be included. It also includes organisation that have numbers of branches or departments, and occassionally branches with different departments (with different functions and different contact details).

The number of organisations is expected to exceed 100,000, and so scalability is vital.

These are the fields I need (and please, refrain from questioning my choice unless you have a better way of doing it .. I have been collecting the data for 2.5 years, and they are ALL required)

organisation name
organisation branch
organisation dept
service description
keywords
category
date entered
last checked

location address1
location address2
location suburb/city
location state/province
location postcode
location country

postal address1
postal address2
postal suburb/city
postal state/province
postal postcode
postal country

contact name
email
url
voice phone
tty/ttd phone
fax
freecall voice
freecall tty/ttd
freecal fax
mobile/cell
pager

Now... obviously things like postcode, suburb name etc would all benefit from a table with them, and given an id, to save space ... so..... this is how far i have gone .. I am feeling that it is incredibly complex, but having followed the rules of normalisation 1 - 4, this is what I have.....(*primary key)

tbl_uniquelisting

fld_ulid*
fld_orgname
fld_deptname
fld_branchname
fld_description
fld_entered
fld_lastchk
fld_keyword
fld_url
fld_email
fld_contactperson
rel_phonelistid

tbl_loc

rel_ulid*
fld_locst
fld_locst2
rel_placeid
rel_countryid

tbl_post

rel_ulid*
fld_post1
fld_post2
rel_placeid
rel_countryid

tbl_place

fld_placeid*
rel_suburbid
rel_stateid

tbl_suburb

fld_suburbid*
fld_suburbname

tbl_state

fld_stateid*
fld_statename

tbl_country

fld_countryid*
fld_country

tbl_phonetype

fld_phoneid*
fld_phonetype

tbl_phonelisting

tbl_phonelistid*
rel_ulid
rel_phoneid

tbl_categories

fld_catid*
fld_cattype

tbl_ulcat

fld_ulcatid*
rel_catid
rel_ulid

Think that's it ... comments?

Cate

tomhath

Two comments on the design and then a suggestion on how to hide the complexity.

1) Your location and postal tables are essentially identical. Consider adding a "LocationOrMailingOrBoth" flag. Then you only need to maintain it once. Also add an EffectiveDate field(addresses change all the time).

2) The use of a phone list is good but you don't need the phone list table, but instead of a list ID just use the fld_ulid; consider using the same idea for contacts, urls, emails, etc. You'll always want lists of those (again with EffectiveDates). You'll probably also want separate lists of phones, emails, etc for the contacts within a fld_ulid.

Now to put all of this together you need to hide the complexity of the table structure and isolate the database design from the rest of your application (so when, not if, the database changes your other code won't need to be updated).

Choices here are Stored Procedures and Views. You'll probably end up with some of each (Procs for inserts and updates or really complicated selects, views for the simpler selects). Avoid having your application code refer directly to the underlying tables in case you need to add some more lists, denormalize some tables for speed, or do additional normalization.

nemonoman

May I share a couple of thoughts?

I am a normalization fan. As a designer normalizes data, additional capablities of an application suggest themselves, seemingly out of nowhere, and often the implementation of apparently difficult tasks is quite simple.

On the other hand, normalization can make an apparently simple task -- such as updating a phone number or searching for a mailing address -- rather complex.

Normalization is a discipline and a method, but not necessarily the only discipline nor the best method for solving all problems.

Particularly when using a db like MySQL (in the version I have at least), which hasn't got VIEWS aor STORED PROCEDURES, it is incumbent on the designer to distinquish between the highly normalized LOGICAL model, and the IMPLEMENTED model, which may be significantly LESS normalized.

I developed a huge, highly normal Oracle db for a major telecom manufacturer, designed to replace and interrelate data from about 7 different legacy dbs. I was one of about 10 leads of a team of 500-600 programmers, working from an LDM that ran to 60 pages. We wanted a db that could live for 7-10 years without major rework. In this environment, it was often best that we idealize a table: not phone number, but electronic contact with columns like electronic contact type, electronic contact identifier, electronic contact prefix requirement, electronic contact valid start date, electronic contact end date,
etc.
etc.

With a large IT staff to document the LDM, to write views and stored procedures, to document and enforce proper usage, etc., etc., this was pretty strong and useful stuff.

This thinking infects one's brain. The going joke among us was an attempt to create an entirely normalized database to handle all possibilities, consisting of merely two tables: Thing and Thing Relationship. (If you think about it you might conclude that this conceit represents the ultimate endpoint of the 4th normal form, and in effect describes a computer's memory manager).

If you don't have Views and Stored procedures, if you don't have a cast of thousands, if you are writing a program yourself and hoping someday to pass off maintenance to another, then I recommend backing off the normalization throttle just a little.

If you decide that you intend only to collect home, business and cell phone numbers, there's no crime in blocking out 3 fields to do so.

One of the OK words our Data Modellers used was "Collapsing" the data model: Where there is a strict hierachical relationship (one person, many phone numbers), one collapses and removes the child table by creating column(s) in the parent table. This is less fluid, but more maintainable.

Balance your and your users' need for maintainance vs. fluidity and you'll do OK.

Nemo

tomhath

it is incumbent on the designer to distinquish between the highly normalized LOGICAL model, and the IMPLEMENTED model, which may be significantly LESS normalized.

Yup. One of my favorite quotes (sorry I don't know the author):

"The price of perfection is bankruptcy"

You can err by going too far in either direction. I would suggest that the tbl_state, tbl_suburb, and tbl_country tables in the original post are overkill. But I'd be real careful about collapsing a many-to-one child table down into an array inside a row.

cldeans

tomhath .. thanks for your comment .. I like your first suggestion, makes sense. About the second one..."2) The use of a phone list is good but you don't need the phone list table, but instead of a list ID just use the fld_ulid; consider using the same idea for contacts, urls, emails, etc. You'll always want lists of those (again with EffectiveDates). You'll probably also want separate lists of phones, emails, etc for the contacts within a fld_ulid"

Are you suggesting that i dont have an identifier id for these tables? I did think of this at first, but then added the id field becs there will be more than one phone number for each organisation and i thought that it was generally good practice to have at least one unique number.

And Nemo ...

"If you decide that you intend only to collect home, business and cell phone numbers, there's no crime in blocking out 3 fields to do so. "

I'm not sure what you mean ... are you suggesting I limit the phone numbers I collect (not really practical for the purposes of this database), or suggesting i dont worry about the empty spaces in all half dozen of the contact type fields?

Thanks all for taking the time to respond .. will take it all onboard.

Cate

cldeans

tomhath

sorry, I missed the second post ..

"You can err by going too far in either direction. I would suggest that the tbl_state, tbl_suburb, and tbl_country tables in the original post are overkill. "

At what point (ie, how many times would, for example, "New York" have to appear in the database before it was no longer overkill?

Another reason, other than being repeated so many times, was that I want to keep the input of cities and states, etc, standard .. I dont want to have one as 'NEW YORK' and another as "New York"

Still overkill?

Cate

tomhath

As far as the phone list, I'd use a table something like this. The contactID would be null for a switchboard (no specific contact). I don't see a need for a separate table with a listid, the list is implied by whatever phone numbers link back to the organization with the rel_uid

fld_phoneid*
rel_ulid

rel_contactid
phone number fields (typeid, area code, number, extension, etc)
from/thru dates

The design of the other tables you mention is good from a purist's point of view for all the reasons you mention. But as nemonoman pointed out, the queries become increasingly complex (and slow) when you have to join several tables together to get an address; cheating a little by denormalizing makes the query simpler, but the tradeoff is the non-standard identification of any one item (NY vs New York vs NEW YORK). Your choice, there's no absolute right or wrong...

nemonoman

You wrote:

are you suggesting I limit the phone numbers I collect (not really practical for the purposes of this database), or suggesting i dont worry about the empty spaces in all half dozen of the contact type fields?

Yes. One of my data modellers was the head of the DB dept. He wanted to have addresses created on a table

person_address_line

which would be a child of person_address which was itself a child of person

cols were something like:
person_address_id(FK)
person_address_line_type(FK)
person_address_line_sort_order

There were then 4 or 5 child tables of different types:

person_address_line_street_type
person_address_line_box_type
person_address_line_delivery_location_type
person_address_line_city_type

etc

The guy looked at me like I was crazy when I asked WTF we were supposed to do with this GD mess. (WTF and GD are technical terms associated with writing SQL and application code on highly normalized DBs).

His response was that this prevented any blank lines in addresses.

We would write stored procedures and multiple views and thus mask the complexity of the pure design from the user community.

At a time when storage is going for about $1 a Gigabyte, I think it's an easy analysis: What does it cost to store a blank field in a column? What does it cost to maintain a completely consistent data model.

Your NEW YORK vs. New York item is a good case in point. If it's easy to deliver that functionality to your users through dropdowns, etc., and you know it is or you wouldn't be writing into a PHP forum, by all means maintain it.

Trying to deliver the same dropdown functionality with city names, for example, is a non-starter.

Sure Codd and Date may cringe that there are 50 instances of Springfield in the Database, and that there are even more examples that are probably misspellings. As many application coders have said about this horrible problem, WTF. Do the search on the zipcode field. Work around it. Live with it.

As George S Patton (reputedly) said:

"A good plan violently executed today is better than a perfect plan tomorrow."

cldeans

Thanks Nemo and Tom for your input...

Basically what you are saying is "Good, little database student ... now that you know the rules, you can go break them!" ... which I take as a compliment, becs I wasnt sure I had learned the rules yet! Thanks. And I guess this is the difference between theory and practical, right? lol!

And I will be sure to put "GD" and "WTF" in my little book of notes, to make sure I look up these terribly technical phrases, so I dont look too much like a newbie later! LOL! (tongue held firmly in cheek!)

Yes, I agree about the complexity issues. As is obvious, I am learning, and thats why I wanted feedback. The purpose has always been to 'optimise', but as this is the first database of any size that I have built, it is always hard to know how far you bend the rules. The comments you have provided have given me some guide, so thanks again.

Have a great day!
Many regards
Cate