2nd normal form and lots of joins

[Mike Hildner]

I'm attempting to normalize an incredibly flat table. I find myself moving several "person" (Name, DOB, Sex, Height et.) and "address" (Block, Street Name etc.) groups out of this table and instead use a pointer to a "Names" and an "Addresses" tables.

Seems like that's what 2nd normal form says to do, but now I have a table that consists mostly of pointers to records in other tables. I'll have to flatten out this table via a view so the user/app can make use of it. I read once (can't remember where) that one should avoid more than four joins in a view because of the overhead.

Should I be concerned about this? Using MS Sql 2000.

Thanks,
Mike

[techgnome]

If the data is laid out properly and indexed correctly, the number of joins is irrelevant.

The question you have to ask is if it makes sense. Having a table that is nothing but a pointer to the names and address table seems like a waste. Why not have a table that holds the name, then a pointer to the address. That way to get the address for a particular name, it's one join: name to address; rather than join from pointer to name and from pointer to address. Seems to me that would be more efficient.

Tg

[Mike Hildner]

If the data is laid out properly and indexed correctly, the number of joins is irrelevant.

That's good to know. I'll look at it and make sure it does make sense. The table is not solely comprised of pointers to other records, but there's quite a few of them (more than four anyway).

I can't just store the name, though, but that's another issue. Problem is, there's lots of people named John Smith, so you need something more unique than the name to identify a unique individual.

Thanks,
Mike

[szlamany]

We've designed dozens of applications for many, many years and have always had a DEMOGRAPHIC table of some sort.

Usually a MASTER ID (int column) or STUDENT ID (int column) is the primary key.

After that we have columns of demographic data that is associated with that person. NAME, SSN, BIRTH, TELEPHONE # - stuff like that.

Addresses move into an address table - primary key is MASTER ID+ADDRESS TAG (a two part primary key). The DEMOGRAPHIC record will have columns for the RESIDENT ADDRESS TAG and the MAILING ADDRESS TAG. If both are the same address, than both TAG values=1 and the ADDRESS table has just one row for that MASTER ID - with a TAG of 1.

We also end of with many child tables related to the MASTER or STUDENT ID. For instance we have an ENTRY/WITHDRAWAL table for when a kid comes and goes from school.

It's primary key is STUDENT ID+SCHOOL YEAR+ENTRY DATE. Those three fields make it unique and also nicely organize the rows into STUDENT order and YEAR order. In that table we have all the columns that are SCHOOL YEAR and ENTRY DATE related - such as BUILDING and GRADE.

Not sure if any of this helps - but it works well for us.

[Mike Hildner]

Actually it does help, thanks. You've always got useful info, szlamany, and I appreciate it.

[salvelinus]

If you had a many to many relationship between tables, then you'd need a linking table consisting of the primary keys from each table. That's the only case I can think of where you'd use one.