A complex primary key with voids? Hope not.

[Ilan Sebba]

I have a 'child' table (say Substance), which can be made out of one of
three 'parent' tables (Animal, Vegetable or Mineral). The uniqueness of
each substance record is determined by the parent record, and by the time
the substance was created. So I would like to have a composite key made out
of Animal, Vegetable or Mineral, and the time stamp.

tblAnimal
ID
AnimalFields

tblVegetable
ID
VeggieFields

tblMineral
ID
MineralFields

tlbSubstance
AnimalVegetableOrMinaral [PK1]
TimeStamp [PK2]
SubstanceFields

My problem is the field Animal, Vegetable or Mineral. This is supposed to
be a foreign key. But to which table? How do I do this. One way, which
will guarantee two voids in each record, is this:

tblSubstance
AnimalFK [PK1]
VegetableFK [PK2]
MineralFK [PK3]
TimeStamp [PK4]
SubstanceFields

Given that a substance has only one parent (animal, vegetable or mineral),
there will always be two voids. I don't like voids (I am sure 'void' and
'avoid' are related).

Another solution is to create a dummy 'grandparent' table which will be a
parent to all animals, vegetables or minerals, and then require that have a
1-2-1 relationship between this dummy table and Animal, Vegetable and
Mineral.

tblCreator
ID

tblAnimal
CreatorFK [PK]
AnimalFields


tblVegetable
CreatorFK [PK]
VeggieFields

tblMineral
CreatorFK
MineralFields

Now my substance table can have one a two field composite key of CreatorID
and Timestamp.

tblSubstance
CreatorID
Timestamp
SubstanceFields

OK, so this is a solution. But I don't like it. It involves creating a
truly dummy field which serve no purpose other than helping me avoid my
dislikes. Do two wrongs make a right?

So, what would the TableDesign newsgroup recommend I do?

Any suggestions most appreciated.

 

[Tim Ferguson]

"Ilan Sebba" <ilan underscore sebba at btinternet stop com
(e-mail address removed)> wrote in

tblSubstance
AnimalFK [PK1]
VegetableFK [PK2]
MineralFK [PK3]
TimeStamp [PK4]
SubstanceFields

Given that a substance has only one parent (animal, vegetable or
mineral), there will always be two voids. I don't like voids (I am
sure 'void' and 'avoid' are related).

Aha: now I see your problem. You actually have a missing table, which would
be the Parent entity. It's not a "dummy table", it's a real entity and
models the "parentness" of the different types of parents. There are likely
to be various parental attributes that should be stored in it. So you don't
have two wrongs, you just have one common modelling scheme known as
Subclassing (or Subtyping).

tblParents
*ParentNumber Primary Key
ParentalAttributes

tblAnimals
*ParentNumber Primary Key FK references tblParents
AnimalStuff

tblVegetables
*ParentNumber Primary Key FK references tblParents
VegetableMatter

tblMinerals
*ParentNumber Primary Key FK references tblParents
MineralLodes

tblSubstances
*ParentNumber FK references tblParents
*CreateDate

constraint Primary Key (ParentNumber, CreateDate)


As a final note, it is not possible in Access (that is to say, in Jet) to
force a Parent to have only one type of Type, so you will have to use forms
and so on to police for the same ParentNumber appearing in two or more of
the thing tables. In a real RDBMS, you could use triggers to implement some
kind of logic but they are not available in Jet, I'm afraid.

Hope that helps


Tim F

 

[Ilan Sebba]

Thank you for this.
I undestand what you say but am now having trouble implementing it. I don't
know how to add new records in the animal, vegetable and mineral tables.

I have a Parent table as you suggest:

tblParents
*ParentNumber Primary Key (autonumber?)
ParentalAttributes

This table contains all the attributes common to animal, vegetable and
mineral. The only thing that these three entities have in common is that
they can give rise to substances. Hence, this table reduces to:

