Relations and Keys

[Tronco]

Im trying to create a MS Access database structure where Products are manufactured by travelling through a series of production sites in a particular order. The order is dependent on the product and its revision number.
Each of the production sites can have a number of tasks performed there - and each of these tasks can be broken down into further sub tasks.

My tables are as follows:
Product Table
Productcode
RevNumber
(these 2 together form a unique record)

Site_Order Table
Productcode
RevNumber
SiteID
OrderNo

Prod_Site Table
SiteID
TaskID

Task Table
TaskID
SiteID
Task

Sub_Task
SubTaskID
TaskID
SubTask

I'm struggling how to connect these tables with various PK and FK's - I would appreciate any help.

 

[BNPMike]

Prod_Site needs to carry the foreign key Productcode+RevNumber. Apart from that I think you are OK.

You seem to be implying you need keys to connect tables. In the relational model, joins (relationships) are formed using SQL. You only need a key (which can be any number of fields) for each relation to identify rows, and Access doesn't enforce this anyway (ie it allows tables as distinct from relations).

You can reveal the keys by putting some data in and writing queries to get information out. The fields you need to put in your queries will tell you what your primary and foreign keys need to be, but as I said you don't need to declare them to make connections.

 

[jedraw]

Tronco,

I think you will find it very beneficial to create definitions for each of your things/entities. It will help clarify what you are dealing with and will reduce your struggling.

I don't think you have clearly defined your tables.

For example:
Prod_Site Table
SiteID
TaskID
has no pruduct info.... perhaps this isn't a product-site table, but a task-site table.

You seem to have a site-order table, but no Order table, nor a Site table.

You know your data better than I do, but it seems to me you have some confusion in what you are dealing with. That will definitely make relating the tables difficult.

Good luck. Let us know how it's going.

Some links that may be useful.

http://www.datamodel.org/NormalizationRules.html#one

http://r937.com/relational.html