Is the use of Enity IDs bad?

[asc4john]

I have a table that hands out Entity IDs to every item in my database,
that is every item now has a unique ID in the database as well as an
Autonumber ID in it's table which I rarely use. Is there a reason that
Entity IDs should not be used? I use them because there are many
tables and most have have some form of many2many relationship. There
are a lot of join tables and these are the tables I am mostly
interested in. The EntityID has proven to be very convient and easy to
use. Also is there a reason that extra data should not be put in the
join table? i.e. instead of the two ID fields there are several other
fields that contain information specific to that join. This is a
design that I have recently put together and so far has worked better
than any thing else I have come up with. Comments on the use of the
Entity ID and added info in the join table would be appreciated Thanks.

 

[Allen Browne]

John, I'm not sure I understand the need for this table.

Could you just use an AutoNumber in each table? Does it matter if a client
is assigned the same number as an invoice? Don't you have relationships
between the tables (hopefully with Referential Integrity), so that each
foreign key field relates to a specific, defined key in the primary table?
So there can be no ambiguity about which table and field that relationship
refers to, even though the ID value is not unique across your application?

Access is a multi-user databse. If you have a single table assigning unique
ID numbers to all other tables in your database, presumably you have written
a function that gets the next available ID value and assigns it. To be
reliable in a multi-user environment like Access, that function would need
to:
- lock the entity assigner table;
- get the last assigned value and increment it,
- assign the value to the table requesting it,
- (possibily check that the write succeeded),
- unlock the entity assigner,
- include some sophisticated error handling with random delays to handle
clashes, and ultimate time-out issues.

Now that's going to cause *huge* multi-user issues. If several users are
adding data at once, even if adding to different tables, they have the
potential for clashing. Even if there's only a single user, this problem
will still arise with other processes and Append queries where there is an
attempt to add lots of records at once.

In short, the design is unnecessary (AutoNumbers do it), complex to write
and maintain (why build an entire multi-user locking system, when Access has
2 or 3 built-in to choose from), inefficient (all tables hitting on the same
assigner table will cripple multi-user aspects), and yields no benefit.

Please, John, don't take that as a personal criticism: you are clearly
wanting to know, and I respect that.

 

[asc4john]

I am describing equipment used in industrial and commercial settings.
Originally there were many tables, one for each different type of
equipment, but these tables had to be linked to tables concernd with
tubing,piping and cables. A pipe can supply several piece of equipment
that may be located in different tables, clash. I then went to one big
equipment table with many different look up tables based on
equipment_type, this led me to quite a long experiment with subclassing
but in the end found little success with this. So I dumped the base
table, kept the look up tables and went to EntiyIDs. I think the real
problem is I don't know where to store, define, assign the type info
for the particular entity I am dealing with. Is a pump stored in a
table of pumps or a table of equipment with a type identifier of
_pump_. Motors go into tables of motors or a table of electrical
equipment or a table of equipment along with guages and switches and
valves. Clashing is an issue. The same pipe, tube cable can go to the
same piece of equipment, loopback not common but has to be accounted
for . There are of course all the possible variations. The Etity
table has solved a problem but maybe I'm solving the wrong problem, I
don't know.

 

[Allen Browne]

Ah, thanks, I think I understand now.

This situation is normally handled as subtypes. You have one main record for
each piece of equipment (regardless of what it is), and then related tables
for each class of equipment. There is an EquipID primary key, which is a
foreign key in a one-to-one relation with the other tables.

The Equipment table contains the fields that apply to most types of gear,
such as acquisition date, location (where the gear is), who is responsible
to maintain it, which supplier provides parts for it, etc. The related
tables contain the fields unique to that part. For example, an electric pump
might have fields for amperate, number of phases etc which don't apply to
diesel tractors, so the tractors table has different fields. But the Pump as
the EquipID (relates to Equipment.EquipID) and the Tractor table also has
the EquipID field (relating to Equipment.EquipID.)

Equipment.EquipID can still be an AutoNumber if you wish. Pump.EquipID will
be Number (not AutoNumber) since it ties to the EquipID in the Equipment
table.

Does that better reflect what you need to do?

 

[asc4john]

I think I tried that approch before and ran into problems. But now,
thinking about it I may have been using an autonumber field in the
"pump" table and not the EquipID in my joins and qureries and thats why
I ran into problems. I will dig those files up and have a look. Maybe
my pass approach was the right idea but implimented incorrectly. That
was several years ago and I really didn't know what I was doing then.
The EntityIDs sure works well though, but I do have reservations about
it. Thank you for your input, very much appreciated.

 

