SQLTeam.com | Weblogs | Forums

Database Design doubts


#1

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


#2

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.


#3

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?


#4

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/...).


#5

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!


#6

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.


#7

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


#8

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?


#9

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.