Table relationships

[rhysmeister]

It has been a while since I've used access so I'm a little foggy on a few concepts. I have just built a database, one table has a primary key called 'domain name' I have linked this to 'domain name' in a few other tables. The idea was that an entry into one table would affect all the other tables, ie. entering

http://www.domain.com

in the 'contact details' table would cause it to appear in the linked tables. I have selected the 'referential integrity' boxes but this is not happening. Can anyone help?

 

[GoDawgs]

You need to do some reading up on databases first of all...the goal is to not repeat any data in the tables. So basically in your case you want to have a domain ID of some kind and then store the domain name in another field like below:

domainID domainname
-------- ----------
1

http://www.domain.com

2

http://www.something.com

Then in your other tables (contact details) you only store the domainID (1). You then use queries to pull all the data together...that way if the domain name changes you only need to change it in the domain table one time and when you pull the data for domainID 1 you will get the correct info. Check out some basic database design books/sites to get you started. Hope that helps.

Kevin

 

[RickSpr]

Actually, to refine what Kevin said, the goal is not to have redundant data except for foreign keys, and it seems to me that's what you've set up here.

Kevin's suggestion is that you create a DomainID as the primary key of your Domains table, and leave DomainName as a non-key field. There are different opinions on this, but that would be my approach as well. I like to have all my primary keys be composed of fields that the user seldom or never sees at all. The advantage is that it avoids cascading updates when the field the user does see (in this case DomainName) has to be updated. The disadvantage is that you will have to join tables more often to get the information you need in a recordset. In this case, for example, each time you use a table that contains a DomainID foreign key, in a context where you want to show the DomainName, you'll have to join the table with the Domains table. If you carry DomainName as a foreign key, you wouldn't necessarily need to do the join.

To address your problem: The "cascade updates" features doesn't do what you want to do here. That is, it doesn't update the parent table (Domains) when you enter a value in the DomainName foreign key in the child table. You can get part of what you want, though, if you base your form (I assume you're using a form) on a join between the Domains table and the child table, and use the DomainName column from the parent table on your form instead of the DomainName foreign key from the child table. Doing this will automatically add a row to the parent table when you enter a new domain name in the form.

However, nothing will make the domain name suddenly appear in other child tables that are not in the form's recordset. How could this work? You would have no fields on the form for any of the fields (other than DomainName) in the other child tables.

To pursue your solution, I need you to describe what kinds of information are in the other child tables, what their primary keys are, and in a general way what kind of processing you need to do with them.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[rhysmeister]

My main table is called "Contact Details" "domain name" is the primary key here. Then I have come child tables "Domain name", "Services" and "Name Server" all these have an autonumber primary key and a foreign key of "domain name". I also have a couple of grand child tables but I don't think they need to be mentioned here. I would like some kind of feature where if I delete an entry from the parent table then all entries related to it would also be deleted, same goes for additons, updates ect. As for queries I think I can handle those. Thanks for your time.

 

[RickSpr]

Ah, well, I see why we were a little confused--you have a DomainName column and a DomainName table, which is confusing enough, but on top of that the column isn't the primary key of the table, but of a different table. That's legal, but very odd. It implies that many rows in the DomainName table can have the same domain name. Since names are natural "unique" identifiers, that seems unlikely. I have to wonder whether you have analyzed your relationships correctly.

In any event, with this structure your Update Cascade and Delete Cascade rules will work correctly. If you update a domain name in Contact Details, it will be updated automatically in the child tables. If you delete a row from Contact Details, the corresponding rows in the child tables will be deleted.

There is no Insert Cascade rule, however. Again, how could there be? The relationships from Contact Details to the other tables are one-to-many, so inserting a new domain name in Contact Details could mean that there are potentially many rows to insert in the child tables. How could Access know how many are needed in each table? And what would it put into the other columns of the child tables (other than the Autonumber keys)?

If you create one big join with the Contact Details table and all the child tables in it, you could then create a form based on that join, with which you can simultaneously insert rows into Contact Details and the child tables. The join query must contain all the DomainName columns, and the Contact Details.Domain Name column must be the one that appears on the form. However, you could only use this form to add the first row in any child table for a given domain name. If you had a second Service for this domain name, for example, you'd need a different form.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[rhysmeister]

Thanks, you're right I will remove the autonumber ID's seems silly having them there. The form you were talking about it seems the easist way to go. I will have a look into this area. Thanks again.