tblParents
*ParentNumber Primary Key (autonumber)

That is OK. But how do I add a new animal records (using code - vb or SQL)?
This can easily be done in MS-Access end user forms, but how do I do this in
code. To insert a record in the animal table, I presumably need the primary
key of the 1-2-1 related parent. How do I know what that is. I think my
problem boils down to 'how do I insert an autonumber record, and then know
what it is?

Many thanks for your help.

Ilan

"Ilan Sebba" <ilan underscore sebba at btinternet stop com
(e-mail address removed)> wrote in

tblSubstance
AnimalFK [PK1]
VegetableFK [PK2]
MineralFK [PK3]
TimeStamp [PK4]
SubstanceFields

Given that a substance has only one parent (animal, vegetable or
mineral), there will always be two voids. I don't like voids (I am
sure 'void' and 'avoid' are related).

Aha: now I see your problem. You actually have a missing table, which would
be the Parent entity. It's not a "dummy table", it's a real entity and
models the "parentness" of the different types of parents. There are likely
to be various parental attributes that should be stored in it. So you don't
have two wrongs, you just have one common modelling scheme known as
Subclassing (or Subtyping).

tblParents
*ParentNumber Primary Key
ParentalAttributes

tblAnimals
*ParentNumber Primary Key FK references tblParents
AnimalStuff

tblVegetables
*ParentNumber Primary Key FK references tblParents
VegetableMatter

tblMinerals
*ParentNumber Primary Key FK references tblParents
MineralLodes

tblSubstances
*ParentNumber FK references tblParents
*CreateDate

constraint Primary Key (ParentNumber, CreateDate)


As a final note, it is not possible in Access (that is to say, in Jet) to
force a Parent to have only one type of Type, so you will have to use forms
and so on to police for the same ParentNumber appearing in two or more of
the thing tables. In a real RDBMS, you could use triggers to implement some
kind of logic but they are not available in Jet, I'm afraid.

Hope that helps


Tim F

 

[Ilan Sebba]

I have done a bit of reading on how to retrieve autonumbers, and I must say
that I am really bothered by the idea.

I would like to argue this point you made about the parent table:

It's not a "dummy table", it's a real entity and
models the "parentness" of the different types of parents

I have difficulty accepting that 'parent' is a real table. On the one hand,
Animal, Vegetable and Mineral have nothing in common. They are mutually
exclusive. Hence, any entity which models their 'parentness' must be
fictitious. On the other hand, Anial Vegetable and Mineral do have
something in common: they all can give birth to children. Surely, that
'giving birth' thing could be an entity (does an entity need a noun and a
verb). So at first I accepted the idea of a parent entity, because of this
common maternal ability to give birth to substances.

It is when I started to populate the 'parent' table that I realised the
difficulty. The parent table has to contain a primary key which will also
be the primary key of the Animal table (and her sisters). But there is
absolutely no natural primary key to alocate. The only natural key you can
have in the parent table would replicate the data to be inserted in the
Animal table (eg the primary key could be a concatination of
DaughterTableName and DaughterRecordID).
Once you have inserted tat dummy primary key, there is no natural way
to retrieve it. This fact is laboured by the fact that the newsgroup
solutions for retrieving autonumbers seem to run contrary to the whole
principle of relational databases (but please do not ask me what those
principles are). 'If you are running such and such a driver, you can keep a
record of the cursor, and don't forget the bookmark there" and so forth and
so forth - all of which suggest that when the artificial autonumber is the
only way of retrieving records - then the relational database has actually
failed. If you can categorically tell me that I am wrong, please do, and I
will try to figure out what 'relational datase' actually means, and will try
to prove my case.

Until now I have been using autonumber for convenience: it is easier to drag
and drop relationships. Even if an authonumber would serve as a primary
key, I could always retrieve it by natural using unique keys.

This whole thing started because I wanted a natural relationship between the
'substance' table and their parent, who could be Animal, Vegetable or
Mineral.

