Primary Key - Auto Number

[Craig Ferrier]

I am looking for some advice on how to set up primary keys and ID numbers.

I need two table, one for donors and one for companies. However I receive
donations from both and only really require one donations table. If I use
auto number in the table I will end up with a donor and a company with the
same ID number. I understand that I can use text for the primary key and
manually assign an ID, but this opens it up to possible data entry errors.
Is there another way of doing this.

I though using D1, D2, D3 etc and C1, C2, C3 etc - but not sure how to this.

What if I assigned an Auto Number and then used a query to create a
secondary ID field adding the letter in front of each ID. Would this work
or cause problems?

Your thoughts and suggestions would be greatly appreciated.

Craig Ferrier

 

[tina]

you really have one "donor" entity. some donors are companies, and some are
individuals, so "donor type" is a value that describes a donor - not a
reason to make separate tables. there may be a few fields that apply to one
donor type and not the other. you can probably afford to simply leave those
fields blank in records where they don't apply; but if you prefer, you can
put the "donor-type specific" fields into a child table linked to the donor
table. the bottom line is: put all your donors in one table, where they
belong, and your problem is solved - you only have to link the donations
table back to that one donor table.

hth

 

[Guest]

Craig:

The appropriate way of modelling this depends on whether all companies and
all people in the database are donors, or whether the databse also records
companies and people who are not donors.

For the first scenario (everyone is a donor) the second solution which Tina
touched on in her reply would be the appropriate one. You would in this
scenario have three different entity types here, Donors, Individuals and
Companies. Individuals and Companies are sub-types of Donors, however, and
the way to model Types/Sub-types is to have one table for the Type (Donors)
and one for each Sub-Type (Companies and Individuals). The primary key of
each sub-type is also a foreign key referencing the primary key of its
super-type, so while Donors can have an autonumber primary key, Companies and
Individuals would have a straightforward long integer number primary key. By
entering data via a Donors form with subforms for Companies and Individuals
linked on the key columns the values for the primary key of the Companies and
Individuals would be set automatically when rows are inserted via the
subforms.

Types and sub-types are characterized by each sub-type sharing all
properties of its super-type, but not those of other sub-types. Consequently
the Donors table would have columns for the attributes which both Companies
and Individuals have in common, while each of the latter would have columns
representing the attributes specific to each sub-type. The Donations table
would relate to the Donors table via a foreign key column referencing the
primary key of Donors.

If the second scenario is true then you do not have a Type/Sub-type model as
there would be Companies and/or Individuals who are not also Donors. In this
scenario you could model the relationship between Donations and Companies and
Individuals either by having two foreign key columns in Donations, one
referencing the key of Companies, the other the key of Individuals. This
would mean there would be Null foreign keys, however. Most people would
accept this, but if you prefer to avoid Nulls in view of their semantic
ambiguity you could adopt the solution suggested by Chris Date of modelling
the relationships by separate tables, one with columns referencing the
primary keys of Companies and Donations, the other the keys of Individuals
and Donations. This is exactly the same as the normal method of modelling a
many-to-many relationship, but in this case used to model a many-to-one
relationship in order to avoid Null foreign keys.

Ken Sheridan
Stafford, England