How do I create Assemblies?

[verplexd]

Hi all,

I'm trying to create an inventory control system in MS Access 2000. I have the standard Inventory Table with partNumber, quantity, description, etc. However I would like to create "assemblies". I beleive that an assembly is another inventory part that is made up of more than one inventory part.

Example:
FanAssembly is make up of:
1 red wire
1 black wire
1 fan
1 mounting bracket
4 screws

I could create the assembly with a simple assembly table, but I would really like it to be an entry in the Inventory Table so that I could make different items out of more than one FanAssembly and/or other parts.

I think I may have known how to do this at one point during school, but I have mostly forgotten. Does anyone have any advice (ie. a good book to read, an example of an assembly)?


Thanks in advance,
Jason

 

[skiflyer]

I don't have much time, but let me write a couple quick things which might kick start your brain.

Table: Parts
------------
parts_id
part_name
part_price
...


Table: Assembly
---------------
assembly_id
assembly_name


Now join them with a many to many relationship, yielding this table

Table: Assembly_Parts
---------------------
assembly_id as foreign key to Assembly.assembly_id
parts_id as foreign key to Parts.parts_id

Complex assemblies could work the same way, except
And either assembly_id or parts_id would be null in each case... or maybe you'd just use exactly the same method as above except assembly and parts ID's would be different as a business rule and you could parse out the details later, using recursion in your front end... many dangers there as far as data integrity (circular references mostly).

Table: Complex_assembly_parts
-----------------------------
complex_assembly_id
assembly_id
parts_id

-Rob

 

[ModelTrains]

I like Rob's reply, but just to give you options, here is a different approach.

If you want the assembly to be a part, you could create the following records:

PARTS TABLE:
parts_id, part_name
0001, "fan"
0002, "mounting bracket"
0003, "red wire"
0009, "fan assembly"

ASSEMBLY TABLE:
assembly_id, assembly_part_id
0009, 0001
0009, 0002
0009, 0003

By having the assembly in the PARTS table, you can select it as if it was another part. This would allow multiple levels of assemblies. Your program can check for the existance of records in the assembly table.

PARTS TABLE:
parts_id, part_name
...
0009, "fan assembly"
0010, "transformer"
0011, "safety case"
0012, "power supply assembly"

ASSEMBLY TABLE:
assembly_id, assembly_part_id
0012, 0009
0012, 0011

The ASSEMBLY table should never have a record where part_id=0003 (red wire). You will also have to be careful with data integrity. You should not allow the ASSEMBLY table to have an assembly_id equal to the assembly_part_id. Such as:

ASSEMBLY TABLE:
assembly_id, assembly_part_id
0012, 0009 <--an assembly can be part of another assembly
0012, 0011
0012, 0012 <--bad record

Mike

 

[verplexd]

Thanks for the replies guys. I will try to implement this and let you know how it goes.

 

[repete]

for what its worth.....

i would also add a quantity field in mike's assembly table

Pete Bloomfield
Down Under

 

[SemperFiDownUnda]

The parts table I would have hold assemblies also
the Assemblies would describe what other parts are needed for the assembly.

given
CREATE TABLE Part(
PartID int NOT NULL ,
.
.
.
)
do
CREATE TABLE Assembly (
AssemblyID int NOT NULL ,
PartID int NOT NULL ,
Qty int NOT NULL
)

ALTER TABLE Assembly
ADD
CONSTRAINT PK_Assembly PRIMARY KEY
(
AssemblyID,
PartID
)GO


ALTER TABLE Assembly
ADD
CONSTRAINT FK_Assembly_Part FOREIGN KEY
(
AssemblyID
) REFERENCES Part
(
PartID
),
CONSTRAINT FK_Assembly_Part1 FOREIGN KEY
(
PartID
) REFERENCES Part
(
PartID
)

Then you can have assemblies of assemblies of assemblies etc