One to One Relationship

[YNWA]

Hi,

I just need to clarify something and need someone to explain it as detailed and as easy as possible.

I have a 4 tables:

Customer
Order
Accessories
Suppliers

Now I have
PK ID in Customer table
PK ID in Order table
PK ID in Accessories table
PK ID in Suppliers table

But I also have

FK customerID in Order table
FK AccessoriesID in Order table
FK SupplierID in Accessories table

When I join up. I have 1-many realtionships. eg. ID to customerID from Customer table to Order table.

How do I create a 1-1 relationship?

I thought my Suppliers table to Accessories would be this.

eg. an accessorie only has 1 supplier BUT a supplier can be used once on multiple accessories.

What do I link to relationship wise to get 1-1? Is 1-1 really possible when Suppliers table could be a lookup table of a combo box for example?

I'm stumped and I should know this.

Cheers

 

[plog]

Where and why do you need a 1-1 relationship? Looks like your tables are set up right and you understand the relationships correctly. My thought is you're misusing the term "1-1 relationship" and just need to explain in plain english what you want.

 

[YNWA]

Where and why do you need a 1-1 relationship? Looks like your tables are set up right and you understand the relationships correctly. My thought is you're misusing the term "1-1 relationship" and just need to explain in plain english what you want.

I am doing some coursework for my cousin. His specs say 1-1 relationship between Accessories tbl and Supplier tbl.

I get the concept of why it is 1-1 as 1 accessory only has 1 supplier linked to it. But in practice its not 1-1 as many people can have the same supplier.

I have asked why and no body knows.

I thought I'd missed a big chunk of DB design somewhere but I doubt it.

Thanks for your help.

 

[jdraw]

Here are some definitions, but I'm sure you've seen these or others.

    One-to-one relationships occur when there is exactly one record in TableA that 
corresponds to exactly one record in TableB.
    One-to-many relationships occur when each record in TableA may have many linked 
records in TableB but each record in TableB may have only one corresponding record in
 TableA.
    Many-to-many relationships occur when each record in TableA may have many linked
 records in TableB and vice-versa.

I don't think your wording here goes with the relationship.

I get the concept of why it is 1-1 as 1 accessory only has 1 supplier linked to it. But in practice its not 1-1 as many people can have the same supplier.

If Supplier and Accessory is 1 to 1, then it's saying only 1 supplier supplies this 1 accessory, and this 1 accessory is only supplied by 1 Supplier. I don't think it has anything to do with "other people having the same Supplier".

I don't think there are many situations/examples of 1:1 relationships. Sometimes they are used to keep private/confidential info hidden from the general user.

I'm sure there are examples, but in practice, not too common.

 

[Pat Hartman]

1-1 in this case actually means that the supplier supplies only ONE accessory which doesn't make sense. To make a 1-1 relationship you need to connect the tables PK to PK so the PK of the supplier table would be an autonumber but the PK of the accessory table would be long integer since you would never need to generate a unique ID for it. If the supplier supplied multiple accessories, the accessory table would have a unique PK and a FK to supplier. If the same accessory is made by multiple suppliers, you have a many-to-many relationship which would be implemented with a junction table.

Your schema also seems to be missing an OrderDetails table. It is rare that a company offers one and only one product and once you offer more than one product, you need a way to have an order that includes multiple products. Hence, tblOrderDetails as a child of tblOrder.

In my personal opinion, naming all the PKs ID is simply wrong and I'm not sure where that trend started but I'd like to stop it. Each PK should have a meaningful name that represents the table it controls so in tblCustomer, the PK should be named CustomerID and then when you see CustomerID in tblOrder, you have some clue that you are looking at a FK and what table you would find it in without actually having to open the relationships window and trace the relationships.

 

[YNWA]

1-1 in this case actually means that the supplier supplies only ONE accessory which doesn't make sense. To make a 1-1 relationship you need to connect the tables PK to PK so the PK of the supplier table would be an autonumber but the PK of the accessory table would be long integer since you would never need to generate a unique ID for it. If the supplier supplied multiple accessories, the accessory table would have a unique PK and a FK to supplier. If the same accessory is made by multiple suppliers, you have a many-to-many relationship which would be implemented with a junction table.

Your schema also seems to be missing an OrderDetails table. It is rare that a company offers one and only one product and once you offer more than one product, you need a way to have an order that includes multiple products. Hence, tblOrderDetails as a child of tblOrder.

In my personal opinion, naming all the PKs ID is simply wrong and I'm not sure where that trend started but I'd like to stop it. Each PK should have a meaningful name that represents the table it controls so in tblCustomer, the PK should be named CustomerID and then when you see CustomerID in tblOrder, you have some clue that you are looking at a FK and what table you would find it in without actually having to open the relationships window and trace the relationships.

Thanks.

So would you say ID naming is wrong or just bad?

This is a piece of coursework, so its not a live official product.

The tables were set out before hand eg. they are the only table they need to build.

To change to 1-1 would I just need to link my Accessory table ID to PK ID in supplier table ?

 

[YNWA]

This is the spec they were given...

Customer

Manufacturer

Supplier
(Accessories)

Order/
Specification


Pack

Accessories































A customer can make many orders

An order involves 1 pack

A pack is ordered from 1 manufacturer

An order includes many accessories

But 1 accessory is supplied from 1 supplier

 

[YNWA]

See attached for what I did to get the 1-1.

