Question about DB design for Frontpage site

  • Thread starter Thread starter c_halberstadt
  • Start date Start date
C

c_halberstadt

Howdy all,

First time poster, long time reader.

Anyway, I have a question about a db design I'm incorporating into a
Frontpage site for our company.

I have an innitial site developed linked to an access back-end
(although, we are going to an SQL Server 2000 db, but that is a
different issue altogether).

My question being, this page is going to have various links to reports,
such as IT, HR, payroll, etc. These relationships are all going to be
1-to-1, such as one employee can only have one IT record, one HR
record, one Payroll record, etc..

At first, this DB was only going to be one table, because it only
needed to house personal and IT information, so there were not that
many fields. However, now there is a growing number of fields, and
reports, that need to be processed.

How would I break this db up into multiple tables. I understand the
idea that these are 1-to-1, and I've always been under the impression
that with 1-to-1, it was better to have everything in just one table
instead of trying to reference others. However, this table is just
becoming to large to manage, and was hoping to somehow break it up into
multiple tables.

I hope I explained this well enough. If anyone has any suggestions, I
would be very grateful.

Thanks,

C
 
Hi C,

You can break it up into several tables. The Employee table would have a
unique key, such as an Autonumber field, which each other table would have a
foreign key field to indicate which Employee a given record belonged to.
However, it is more expensive to do a JOIN (multi-table) query than a simple
SELECT query, as the database must scan each table separately to get the
related records. So, unless you have a one-to-many or a many-to-many
relationship, one table *is* better.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Hard work is a medication for which
there is no placebo.
 
I would break it up into several tables - Employee, IT, HR, Payroll and have
one-to-many relationships, with Employee being the primary table - for a
couple of reasons....

First, as you've said, the large table is becoming hard to manage.

Secondly, absolutes in programming have a way of blowing up in your face.
When they say "one employee can only have one IT record", what they really
mean is "one employee can only have one IT record, until such time that we
decide they need two records.". Such time will occur at approximately the
same time that you complete coding for "only one IT record"

Add these words to your Red-Flag Alert list....
Always, Never, and Only.

Bob Lehmann
 
Back
Top