Need some help Urgent (1 Viewer)

Brain · Jan 5, 2009

Hi. I need some help for my online shopping table prototype. I want to be able to allow data to be entered which shows that customers can buy the same products. I can't do this because the Product ID is a primary key in the product table. So if someone wants to buy a bread say, then the database can only all allow one person to be able to purchase the bread.

Here is a picture of the relationship. There needs to be something that links Order details table to Product table so that I can have duplicate Product ID. I don't know if this is possible and if so how.

Any help would be very appreciated.

dkinley · Jan 5, 2009

Think of OrderDetails like a junction table ...

Remove the relationship between OrderDetails and Products and instead, create a ProductID foreign key in OrderDetails (basically reversing the relationship you do have but on different fields).

In this manner, you can have many Order Details for each Order, and many of the same Products for each Order Detail.

However, in your existing set-up - if you have the Qty field in OrderDetails - then they can order more than one quantity of Bread. They just can't place two distinct orders of bread with different quantities. Depending on interpretation, this design also stands without thinking of other areas where it could fail.

-dK

Brain · Jan 5, 2009

dkinley said:
Think of OrderDetails like a junction table ...

Remove the relationship between OrderDetails and Products and instead, create a ProductID foreign key in OrderDetails (basically reversing the relationship you do have but on different fields).

In this manner, you can have many Order Details for each Order, and many of the same Products for each Order Detail.

However, in your existing set-up - if you have the Qty field in OrderDetails - then they can order more than one quantity of Bread. They just can't place two distinct orders of bread with different quantities. Depending on interpretation, this design also stands without thinking of other areas where it could fail.

-dK

I've done the second point you have made but the problem still remains in that a different person can't order the same product (e.g. Bread).
I'm not fully in understanding of your first point however. How do I relate it and does it allow more than one person to order the same product. Thanks very much in advance again.

dkinley · Jan 5, 2009

By reversing the relationship, Products basically becomes a look-up table to populate OrderDetails with. Again, this is putting a foreign key of Products into OrderDetails and providing the relationship to it (and removing the foreign key of OrderDetails in Products).

Such as Orders is to Customers, Products would be to OrderDetails.

-dK

Brain · Jan 5, 2009

Hi thanks for the help. Just one thing. When you get rid of the order details ID in the Products table this means the table won't have a one to many relationship. This is what you have told me (hope I'm not wrong):

This is what my customer table has when you click on the plus signs.

The above image should show one more data of the products such as name, price from the Product Table.

Thanks

dkinley · Jan 5, 2009

Correct. The expansion you want (Item, Price) can be achieved through a left-join query. You are using OrderDetails as a junction table so it will not 'expand' correctly through the table view. The tables no longer represent that nice waterfall look where one spills over and into another to fill it up.

What we have done is to allow Products to exist independant of the rest of the tables. In this manner, to update Products - does not require interaction of the rest of the tables. It can be done through this single source. The ProductID is stored in OrderDetails. It is through this ID the link is created with a left-join that will allow the Order to be fully populated with the required data (Item, Price) and also allow the duplication requirment in your original post.

This help?
-dK

Brain · Jan 5, 2009

So what I have done is correct? However is this normalized? Just in case I want to do a query. Thanks a million.

boblarson · Jan 5, 2009

Brain said:
So what I have done is correct? However is this normalized? Just in case I want to do a query. Thanks a million.

Yes, what you have done, based on dkinley's suggestion, is correctly normalized, at least from what I see.

dkinley · Jan 5, 2009

I would say that this is normalised; however, I never studied it formally so I couldn't say with exact precision (start getting confused after 3NF) ...

The fact is we are storing a number in lieu of data (the Product foreign key field) into Order Details. The data side is presented when the query is executed - until then, it's just a number.

Even though this isn't a formal junction table (those are for many-to-many to my knowledge), you could search this site for that phrase. There are far better definitions and applications thereofs that could provide a better explanation that I ever could. I pose this as more of a look-up table. Could you do this at the table level? Yes. Do you want to? No, because there are known problems in Access in using lookup fields.

I do not anticipate any problems with queries at all or any impact to your application when running them.

-dK

Brain · Jan 5, 2009

Thanks Dkinley for your help it has been fully appreciated. It works so thanks for getting me thus far.
Thanks also Bob Larson.

Need some help Urgent (1 Viewer)

Brain

Registered User.

dkinley

Access Hack by Choice

Brain

Registered User.

dkinley

Access Hack by Choice

Brain

Registered User.

dkinley

Access Hack by Choice

Brain

Registered User.

boblarson

Smeghead

dkinley

Access Hack by Choice

Brain

Registered User.

Similar threads

Users who are viewing this thread