Is this correct?

Bear in mind the first stage is tables/relationships.

Then next month they will need to do queries and forms.

Do i need the SupplierID in the Accessories table if I am not 1-many?

 

[RainLover]

A customer can make many orders

An order involves 1 pack

A pack is ordered from 1 manufacturer

An order includes many accessories

But 1 accessory is supplied from 1 supplier

There are no One to One Relationships in the specs. They are all One to Many. You misread the spec which could be easilly done by a person with limited experience.

If One accessory is supplied by one supplier, then it follows that the One supply many accessories.

You need a Primary Key (Autonumber) on the One side and a Foreign Key of type Number (Long) on the Many Side. The Many Side also has its own Primary Key of Autonumber.

Best you look at some sample Databases to get an understanding then ask more questions.

 

[RainLover]

One other thing.

If you are going to post a sample database, please convert it to A 2003 as not all people here have later versions.

 

[YNWA]

There are no One to One Relationships in the specs. They are all One to Many. You misread the spec which could be easilly done by a person with limited experience.

If One accessory is supplied by one supplier, then it follows that the One supply many accessories.

You need a Primary Key (Autonumber) on the One side and a Foreign Key of type Number (Long) on the Many Side. The Many Side also has its own Primary Key of Autonumber.

Best you look at some sample Databases to get an understanding then ask more questions.

I know what I am doing. I am just confused by the piece of coursework my cousin has given me to look at.

The diagram included in the coursework has a relationship diagram and they are asking for 1-1 joins from Accessories to Supplier tables and so on.

See attached for the image of the diagram.

I was on the same lines as you with the 1-many. But they seem to want 1-1.

 

[RainLover]

I can see what you are saying, however something is wrong.

If you are reading it the way the diagram says then all the information from Manufacturer and Pack could be included in Order. Then Supplier could be included in Accessories.

This would leave just three tables. Customer, Order and Accessories.

Well that is what you are saying and yes it does look like that. The only conclusion I can make is that the question is wrong or some vital information is missing.

I don't know what you can do about it except to go back to your cousin and ask more questions.

BTW

The way to create a One to One is for a PK in one table which matches a FK in the other. The Foregin key must be set to no duplicates.

In all the time I have been dealing with Access I have only used One to One to put a Memo field into a separate table. This was done as some say that a Memo field is the most likely to become corrupt.

 

[stopher]

YNWA

Whilst I agree wholeheartedly with the advice being given here for real application, do take caution that taught theory is often abstracted from the real world. One thing my tutor said when completing assignments is "don't use you're knowledge of the real world to influence your solution according to a given spec.

In database "theory", an ID is the unique identifier for the respective table. So for a car, the unique identifier would be the registration plate. In practice, a database designer would introduce a meaningless surrogate key as the PK as using car reg would cause problems if say, the reg changed. But surrogate keys may or may not be part of your taught course.

So in you example, I would expect PackID to actually be a meaningful pack code (the code it is know by to people who handle it and may be printed on the product) and manufacturerID to be a meaningful manufacturer ID (maybe quoted on the purchase orders for example). So the two won't match! Therefore you can't use the PK<>PK method described by Pat. The way I was taught to handle 1:1 is to intoduce a FK in one of the tables and set it to Unique. This can be implemented either way round in your example (I assume the cross line means "optional"). If madatory you need to make the FK Not Null. If both ends mandatory then in an industrial database engine you would have to implement a check for madatory participation at the on-FK end. Hope all that makes sense.

As mentioned my comments here are purely based on my own academic experiece and I know that different institutions take a different stance so Pats approach my well be right.

hth
Chris

 

[stopher]

The way to create a One to One is for a PK in one table which matches a FK in the other. The Foregin key must be set to no duplicates.

Sorry Rainlover, I was still typing when you answered before me.

 

[RainLover]

Sorry Rainlover, I was still typing when you answered before me.

No problem, besides you reminded me of a project I handed in using Pascal. I was told to redo it, not because it was wrong but because I used things we had not yet learnt. I had to do the project with what I was given not what I knew.

I don't know if this has anything to do with the question, but I thought I might just mention it.

 

[plog]

I think everyone is missing the big, big picture: He's having us do homework which he in turn is doing for his cousin.

I am doing some coursework for my cousin

 

[RainLover]

I think everyone is missing the big, big picture: He's having us do homework which he in turn is doing for his cousin.

Please read post # 12. " I don't know what you can do about it except to go back to your cousin and ask more questions. "

I know I am missing something but doing work for his cousin is not one of them.

 

[YNWA]

I think everyone is missing the big, big picture: He's having us do homework which he in turn is doing for his cousin.

Whats thi smeant to mean?

I am trying to help him out but am confused as to why they are asking for 1-1 relationships when all I have ever known is 1-many.

 

[RainLover]

I worked it out.

Some people think we should not help people who are working on a school project. Why they think like this I have no idea.

Someone made a statement years ago and a buch of people have spread the word without know why. It is a bit like herding sheep. Get one through the gate and the rest will follow without question.

Plog I did not mean that you were a sheep. Just an analogy.

 

[spikepl]

@RainLover

Because school assignments are about two things: the end product but also the process of getting there. Doing an assignment for somebody deprives that person of the intended benefit of learning how to solve problems on one's own. Helping out is a different thing (but does not apply in this instance where you'd actually do the job for a third party).