[David F Cox]

Also is there a reason that extra data should not be put in the

join table?

The idea behind a relational database is that it stores and accesses
information about relationships. The join table is where the information
about a relationship between the two entities joined belongs. e.g If People
are joined to Organisations the date joined belongs in the link table.

 

[Amy Blankenship]

I am describing equipment used in industrial and commercial settings.
Originally there were many tables, one for each different type of
equipment, but these tables had to be linked to tables concernd with
tubing,piping and cables. A pipe can supply several piece of equipment
that may be located in different tables, clash. I then went to one big
equipment table with many different look up tables based on
equipment_type, this led me to quite a long experiment with subclassing
but in the end found little success with this. So I dumped the base
table, kept the look up tables and went to EntiyIDs. I think the real
problem is I don't know where to store, define, assign the type info
for the particular entity I am dealing with. Is a pump stored in a
table of pumps or a table of equipment with a type identifier of
_pump_. Motors go into tables of motors or a table of electrical
equipment or a table of equipment along with guages and switches and
valves. Clashing is an issue. The same pipe, tube cable can go to the
same piece of equipment, loopback not common but has to be accounted
for . There are of course all the possible variations. The Etity
table has solved a problem but maybe I'm solving the wrong problem, I
don't know.

I personally wouldn't be afraid of self joins. I find them very convenient.
But--and this may be a big but--the end use for my data is never within
Access. So often if a given data structure works well with Access's
internal forms and reports doesn't matter to me. So what works for me may
not necessarily work for you. The thing that self joins give you that other
structures don't is that they can essentially model infinite depth. This is
an advantage where you need that, but can be a disadvantage depending on the
capability of whatever tool you are using to show that kind of depth and
your ability to cope with recursion.

One way of dealing with your structure would be something like this:

Equipment
EquipmentID PK
EquipmentDesc
EqipmentType

Properties: Describes all properties any piece of equipment could
potentially have
PropertyID PK
PropertyDesc Description of the property
PropertyType Type of property (could include equipment)
PropertyEquipmentType (when the type is equipment, use this to constrain the
list to a given type of equipment)

PropertyType
PropertyTypeID PK
PropertyTypeDesc

EquipmentType: Describes the types of equipment so that you can define which
properties can be filled in for which type.
TypeID
TypeDesc

EquipmentTypeProfile: Describes what properties a given piece of equipment
should have
TypeID
PropertyID

EquipmentPropertyValue
EquipmentID
PropertyID
PropertyValue

So now any piece of equipment can be described by pulling the profile of
what properties it SHOULD have and the values that those properties have.
The problem you'll have with doing this in Access is that it's relatively
difficult to make a form where the "fields" you are to fill in are
determined by a query--even more so when some of those fields will need to
have comboboxes in them to allow you to select a piece of equipment that is
a property for a different piece of equipment.

HTH;

Amy

 

[Jamie Collins]

This situation is normally handled as subtypes. You have one main record for
each piece of equipment (regardless of what it is), and then related tables
for each class of equipment. There is an EquipID primary key, which is a
foreign key in a one-to-one relation with the other tables.

The Equipment table contains the fields that apply to most types of gear,
such as acquisition date, location (where the gear is), who is responsible
to maintain it, which supplier provides parts for it, etc. The related
tables contain the fields unique to that part. For example, an electric pump
might have fields for amperate, number of phases etc which don't apply to
diesel tractors, so the tractors table has different fields. But the Pump as
the EquipID (relates to Equipment.EquipID) and the Tractor table also has
the EquipID field (relating to Equipment.EquipID.)

There is an element missing from your description. Without an 'entity
type' column in the 'equipment' table, how can you prevent a 'tractor'
being entered into the 'electric pumps' table? See this example:

http://groups.google.com/group/microsoft.public.access.tablesdbdesign/msg/cab59beaa9ae84fc

Returning to the OP's original question, the use of Enity IDs is not
ideal. An industry standard identifier for a 'tractor' is the VIN; I'm
not familiar with the 'electric pump' industry but I'm pretty sure they
don't use VINs. The vagueness of the name 'Enity ID' suggests there is
no common natural key identifier, so we are in the realms of inventing
a key for entities that are only related at a custom level of
abstraction. Remember an autonumber should never be exposed and is only
useful as a 'surrogate' if you also have a natural key. And, as you've
already pointed out how, the database itself is unsuitable when it
comes to establishing a trusted source for custom key values.

Jamie.

--