Is this the 'right' design? (table w/o PK and multi relationships) (1 Viewer)

machumpion

Registered User.
Local time
Today, 11:42
Joined
May 26, 2016
Messages
93
G'day!

I want to store the unit prices I sell my products to distributors for.

I have distributors to whom i sell my products to. Prices for products vary depending on Container (i.e. bag, jar, case etc) and type (fruit, vegetable, meat etc.). In addition, sometimes the prices vary depending on which distributor I sell to.

These attributes are in the 'Products' table. Distributors are in the 'distributor' table.

I tried to make a junction table [distrbcost] featuring 'distributor', 'container', 'type' and 'unitcost'. This table has no primary key, because container, type, distributor and unitcost are not unique IDs.

From [distrbcost], i joined [distributor] to [distributor].[distributor] and [type] to [products].[type], and [container] to [products].[container]. (see attached)

Later on, it seems Access automatically made 4 new "sub" Products tables (Products_1, Products_2.. Products_4). Running a query of the products against a distributor's orders seems to be working correctly. Is this the optimal way to design form this relationship? What is the deal with the extra Product_x tables?
 

Attachments

  • access product relationship.PNG
    access product relationship.PNG
    41.3 KB · Views: 200

jdraw

Super Moderator
Staff member
Local time
Today, 11:42
Joined
Jan 23, 2006
Messages
15,364
For clarity, what exactly do you mean by unit?

I recommend you provide a few sample products to help put the statements into context.
 

machumpion

Registered User.
Local time
Today, 11:42
Joined
May 26, 2016
Messages
93
For clarity, what exactly do you mean by unit?

I recommend you provide a few sample products to help put the statements into context.

1 unit is some combination of container & type could be a jar of fruits, or a bag of meat.

I might charge Johnny Distributor $5 for a jar of fruits, but charge Mick the Distributor $7 for a jar of fruits.
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:42
Joined
Jan 23, 2006
Messages
15,364
Some facts to help with disccussion:

You sell products to distributors
Producttype can be fruit, vegetable or meat.....
SellingUnits represent the Container of product(s) sold.
A SellingUnit may be a bag, jar,case......

The SellingPrice varies depending on the SellingUnit and the ProductType and "sometimes" the Distributor involved.

Consider the following :
You sell 2 bags of Product X (which is ProductType "fruit") to Distributor John for $8/bag.

How exactly would you determine the selling price of a bag of Product X to distributor John in this transaction?

If this sale was to Distributor Sam, what is the logic for the sale? That is, what changes between John and Sam?

You said that you want to store the unit prices. What do you do with these? Are there some already defined processes/processing?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:42
Joined
Feb 28, 2001
Messages
26,999
This table has no primary key, because container, type, distributor and unitcost are not unique IDs.

Clarify please: Are they not unique IN THIS TABLE or are they not even unique in the individual tables wherein they are individually defined (i.e. a distributor table, container table, etc.) My question is partly rhetorical because if that statement is globally true, you DO NOT have a database. You have a complex set of spreadsheets.

For your clarification, you need to learn or review the concept of compound keys, where more than one field is a PK when used in combination but not when taken in isolation.

From a design viewpoint, it is perfectly OK to define a table in which the individual records are unique only for specific combinations of contributor keys. One can also argue that structurally, if this complicated cost table has no dependents, it might not actually need a "true" PK. But it might help performance if the contributing keys that were recorded in the table were at least indexed with Allow Dups.

Obviously, in this structure, this price table depends on what was sold, to whom, and in what units (and further, if there is a quantity discount, the number of units in a sale). So it is a dependent to many independent factors. Where your structure would totally fail is if something else would then depend on this table.
 

machumpion

Registered User.
Local time
Today, 11:42
Joined
May 26, 2016
Messages
93
Some facts to help with disccussion:

You sell products to distributors
Producttype can be fruit, vegetable or meat.....
SellingUnits represent the Container of product(s) sold.
A SellingUnit may be a bag, jar,case......

The SellingPrice varies depending on the SellingUnit and the ProductType and "sometimes" the Distributor involved.

