Database Design

[Outatym]

I am trying to set up a DB that users can specify the criteria they want to
search by on a form, then click a command button...and a report is generated
from the corresponding query. This DB is going to be used by our producers to
search for Insurance Carriers for our clients, which in itself is a daunting
task. I am having trouble deciding on the relationships between the tables I
have set up. Here is what I have so far...


**************************************************************************************
tblTruckingMarkets
-MKTID (PK)
-TruckingCoverageID (FK to tblTruckingCoverage)
-NewVenturesID (FK to tblTruckingNewVentures)
-OperationsTypeID (FK to tblTruckingOperationsType)
-TruckingUnitsID(FK to tblTruckingUnits)
-CargoTypeID (FK to tblTruckingCargoType)
-EquipmentTypeID (FK to tblTruckingEquipmentType)
-RadiusID (FK to tblTruckingRadius)
-StateID (FK to tblTruckingState)

tblTruckingCoverages
-TruckingCoveragesID (PK) (Number)
-TruckingCoverage (Text)

tblTruckingNewVentures
-NewVenturesID (PK) (Number)
-AcceptNewVentures (Text)

tblTruckingOperationsType
-OperationTypeID (PK) (Number)
-OperationType (Text)

tblTruckingUnits
-TruckingUnitsID (PK) (Number)
-TruckingUnits (Text)

tblTruckingCargoType
-CargoTypeID (PK) (Number)
-CargoType (Text)

tblTruckingEquipmentType
-EquipmentTypeID (PK) (Number)
-EquipmentType (Text)

tblTruckingRadius
-RadiusID (PK) (Number)
-Radius (Text)

tblTruckingState
-StateID (PK) (Number)
-State (Text)

**************************************************************************************

Ok....now that's out of the way. Here is where it gets confusing. Take for
example tblTruckingState, in the state field I have listed all 50 states.
Each Insurance carrier can write in multiple states, but the users will only
search for one state for their client. The way I have it now will only lead
to repetition.

Also, take for example the tblTruckingCargoTypes and CargoTypes field. Each
carrier is different. Some will insure almost anything in the list, some only
a few items.

The way I have it now will only lead to repetition and multiplication of
data, especially dealing with the carriers and the multiple states they write
in. I have designed DBs before, but it's been awhile and I'm a tad rusty. Any
insight on this would be greatly appreciated.


Thanks!

 

[mscertified]

The people on this forum may know nothing about 'insurance carriers' - which
is not mentioned in any of your tables. I assume you mean
tblTruckingMarkets??? If you search by state on this table surely you will
not get duplicates unless you have the same state more than once for the same
PK. If this is the case, you must break out each attribute in this table to a
separate table.
E.g. tblTruckingMarketsStates, tblTruckingMarketsCargoType etc. etc.

-Dorian

 

[Allen Browne]

The main entity you deal with are the carriers, so you need a table of
clients:
tblClient:
ClientID AutoNumber primary key
Company Text
...

One client can operate in several states, so you need a related table for
the states the client operates in:
tblClientState:
ClientID relates to tblClient.ClientID
StateID relates to tblState.State

One client can also carry several types of cargo, so you need a related
table to store that:
tblClientCargo:
ClientID relates to tblClient.ClientID
CargoTypeID relates to tblCargoType.CargoTypeID

And so on. In other words, this will be several tables related back to the
Client table, not one combined table like you proposed in
tblTruckingMarkets.

You would only combine the StateID and the CargoTypeID in the same table if
there was some relationship between those entites that was important. For
example if you wanted to record stuff like:
Client 64 carries cargo type 20 in Nevada
Client 64 carries cargo type 20 in Utah
but I seriously doubt that you need to limit the cargo type to state
borders.

I'm not sure what constitutes a New Venture, but is it date-based (e.g.
anything started in the last 2 years is a new venture)? If so, you might
just put a date field in the Client table to indicate when the venture
started. This way you can query whether it started in the last 2 years
without having to keep that up to date manually.

Hope that helps you piece this together.

 

[Outatym]

Allen,

For some reason I never thought of relating the tables back like that. Now
the users of this database will not be storing any of the data they input.
They will just be querying the data that I put in there for them. Think of
it kind of like a search engine for insurance carriers with multiple
variables.

Would the DB design you proposed still work?

As for your inquiry about the New Ventures, a New Ventures is a new company.
Some carriers will not write them no matter what...some will. I just need
to be able to say whether company A writes new ventures....company B does not.

Hope this helps out....I appreciate all of your help on this!

Thanks,
Chris

 

[Allen Browne]

Yes: the structure would still work for querying/searching.

Okay: sounds like SupportsNewVenture should be a field.