brain cramp - 1 table or 2?

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I am supposed to design a database for tracking subcontractors license
requirements for a construction company. Years ago I did this in FileMaker
and it works fine, but it is not a relational database.

The problem is that many values in the fields are calculated by dates of
other fields and some fields have values that sometimes are null because of
the data in specific fields.

For example: Oregon contractors are required to have a license and Workers
Comp insurance. Washington contractors don't need workers comp because that
is part of their license. IF the contractor is licensed in Washington only
but is also licensed in Oregon, they don't need workers comp because it is
covered in the Washington license.
When it is all done, whether they are approved or not for use by the company
is determined by these factors and the expiration dates of various licenses.
The "approved" word needs to be displayed to the end user looking at the
database.

I have one table with all the contractors info and other lookup tables for
the information such as business type, etc. Should I have 2 tables for
contractors, 1 oregon and 1 washington? seems that that would be repetitive
and not "normalized".

Any thoughts appreciated,
Rob
 
I would have 3 tables: 'Contractor', 'State Requirements'
and a 'Licence' table linking Contractor and State
For each Contractor you would then look in Licence to see
the states licenced in and then in the State table to see
the requirements.

Decide what columns are needed in each table, then design
the links in Table Designer.

An example of the contents would be:
Contractor table
Contractor A
Contractor B

Licence table
Contractor A State CA
Contractor A State AZ
Conttactor B State NY

State table
AZ ...
CA ...
NY ...
etc

- Dorian
 
During your thinking process bring into the
picture "Normalizing of Tables". From your description
you will nedd at least 3 tables and probably more to get
them all normalized. This is not a hard problem, but if
you don't normalize your tables the DB will be difficult
to operate and won't provide the results you are looknig
for. If you need more info about normalization search
the Microsoft knowledge Base.

Cheers,
Henry
 
Back
Top