Normalisation query

[TimGoff]

Morning,

I have a main table containing Rights information…joined to that are supplier tables etc. In the Rights Tables I have a link to the contract..but now there is a need to add a further contract. I could add another field, ContractLink1 to the Rights Main table which will do the job. But is there any mileage in adding another table, tblLinkedContracts say , to cover the situation in the future if more than two contracts need to be added to a record? If so, have you any example database of similar situations so I can see how it was done/fields/code etc?

Thanks in advance
Tim

 

[GHolden]

If you have Many Contracts for Each Rights (Many to One) you should be putting the Key of Rights into the Contracts table rather than the other way round (Key of the One into the Many).

eg.

Rights table would be

RightsID
FieldOne
FieldTwo
etc..

Contacts table would be

ContactsID
RightsID
FieldOne
FieldTwo
etc..

This will allow you to have as many Contracts for each Right as you need, and a simple query will be able to return all contacts for each right.

Hope this helps.


There are two ways to write error-free programs; only the third one works.

 

[TimGoff]

Thanks Greg

There will probably be two contracts attached - so would I be able to get away with the two fields in the Rights main table? I suppose to keep everything as dynamic as possible I would need to create the other table as you say.

Look at your example, will I need to create a relationship between the two RightsID fields, and the ContractsID field?

Also, do you know anywhere there are database examples I can download to see this sort of thing in action?

Thanks again,
Tim

 

[GHolden]

Tim,

You could in theory get away with the two fields, however this will make any querying of the data much more difficult. Because you have two fields in one table linking to the same field in another table. Also if you did need to expand further you would have to make more changes.

Say for example you want to list all Rights and Contracts, you would have to do two queries and then merge them. One query selecting all Contracts which matched the first link then one which matched the second.

Or to find which right a contract belonged to you would again have to search more than one field.

In my example the only relationship you have to create is between the two RightsID fields.
This would be a one to many relationship, the one being in the Rights table (as this key is unique in this table) the many being in the contracts table (as the RightsID can appear more than once).

If you then want to retrive contracts for a particular Rights record, you would just select all contracts whose from the contracts table whose RightsID matched the RightsID of the record in the rights table.

This is part of database Normalisation (or Normalization if you're American), there are many very good articles about this on the web (although it can be a difficult subject to grasp initially).

If you wish I have serveral pdf documents on Normalisation which I can e-mail to you if you wish. Otherwise I will try and answer your questions as best I can.

There are two ways to write error-free programs; only the third one works.

 

[TimGoff]

That's great, thanks very much

(just noticed I called you Greg - I have no idea where I got that from!)


Yes, that would be really good if I could have a look at the documents, please send them to tim.goff@virgin.net

Thanks for your help again

Tim

 

[TimGoff]

Hi,

I now have a main Rights table, and tblLinkToContract with fields:

LinkID
RightsID (from Rights table)
LinkToContract (the path to the contact on a drive)

What I would like do happen now is when you click a button, a box pops up displaying all the contracts that are related to this Rights record. I've been using the openrecordset command , but it only brings up the first contract. Also, how could you display a small box - would you need to base it on another form?

Thanks
Tim

 

[GHolden]

Tim, I've e-mailed you a small example of one way you could view the information you're trying to get.

Graham There are two ways to write error-free programs; only the third one works.