Table Relationship

[JohS]

Hi. I'm trying to build a contact table with CompanyName, LastName &
FirstName.

To make this system not looses its speed when it grows big, I thought it
would be an idea to split this into several tables, one for CompanyName, one
for LastName and one for FirstName which will be connected in a linktable.

Example:

TableCompanyName

fldMainKeyCompanyNameIDnumber

fldCompanyNameText



TableLINK

fldForeignKeyCompanyNameIDnumber

fldForeignKeyFirsNameIDnumber

fldForeignKeyLastNameIDnumber



Is this a good way to do this (I'm of course aiming for only storing one
TEXT Name once), and if so, how should I set up the Relationship (have tried
several combinations with no luck as I on the form will have rejection msg.
when trying to store duplicates)?

Thanks for feedback, JohS

 

[Larry Daugherty]

It's a lot of your time and trouble for no apparent gain. Better to
stick with the rule of a single table for each entity type and all
entities of that type to reside in that table. There are any number
of reasons for segmenting a table but I think you've gone overboard.

Make sure that the fields by which you expect to sort and search are
indexed. Again, don't go overboard and index every field or data
entry can be slowed.

Beyond the above and a few other judicious tweaks, go for better
hardware. Expand memory before clock speed or multi-processors. At
some point you'd be better going to client server on powerful server
hardware.. That way only the results would travel over the network to
your own PC.

HTH

 

[Baz]

Actually it's always a good idea to separate company and contact details.
If you have them in the same table then the design isn't normalised: one
company can have many contacts.

 

[JohS]

Yes, and this is base of my question (though I also agree with you Larry
that it's a bit "overboard" to split FirstName and LastName), as in this
project there are several contacts inside a company and also it is contacts
which have several companies.

In the link table I've (after first using the Lookup Wizard) set up Enforce
Referential Integrity and then on Join Type: Only include rows where the
joined fields from both tables are equal.

Is this correct? (Or is this too much "closed down", since I struggling
with finding an easy way to just enter a new text value directly on my form
(Can't this be done in the same combo or text box - I mean, just directly
add a new record if it doesn't exist from before)). JohS

 

[Baz]

Contacts with several companies? Aaaargh! Many-to-many relationship!

You're gonna need a data structure with THREE tables, something like this:

companies
=======
company_id (PK)
company_name

contacts
======
contact_id (PK)
first_name
last_name

contact_companies
=============
company_id (PK)
contact_id (PK)

In the relationships window simply relate the fields with the same names.
The form design will be a nightmare (always is with many-to-many
relationships).

 

[JohS]

Thanks for feedback. Yes, this is what I've done.

And since you are indicating that it will be difficult to do the form design
(and I suspect you are right), I'll try to se on this

www.databasedev.co.uk/not_in_list.html JohS

 

[John W. Vinson]

In the relationships window simply relate the fields with the same names.
The form design will be a nightmare (always is with many-to-many
relationships).

Well, not so much a nightmare as an everyday chore. Many to many relationships
are universal (OrderDetails, ClassEnrollment, inventory transactions, .... on
and on.)

It is a chore... but that's what the Access tools like subforms, combos and
listboxes are designed to manage!

John W. Vinson [MVP]