Relations problem with tables

[jayke]

Hi,

I am creating some table for a database I want to use.

I had the following tables:

[Organisation]
orgOrganisationID (primary key)
orgName
orgPostalcode
orgStreet
orgCity
orgCountry


[Contacts]
coContactID (primary key)
coSurename
coFirstname
coFysicPostalcode
coFysicStreet
coFysicAdditions
coPostPostalcode
coPostStreet
coPostAdditions
coTel
coFax
coEmail


[Postalcodes]
poPostalcode (primary key)
poPostalCity
poPostalCountry


I have two postalcodes in the Contacts table. I can't create two one-on-many relations from the Postalcodecodes table to the Contacts table.

So I thougt I split up the contacts table and create a one-on-one relationship between the two new tables.

So it would look like this:

ContactsGeneral
cgGeneralContactID (primary key, autonumbering)
cgSurename
cgFirstname
cgFysicPostalcode
cgFysicStreet
cgFysicAdditions
cgTel
cgFax
cgEmail


ContactsPost
cpPostContactsID (primary key, numeric)
cpPostPostalcode
cpPostStreet
cpPostAdditions

I would create a one-on-one relationship between coGeneralContactID and coPostContactsID. And now I can create my one-on-many relationships between from the Postalcodes table.

1) Is this clever?
2) Should both these fields have a key?
3) I set coGeneralContactID to autonumbering and coPostContactsID to numeric. is this also correct.
4) I also use prefixes before the fieldnames 'cp-' for 'ContactsPost' is this naming convention ok? I think it makes programming easier. But are there condingstandards for that in Access/VBA?

Thanks,

Jayke

 

[The_Doc_Man]

I can't create two one-on-many relations from the Postalcodecodes table to the Contacts table.

Ah, but you CAN. When you get into the relationship window, add the PostalCodes table twice. The second one will be labelled PostalCodes(2).

Define one relationship with the untagged PC table and the other with the tagged PC table. It's the same table, just a second reference.

1) Is this clever?

Who cares? The better question is, Should I split the tables for data reasons independent of the problems with dual references? If THAT answer is YES, then do it. If there is no particular reason to split the table OTHER than the double-barreled relationship for postalcodes, you might have no good reason to split the table. And the question has to do with whether you mixed apples and oranges when you defined the original tables.

Technically, having two possible postal codes in the same record implies the presence of a "repeating group" which is usually a significant indicator of the need to split a table. HOWEVER, there are always compromises. (Pat, try not to jump too hard on my stuff.) When the work required to do the split is a lot and the times you actually need the second postal code is VERY VERY little, there are times when you ignore the problem and program around it. The more often you have to "program around it" the more justification you have for the split.

A true purist would split the tables. Me... I'd USUALLY split the tables. Tells you something about me, doesn't it?

2) Should both these fields have a key?

If by KEY you mean INDEX, you put indexes on fields when you will use those fields for sorting, searching, report-ordering, etc. Having too many indexes costs you because updating an indexed field means you have to write data twice.

If you REALLY meant to ask whether something should or should not be a key, there are normalization rules to consider. A field should be part of a key when every other field in the table depends on the entire key (including that field you were considering). If the table contains fields that are not dependent on that candidate field, the answer is probably NO (and possibly that you should split the table if some fields do and some fields do not.)

3) I set coGeneralContactID to autonumbering and coPostContactsID to numeric. is this also correct.

Not only numeric but specifically LONG Integer as a data type.

4) I also use prefixes before the fieldnames 'cp-' for 'ContactsPost' is this naming convention ok? I think it makes programming easier. But are there condingstandards for that in Access/VBA?

Search this forum for recent questions on naming conventions. Within the last week or so I answered a question on naming conventions to some considerable depth. Others also contributed to the same thread. Rather than re-invent the wheel, I suggest a search.

But today I'm being generous. Look here. http://www.access-programmers.co.uk/forums/showthread.php?t=85072

A naming convention is only as good as how often you observe it.

 

[jayke]

Thanks!

| can see the light again!

You have been very helpfull!

Regards.