SQLTeam.com | Weblogs | Forums

Database Design doubts

Greetings again experts.

I have some doubts I need your expertise on.

I have a lookup table called EquipmentType with the following attributes:
EquipmentTypeID
EquipmentType.

Sample data:

**Equipment Type ID**         **Equipment Type**
1                                      Aircrafts
2                                      Boats
3                                      Cars

Then I have three tables, Aircrafts, Boats, Cars

My doubt is that I can't seem to decide to decide whether to have EquipmentTypeID as foreign key to each of the equipments.

For instance:
Table = Aircraft
AircraftID
EquipmentTypeID
other attributes

Table = Boats
BoatID
EquipmentTypeID
other attributes

Table = Cars
CarID
EquipmentTypeID
other attributes

OR is it better to just have a bridging table with the following attributes:

BridgeTable
CarID
AircraftID
BoatID
EquipmentTypeID

This way, we would not have EquipmentTypeID on any of the equipments.

Can I please get your thoughts on this?

Many thanks in advance

FK 100%. Since a given piece of equipment can only have one type, it's a classic use of a FK in the main table.

Scott, thanks very much for the prompt response.

If I understand you correctly, you are suggesting that BridgeTable is not necessary here, just add EquipmentTypeID to each equipment table, correct?

Yes. A bridge table is not needed. Since a given piece of equipment can only have one type, just add the FK / type ID to the main tables (Cars/Boats/...).

Aren't all the records in the [Aircraft] table "Aircrafts" by default? So not sure you need an [EquipmentTypeID] column at all.

However, if you had a VIEW that combined data from the three tables [Aircraft], [Boats], [Cars] then personally I would add a "Source" column, to the VIEW, to indicate which table the data came from (and, in the context of your question, that "source" column could be satisfied by the use of [EquipmentTypeID] instead.

That slightly leads me to the thought that if you do "combine" data from these three tables then having [EquipmentTypeID] in each table, even though EVERY ROW in one of those tables will have the same value, might make the combined data easier to handle.

But by the time I get to that line of thought I start to ask: Why you not have ONLY ONE [Equipment] table containing all of the Aircraft, Boats and Cars (and, maybe, have a 1;1 table for each type, storing ADDITIONAL ATTRIBUTES, being specific to ONLY that type of equipment).

So the main table might be EquipmentID, EquipmentTypeID, EquipmentName and then a table for CarAttributes containing details about the Trunk size (i.e. attributes not relevant to the other Equipment Types) ...

... or you could just have a Key/Value table of Attributes which only certain KEYs being valid for certain types of Equipment.

I'm probably over thinking it though ... that's what normally happens!

Kristen,

What happens then if user has more than one car or boat or aircraft?

That's why I don't think we can have one table for all three equipments.

I don't know your SPEC in detail, of course, and I may well not have read the O/P well - that's something else I'm famous for ... so do please put me straight if I am off-course

So I'm thinking:

Table: Person

PersonID, PersonName, etc.

Table: Equipment

EquipmentID, EquipmentTypeID, EquipmentName

Table: EquipmentType

EquipmentTypeID, EquipmentTypeName with FKey to Equipment

Table: EquipmentOwned

PersonID, EquipmentID - personally I would also put an ID on this table, just to make it easy to refer to a specific row, but that is not the by-the-book way.

Table: CarAttributes

EquipmentID,HasTrunk, IsHatchBack, ...

Table: BoatAttributes

EquipmentID, HasOutboard, NoOfBerths, ...

or have a single Attributes table instead

Table: EquipmentAttributes

EquipmentID, AttrKey, AttrValue

Where data is:

1234, 'HasTrunk', 'True'
5678, 'HasOutboard', 'False'

where in this example 1234 = Car EquipmentType and 5678 = Boat equipment type

The benefit of the Attributes Table is you can easily add more AttrKey values going forwards, no database change. The downside is that it can be a nuisance to include in reports ("I want all the attributes of the Equipment, on one line, in separate columns" - that's easier if the attributes are ... ermmm ... in separate columns in a table :slight_smile:

The upside for Attributes Table is if any of the attributes can have multiple occurrence - just add an Item or Sequence column, and you can allow repeat values.

AuthorisedPerson - repeat that (in Attributes Table) for each person that is authorised to use that piece of Equipment

Hey Kristen,

Nothing to get you straight on at all.

You guys are awesome and I truly appreciate the help immensely.

Let me go through this. I think it is looking good along with Scott's.

After some review of your draft, I see this:

EquipmentType = Table
EquipmentTypeID,
EquipmentTypeName,
etc

Then I see:

Equipment = Table
EquipmentID = this is ok
EquipmentTypeID = this is ok
EquipmentName = Why this when we have equipmentTypeID that indicates equipment name?

Yup, I agree, should be covered by the Name from the EquipmentType table. I was thinking it would need "A descriptive name specific to that particular piece of equipment"

If you had several pieces of identical equipment the same you might want a human-friendly name for each one - e.g. the License Plate Number, or some data similar to that. For example, if you had 10 pickup trucks in your "fleet", all the same Brand.