Desperately seeking Address help

[PSI]

I have multiple entities ( Company, Employee, Vendors etc ) any of
which can have multiple Address's.

After much googling, and following some much appreciated advice from
respondents to previous posts I have arrived at the following table
structure ( fields scaled down for simplicity ):

tblEntity
=======
EntityID - autonumber, PK
Name - Text

tblCompany
========
ID - autonumber, PK
EntityID - number, FK, 1 t o1 to tblEntity.ID

tblEmployee
========
ID - autonumber, PK
EntityID - number, FK, 1 t o1 to tblEntity.ID

tblAddress
=======
ID - autonumber, PK
EntityID - number, 1 to manyto tblEntityID
Type - number
Address1
City

All relations have IReferential Integrity enabled and Cascade Delete's
enabled.

However I am unable to get this to work. I am trying to enter some
data in Dataview mode for the various tables but always run into an
error because the Entity table requires a record in one of the tables
linked 1 to 1. The only way to add the data is for the EntityID to be
the same in all the 1 to 1 tables which would be logically incorrect.

The second issue is that deleting a record from one of the tables (eg
Company) cascades to the Entity table but doesn't cascade to the
Address table. On the other hand deleteing an entity record does
delete from the Company and Address table. This behavior seems to
indicate that the application needs to always have the EntityID in
order to remove a company/employee etc. rather than the Company.ID
etc. ( This doesn't feel right to me, but if thats the way it works
then no problem)

I'm a real novice with table design so I'm obviously missing something
( ie the big aha ). Any help in setting up this address support
would really be appreciated.


My environment is:
VC++ 6.0, ADO, Jet4,MDA C2.7, Win2K

Thanks
Frank

 

[Tim Ferguson]

tblEntity
=======
EntityID - autonumber, PK
Name - Text

tblCompany
========
ID - autonumber, PK
EntityID - number, FK, 1 t o1 to tblEntity.ID

There's no need for the tblCompany.ID: since you have a unique index on
tblCompany.EntityID it is simpler (and logical) to use that as the PK.

tblEmployee
========
ID - autonumber, PK
EntityID - number, FK, 1 t o1 to tblEntity.ID
Ditto


tblAddress
=======
ID - autonumber, PK
EntityID - number, 1 to manyto tblEntityID
Type - number
Address1
City

However I am unable to get this to work. I am trying to enter some
data in Dataview mode for the various tables but always run into an
error because the Entity table requires a record in one of the tables
linked 1 to 1.

Then you have the relationships the wrong way round. It should be FROM one
tblEntity.EntityID TO zero-or-one tblOther.EntityID. That way you enter the
entity first, and then the company or employee.

The second issue is that deleting a record from one of the tables (eg
Company) cascades to the Entity table but doesn't cascade to the
Address table.

No: that's because the relationships are the wrong way round. Actually, I
really don't think you want Cascade Deletes enabled in such a complex
arrangement but you would be better off handling them yourself. Say you
have an entity Harrison and mistakenly start filling him in as a company:
you want to remove the company record, retain the entity record and start
filling the correct employee record; or you may want to remove the entity
record altogether, especially when you find that you already have Harrison
the employee; or you may want to keep everything and just re-use it for
Henry's the company and carry on filling stuff in.

Correctly enabled, Cascade Delete will allow you to delete the Entity
record and it will, without warning, remove the corresponding Employee or
Company record. Is that really what you want?

I'm a real novice with table design so I'm obviously missing something

No, you're not far off. You are not beginning with a simple design, but
there's nothing in here that is inherently impossible.

All the best


Tim F

 

[PSI]

Then you have the relationships the wrong way round. It should be FROM one
tblEntity.EntityID TO zero-or-one tblOther.EntityID. That way you enter the
entity first, and then the company or employee.

Changing the direction has cleared up the problem. Although with
manual entry the integrity still sometimes gets messed up ( ie enter
the same EntityID in company and employee, delete the entity record
and only one table gets cascaded. The record in the other is
orphaned). Not a problem because it shouldn't happen once programmed.

No: that's because the relationships are the wrong way round. Actually, I
really don't think you want Cascade Deletes enabled in such a complex
arrangement but you would be better off handling them yourself. Say you
have an entity Harrison and mistakenly start filling him in as a company:
you want to remove the company record, retain the entity record and start
filling the correct employee record; or you may want to remove the entity
record altogether, especially when you find that you already have Harrison
the employee; or you may want to keep everything and just re-use it for
Henry's the company and carry on filling stuff in.

Correctly enabled, Cascade Delete will allow you to delete the Entity
record and it will, without warning, remove the corresponding Employee or
Company record. Is that really what you want?

It seems I've been approaching the problem from the wrong point.
What I have been trying to do is delete the Company ( or Employee etc
) record and have the Entity and Address records deleted. To do this I
was trying to use the Entity as a "bridge" between Company, Employee
tables and Address table. Following your suggestions it seems I
should track the EntityID in the application and delete the Entity
Record NOT the company record.

Having restructured the references as you suggest things are behaving
much more rationally and I think I understand it ( although I need to
unlearn my previous appoach ).

Many thanks for the help. Without it I would still be banging my head.

Frank

 

[Tim Ferguson]

Glad it helped: some extra points...

To do this I
was trying to use the Entity as a "bridge" between Company, Employee
tables and Address table.

Think of it more as an Entity as a "something" that has addresses; and each
something might be an Employee type of something, or a Company type of
something - you know which by the presence of a Company or an Employee
record. (Unfortunately, there is nothing in Access that prevents you having
both at the same time, so you have to program it into the user interface
and hope.)

If you delete a Company record, then you still have the something, which
may be an embryonic company or an embryonic employee. If you delete the
something (i.e. the Entity record) then you -must- remove the company or
employee record, because otherwise you will have a company-type of nothing,
instead of a company type of something. That is what referential integrity
means, and why Cascade Delete works the way it does (and why I think it's a
Bad Thing).

By symmetry, the same should be happening in the Employee table, and I am a
little worried about you saying "The record in the other is orphaned" --
the point of having a RDBMS is that it prevents such an occurrence.
Childless, yes, but parentless never!

Following your suggestions it seems I
should track the EntityID in the application and delete the Entity
Record NOT the company record.

As above, you can't delete the Entity and leave the Company: if the
relationship is set up right, the DBEngine won't let you. Don't worry about
finding this hard. Once you understand this, you have essentially got the
whole of relational theory cracked!

All the best


Tim F