Table desing problem

[Fernando]

Hi, im having trouble with the design of a Inspections Log (building permits) data base:

so ok,
Each City (tblCity) can have many Jobs
Each Job (tblJob) can have many Permits
Each Permit (tblPermit) can have many Inspections (tblInspections)

a Inspection has a InspectionType (ie: Partial, Final, etc)

Some Cities (like 20% of them) use a code system to schedule inspections by phone (ie: 001 for Final, 002 for Partial).
These codes are different between cities (ie: for Miami Beach: 101 for Final, City of Miami: 2001 for Final)

90% of the inspection types are the same for every city (ie: every city has a "Final", "Rough", etc inspections)
Some cities have more inspection types than others (ie:City of Miami has a "Partial Rough" inspections but City of Aventura doesn't)

So here is the thing,

I have 2 ideas of how to do this

The first one is would be to have a table tblInspectionType where it will be stored all the InspectionTypes and codes for each city (and of course the city ID)

but in doing this i will have lots of redundant data like 50 records with "Final" or "Rough" and lots of nulls in the Field for the codes

The second idea is to have 2 tables tblInspectionType and tblInspectionCode where one inspectionType has many inspectionCodes but i will still have the nulls since there are cities with no codes

so theres a third idea
create a field in tblInspection for the Code
store the tblInspectionType and tblINspectionCodes IDs on the inspection table so when theres no Inspection Code i will leave that new field blank but again i will have nulls and now and some kind of redundancy since the InspectionCode is stored on tblInspectionType

now that i read the null theread im scared, plz help

 

[KeithG]

I would create tblCodes and tblCityCodes. With a one to many relationship from tblCodes to tblCityCodes. tblCodes will contain every possible code and in tblCityCodes you can assc0iate CityID's with CodeID's. So you can assoiate one code with many cities. Would this work for you?

 

[Fernando]

Thx for your response KeithG
If the city uses Codes for InspectionTypes, a city has as many codes as the inspectiontypes they have, im sorry for not explaining it well.

a Code is "unique" for a Inspection Type and a City

A City may have codes for InspectionTypes for example

City of Miami:
Code - Inspection Type
001 - Final
002 - Slab
003 - Rough
004 - Pool

City of M Beach:
Code - Inspection Type
2212 - Final
2213 - Slab
2214 - Rough
2215 - Partial Rough
2216 - Pool

In the past example both cities uses a code system to identify the type of inspection (InspectionType), but as you can see both cities have some InspectionTypes in common like Final or Slab. And there are some InspectionTypes that not every city has like in City of M Beach they have a Partial Rough InspectionType but in City of Miami they don't

now theres another thing, There are cities (80% of them) that don't use codes at all like the following

City of Aventura
Code - Inspection Type
[None] - Final
[None] - Slab
[None] - Rough
[None] - Partial Rough

Now the thing is that there may be the possibility that these cities that doesn't use a code to identify inspections will start to use them somewhere in time.
Theres another possibility that this business may grow rapidly to northern counties so there may be a remote possibility that for a Duplicate Code but i don't think this would be a problem since i'm using Autonumbers as PKs in all tables

so when the user enters a new Inspection, when he picks the inspection type it will show the code depending on what City is the job from ( City < Job < Permit < Inspection ), to accomplish this is think the best way is:

tblInspectionType
InsID - InspectionType
1 - Final
2 - Rough
3 - PartialRough
4 - Pool
...

tblCity
CityID - City
1 - City Of Miami
2 - City Of M Beach
3 - City of Aventura
...

tblCode
CodeID - Code - CityID - InsID
1 - 001 - 1 - 1
2 - 2212 - 2 - 1
3 - Null - 3 - 1
4 - 002 - 1 - 2
.....

So i will store the CodeID on the Inspection Record so i can show the InspectionType from it but i will have lots of Nulls for the cities that don't use codes for it, i would like to know if theres a better o a right approach to this kind of problem?.