Your suggested I have a 'supertype' parent entity for Animal Vegetable and
Mineral. I really want to do this, but it seems that I must mess around
with things which have nothing to do with SQL to keep track of the single,
unnatural attribute of this parent table (the autonumber).

What are your thoughts?

Many thanks

Ilan

"Ilan Sebba" <ilan underscore sebba at btinternet stop com
(e-mail address removed)> wrote in

tblSubstance
AnimalFK [PK1]
VegetableFK [PK2]
MineralFK [PK3]
TimeStamp [PK4]
SubstanceFields

Given that a substance has only one parent (animal, vegetable or
mineral), there will always be two voids. I don't like voids (I am
sure 'void' and 'avoid' are related).

Aha: now I see your problem. You actually have a missing table, which would
be the Parent entity. It's not a "dummy table", it's a real entity and
models the "parentness" of the different types of parents. There are likely
to be various parental attributes that should be stored in it. So you don't
have two wrongs, you just have one common modelling scheme known as
Subclassing (or Subtyping).

tblParents
*ParentNumber Primary Key
ParentalAttributes

tblAnimals
*ParentNumber Primary Key FK references tblParents
AnimalStuff

tblVegetables
*ParentNumber Primary Key FK references tblParents
VegetableMatter

tblMinerals
*ParentNumber Primary Key FK references tblParents
MineralLodes

tblSubstances
*ParentNumber FK references tblParents
*CreateDate

constraint Primary Key (ParentNumber, CreateDate)


As a final note, it is not possible in Access (that is to say, in Jet) to
force a Parent to have only one type of Type, so you will have to use forms
and so on to police for the same ParentNumber appearing in two or more of
the thing tables. In a real RDBMS, you could use triggers to implement some
kind of logic but they are not available in Jet, I'm afraid.

Hope that helps


Tim F

 

[Tim Ferguson]

"Ilan Sebba" <ilan underscore sebba at btinternet stop com
(e-mail address removed)> wrote in

I have done a bit of reading on how to retrieve autonumbers, and I
must say that I am really bothered by the idea.

I would like to argue this point you made about the parent table:


I have difficulty accepting that 'parent' is a real table. On the one
hand, Animal, Vegetable and Mineral have nothing in common.

In a real world example, that's very uncommon or even never true. If their
progeny are all the same thing, then it is hard to see how the parents
share no attributes at all. The point is moot anyway, since you don't need
to have any other attributes in the Parents table.

Hence, any entity which models their 'parentness'
must be fictitious.

No less fictitious than a table of IsAPatientOf or HasRegisteredFor or
IsALegalRequirementOf. Tables are models of real life entities that may
just as well be abstract as concrete. The act of adding a name to a class
register is an entity that is stored in the HasRegisteredFor table; the
notion that a certain qualification is mandatory for a certain type of
occupation constitutes a record in the IsALegalRequirementOf table.
Similarly, the class of all things that can give birth to something else
constitutes an entity if it is relevant (or, in your case, critical) to the
project in question.

Surely, that 'giving birth' thing could be an entity

To be sure, a pregnancy and a delivery could easily be modelled as an
entity in itself. If, however, there is only one offspring for each
pregnancy, then all the pregnancy details become functionally dependent on
the offspring key, so there is no advantage in separating them out.

The only natural key

(Point of order: there are no natural keys in this set up; they are all
artificial. OK, carry on)

you can have in the parent table would replicate the data
to be inserted in the Animal table ....
Once you have inserted tat dummy primary key, there is no natural way
to retrieve it.

Once again, it's not a dummy key: it's the identifier of a Parent.

And you are right, Access and Jet does not provide a good way of getting a
new Autonumber and handing it back to the program. This is a physical
implementation problem that (a) does not invalidate the logical design; (b)
works more easily in real databases; and (c) is quite easy to get round
anyway.

