Database design

[sandy6078]

Hi all, I hope that someone can give me some advice.

I have been working on a database for engineering. What the engineers want is a way to search for a part number whether it is an assembly, sub-assembly, sub-sub-assembly, or component number. The engineer then wants to know all related part numbers. For example if part number 123456c91 is a component, the engineer would want to know where it is. A component (a screw) can be part of a sub-assembly and also an assembly. A sub-subassembly can be in more than one subassembly.

There is a Program.

Each Program will have many Part Families.

Each Part Family will have many Assemblies.

Each Assembly can have many Components and many SubAssemblies.

Each SubAssembly can have many Components and many Sub-SubAssemblies.

Each Sub-SubAssembly will have many components.

There will also be look-ups for supplier, material, process.

There is also a revision history is for each assembly, subassembly, sub-subassembly and component.

The assembly, sub, sub-sub, and component tables will all have an autonumber, part number, description.

I am attaching my work in progress. :banghead:

I will very much appreciate any help given.

Sandy

[XLGibbs]

Don't use autonumber. It is not a good primary key. Reason being, if you delete a row, the numbers don't adjust every. Better to develop your own set of relational keys.


You have a number of tables with 1:1 or 1: many relationships.

Set up your tables in order, begin with the one that will have the fewest unique items (Program)

Then, I would assume the program table would have a primary key available..some type of ID or existing field in that table. That feild would be in the related table Part Family and so forth.


Stay away from AutoNumber. It is not what you think it will be and will cause numerous issues later. Use fields that exist already (Program#, part#, sub part#s etc.

[mattj]

I feel I must disagree regarding the use of an autonumber as an (access) primary key. The autonumber data type provides a unique number to identify a record and provides that functionality extremely well.

The dangers and problems with the AN data type are generally caused by using it for something other than it's intended purpose, such as sequential numbering.
Using it as the relational key makes joins and relationships clearer, easier to understand, and significantly easier to create.
This doesn't stop change the fact that natural primary keys still exist, and within the Access RDBMS, should be a unique index, or in the case of a multi-field PK, a multi-field unique index.

Within Access, the autonumber provides and easy and reliable method of joining tables.


That being said, I am quite interested in the numerous issues you have experienced with it.

Matt

[sandy6078]

By using the autonumber as the PK I can insure that if the end customer changes numbering system I will not have to re-write any code etc. for the application. The PK is used soley for relational information.

I'm not sure if I have designed the database correctly. If I have designed correctly I have no clue how to write the query, or code or whatever it takes so that a user can search for a part number and see all related part numbers.

Along this line I have no idea how to write a query so that a report would print out that information.

I have been playing with the queries for weeks.

[XLGibbs]

Here is some food for thought.
http://articles.techrepublic.com.com/5100-1035_11-5032734.html

Choose carefully, as the article points out, if one of your tables using AutoNumber as a Primary Key gets corrupted, it will be impossible to recover the proper relationships.

It is a simple tool, but be wary.