One to One or One to Many

[amerifax]

What would be the advantage or problem with the following:

Let me give you an example of a few tables we have and tell me how the joins types would be set up.

I have a Model table. In the Model table I have an ID for Builder (which is a table), an ID for Subdivision (which is a table) and an ID for City (which is a table). How exactly are the relationships set up because this is just how Model will join to these other tables. The other tables will join to each other in different ways.

Is there an advantage to setting up all the relationships or joins ahead of time? Would it be benefitial as far as the speed to access the different tables or would it slow the system down becuase it is looking at all these relationships. At any time I could have 8-10 tables that relate.

 

[MSAccessRookie]

What would be the advantage or problem with the following:

Let me give you an example of a few tables we have and tell me how the joins types would be set up.

I have a Model table. In the Model table I have an ID for Builder (which is a table), an ID for Subdivision (which is a table) and an ID for City (which is a table). How exactly are the relationships set up because this is just how Model will join to these other tables. The other tables will join to each other in different ways.

Is there an advantage to setting up all the relationships or joins ahead of time? Would it be benefitial as far as the speed to access the different tables or would it slow the system down becuase it is looking at all these relationships. At any time I could have 8-10 tables that relate.

A relationship can be created by adding a field of Type Number representing the table that is being related to any table requiring one.

For Instance:

Model Table

Primary Key - AutoNumber
Model Name - Text
Builder ID - Number (Refers to Key in Builders table)
Subdivision ID - Number (Refers to Key in Subdivision table)

{ Other Foreign Keys as required }

The more relationships you have defined, the easier it is to be assured that your application will work the way you want it to

 

[amerifax]

My propblem was understanding how to save the relationships for all the many ways I use them. I just realized that the relationship is saved when you save the database. "Teach Your Selp Visually - Access 2007".

Thanks for the help.

Bob

 

[gemma-the-husky]

the relationships arent that critical - but they may be helpful

they may help you understand/define your system, and you can enforce referential integrity, eg by saying that for every model, you HAVE to have an existing builder. With ref integrity, you wouldnt be able to delete a builder, say, if you have used the builder on an existing model - therefore you dont get orphan/floating records

When you design queries in access, access will automatically link tables where a reference is set

i think it may help access internally when access works out how to satisfy your queries

but everything will work in the same logical way without the references

 

[boblarson]

Relationships define Referential Integrity and, really if you think that because you set a relationship one way and then you believe that you will need to do it another way at some time, I am going to argue that you haven't designed your database correctly then. There should be no need to redefine relationships (with the possible exception of that you just found you made a mistake in originally setting things up). If you have a one-to-one relationship then it is a one-to-one relationship and from this point on out it should always be a one-to-one relationship. If, however, you think that at any time something could have a many side to the one then you would define it as such, regardless of whether the majority of the records are going to be one-to-one.

 

[gemma-the-husky]

bear in mind that one to one relationships are not usual

if if for every instance of an item in tableA, there is at most one instance of a matching item in tableb, then it follows that there is no real need to separate tablea and tableb, and a single table would suffice

there are reasons why one to one realtionships MAY be used, but there really to do with security concerns perhaps

 

[boblarson]

bear in mind that one to one relationships are not usual

if if for every instance of an item in tableA, there is at most one instance of a matching item in tableb, then it follows that there is no real need to separate tablea and tableb, and a single table would suffice

there are reasons why one to one realtionships MAY be used, but there really to do with security concerns perhaps

Agreed....

 

[amerifax]

>>they may help you understand/define your system, and you can enforce referential integrity, eg by saying that for every model, you HAVE to have an existing builder. With ref integrity, you wouldnt be able to delete a builder, say, if you have used the builder on an existing model - therefore you dont get orphan/floating records<<

It seems that the value of relationship is to prevent deleting needed records. I thought it was the basis for the links.

Bob

 

[boblarson]

It seems that the value of relationship is to prevent deleting needed records. I thought it was the basis for the links.

The value of a relationship is to ensure the integrity of your data. In other words, without R.I. your users might be able to add a record to the many side of a one-to-many relationship without adding the one. Or they could delete the one, leaving the many. As mentioned that would create "orphan" records. Records that SHOULD have a related record somewhere but don't. Therefore the integrity of your data would be suspect. Can anyone believe that the data is correct or complete? Setting the relationships and selecting the appropriate R.I. settings is an extra tool that allows Access to manage what would otherwise require a lot of work on your part to ensure data integrity.

 

[amerifax]

I think I have it know.

Thanks for the help
Bob

 

[Pat Hartman]

You seem to be confusing defined relationships which enforce RI with joins. The cardinality of defined relationships is determined by the database engine (Jet), not you. When you join tblA to tblB on primary key to primary key, the presumed relationship is 1-1. When you join tblA to tblB on primary key to data field or partial primary key, the presumed relationship is 1-many. Many-Many relationships are simply two separate 1-many relationships that come together in a single junction table.

Tables and queries can be joined to other tables and queries in the query designer window. These are not relationships and they do not have any impact on RI. they do not need to mimic the defined relationships although you do need to understand what you are trying to accomplish when you join on non-relationship fields. Usually the results of these joins are nonsensical.

 

[amerifax]

Pat,

We thought we understood before but you noticed that we really didn't. It's nice to have someone out there who wants to make sure you really understand it, even though you think you are clear on it. I do understand now, thanks for pointing that out to me, I was crossing the meaning on them. Everyone here has been really helpful.

Thanks again,

Bob

 

[Shadow Stewart]

With one to one relationship type you just update one record in two or more tables at a time but when you use one to many relationship type you can update many records at a time.


Shadow Stewart

 

[gemma-the-husky]

shadow stewart

its not quite that i think

if you have a relationship thats one to one, it implies that there is no need for that realtionship - since you could get the same effect by storing all the data in one table.

ie a one-to-one realtionship says that for each item in tableA, there will only be one item in tableB

so why would you have a one-to-one table

i can think of two reasons

a) TableA contains different types of similar data, eg products, but for some products you store additional information. So rather than store nulls/blanks for those fields, for all the other products, you have an extra one-to-one table (TableB) for those items where you do need the extra stuff

b) TableA stores basic data (eg staff information)
TableB stores related data but more sensitive (say salary levels, bank details)

having some data in tableB, makes it easy to apply different security settings to each Table - you are probably happy for anyone to see employee names/addresses/phone numbers etc, buit you want to limit access to salary details/NI numbers etc

 

[The_Doc_Man]

Why do you define relationships ahead of time?

1. If you first define your tables and the relationships between them, then Access can see that information and take it into account when you build multi-table queries. Access will correctly build the JOIN for you if YOU correctly built the relationship. It knows which fields are involved and whether the join is INNER and LEFT or RIGHT. (You're still on your own for OUTER JOIN.)

2. When executing JOIN queries, Access can see the relationship and KNOW whether a given key is or is not applicable. For pre-processed queries, this drastically helps Jet "know" how to optimize what it is doing.

3. When attempting to add or delete records in one table, Access can know what is required to maintain multi-table consistency. (This is relational integrity in action - stopping deletes that would leave something dangling, deleting children of a departing parent, etc..)

4. Having properly-defined relationships makes the relationships window a nice graphics view of what tables are related to each other. Very visual.

5. The most important reason: If you CAN define the relationship because you know it is applicable, you have studied the problem enough to understand it and work with the design. If you don't know ahead of time that a given relationship exists, you probably don't understand your problem well enough to be implementing it yet.