Consider the following :
You sell 2 bags of Product X (which is ProductType "fruit") to Distributor John for $8/bag.
How exactly would you determine the selling price of a bag of Product X to distributor John in this transaction?
The $8 price per bag is a set price for John that I decide and would be stored in the DistrbCost table.

Container: Bag
Type: Fruit
Distributor: John
Unitcost: 8

John's order of 2 bags would be in the Orders table with the fields
ProductID: Fruit Bag-Apple
Units: 2
Distributor: John
Date
Amount

Amount
is the value of John's order if he sells the two bags of fruit to his customers. (i.e. $20 per Product X =$40) I do not want to use this amount. I want to use my price to him of $8 ($16= 2 bags * $8)
If this sale was to Distributor Sam, what is the logic for the sale? That is, what changes between John and Sam?

the price to Sam may appear in the DistrbCost table as
Container: Bag
Type: Fruit
Distributor: Sam
UnitCost: 10

You said that you want to store the unit prices. What do you do with these? Are there some already defined processes/processing?
I wish to calculate a distributor's product orders using the price I charge them [UnitCost] in a particular period.
 

machumpion

Registered User.
Local time
Today, 11:42
Joined
May 26, 2016
Messages
93
Clarify please: Are they not unique IN THIS TABLE or are they not even unique in the individual tables wherein they are individually defined (i.e. a distributor table, container table, etc.) My question is partly rhetorical because if that statement is globally true, you DO NOT have a database. You have a complex set of spreadsheets.

For your clarification, you need to learn or review the concept of compound keys, where more than one field is a PK when used in combination but not when taken in isolation.

From a design viewpoint, it is perfectly OK to define a table in which the individual records are unique only for specific combinations of contributor keys. One can also argue that structurally, if this complicated cost table has no dependents, it might not actually need a "true" PK. But it might help performance if the contributing keys that were recorded in the table were at least indexed with Allow Dups.

Obviously, in this structure, this price table depends on what was sold, to whom, and in what units (and further, if there is a quantity discount, the number of units in a sale). So it is a dependent to many independent factors. Where your structure would totally fail is if something else would then depend on this table.

The various containers (etc jar)and types (meat) are not unique in any table. The productID in the Products table features different spellings of potentially identical products from distributor reports I receive. (i.e. Apple Bag- 16 Count; Large Apple Bag). These productIDs are unique, but both these IDs would have
Container: Bag
Type: Fruit

I'm going to do some research on compound keys. Thanks for the advice.
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:42
Joined
Jan 23, 2006
Messages
15,364
Info re Composite unique index

This is probably relevant to your research.

I think you have to get all of your facts organized and then develop a model that supports your business facts. We don't know what exactly is involved in a sale to John for $8, and same sale to Sam is $10. Whatever is involved is a business fact that you understand, and must be accounted for in your tables, relationships or logic.

You haven't mentioned Distributors' customers previously, and I'm not sure if they are in scope or not for your application. My guess is they are not in scope, but again a complete set of business fatcs would identify same.

Good luck.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:42
Joined
Feb 28, 2001
Messages
26,999
When I read your description, I believe you have violated (or are trying to violate) one of the Old Programmer's Rules: If you can't do it on paper, you can't do it in Access. If you don't have precise relationships defined on paper, then you are shooting from the hip and hoping that Access will show you how to do something. Which leads to the OTHER Old Programmer's Rule: Access won't tell you anything you didn't tell it first, or at least tell it how to do that thing.

jdraw and I both see disorganization in your data sets. You are either working from an incomplete picture OR you are having a bad problem with "forest and trees" syndrome. You are having trouble focusing on the big picture, in other words.

The various containers (etc jar)and types (meat) are not unique in any table.

If you are dealing as a pass-through broker and what you buy is exactly what you sell, unchanged, then the containers are descriptive but otherwise irrelevant other than as names to be printed on invoices. On the other hand, if your prices derive from content and quantity of the containers (i.e. the stuff inside the containers counts for something) then you have a true nightmare on your hands because you have NO visible way to set prices. So, the first question is: What are you selling?
 

Users who are viewing this thread

Top Bottom