the newsgroup
solutions for retrieving autonumbers seem to run contrary to the whole
principle of relational databases

Well, we have religious Artificial Keys vs Natural Keys wars here every now
and then, but the areas of agreement are vastly greater than the
disagreements.

(but please do not ask me what those principles are).

If you are going to start rubbishing long-established design methods like
subclassing, then you had better be pretty well versed in the principles -
and the Rules, of which there are twelve -- of R theory.

'If you are running such and such a driver, you can
keep a record of the cursor, and don't forget the bookmark there" and
so forth and so forth - all of which suggest that when the artificial
autonumber is the only way of retrieving records - then the relational
database has actually failed.

I do not recognise what you are quoting there, but you don't have to get to
pernickety about the failings of real-life software that is trying to model
a mathematical theory. AFAIK, there is no available software package, on
the desktop or mainframe, that actually lives up to Codd's twelve rules,
and probably never will be. The translation of a good logical design into a
physical implementation will always involve some climbdowns, some kludges
and some workrounds. It's life and that is what VBA is there for.

If you can categorically tell me that I
am wrong, please do

Yes you are

and I will try to figure out what 'relational
datase' actually means, and will try to prove my case.

Start with CJ Date, which is a lot more readable than Codd's original work.
And/ or a good university course. Remember you are up against forty years
of solid research by some major mathematical minds, which in turn is based
on an even longer history of set theory.

Your suggested I have a 'supertype' parent entity for Animal Vegetable
and Mineral. I really want to do this, but it seems that I must mess
around with things which have nothing to do with SQL to keep track of
the single, unnatural attribute of this parent table (the autonumber).

It actually takes about four lines of code, although you can make it harder
if you need to, to create a new Parent record and deliver the key to the
subtype.

B Wishes


Tim F

 

[Ilan Sebba]

Thanks for putting me in my place ;-)

I better be much more careful (and correct) before criticising relational
databases in a newsgroup named tablesdbdesign. Apologies for sounding
almost blasphemous. But at least I am assured that I am on the right track.
Now my job is to absorb it. I think all my difficulty turns on one key
sentence you write:

"Once again, it's not a dummy key: it's the identifier of a Parent. "

I think this is really the crux of my problem. The government has given me
an ID number. From my point of view, this is a 'fictitious' number. It was
given to me so that the government could create a record for me in a
database. From the government's point of view, the ID number is the *only*
thing which can identify me, and therefore is very real. Nothing else can
uniquely identify me. So if I can accept this about my Social Security
number, why am I having such a hard time with the parent table? Probably
because the government has no other choice (not even my ID can uniquely
identify me - under current lab conditions 2 out of every 140 million people
will have the same DNA).

But with my parent table, the only reason I am creating this 'identifier'
(and resorting to VBA) is because Codd gives me no other acceptable choice.
So I am off to study the 12 principles (looks like a doctorate will not be
enough to truly master it).

Anyway, thanks for bearing with me and being patient notwithstanding my
rubbishing a bible of sorts. No offence intended, I assure you. I should
have tried harder to convey the message that the limitations I perceive in
the database model are more likely to stem from my ignorance rather than a
genuine failing of relational databases.

Thanks once again.

Ilan

 

[Tim Ferguson]

"Ilan Sebba" <ilan underscore sebba at btinternet stop com
(e-mail address removed)> wrote in

But with my parent table, the only reason I am creating this
'identifier' (and resorting to VBA) is because Codd gives me no other
acceptable choice.

Actually, it's because of set theory that you _do_ have the choice. Think
back to your O-level maths, and remember the stuff about "Set {B} is a
subset of set {A}", and "C is a member of {B}", therefore "C is a member of
{A}". A FK relationship is an example of a subset relationship: so the set
of all {Child.Parent} values is a subset of... well, you have to have a set
{Parent.ParentID} values for it to be a subset of.

Thanks once again.

Best of luck


Tim F