Need help with table normalization

[KurtL]

Im setting up a database that contains information about printed circuit boards that are being developed for our current project. The information that i need includes the following:

Board # - 3 digit number
Board Name -
Module # - 9-digit number
Module Name -
Project Leader - engineer responsible for design
In/Out/Oem - inhouse, outsourced, or purchased OEM
Designer - engineer or outsource contractor who designed the board
MFG/Assembler - company responsible for building the boards
Directory - file on network drive the drawings are located in

There can be mutiple boards in a single module. So i have 3 tables right now.

pcb_tbl - this includes all the info above minus the module info.

Module_tbl - this table includes the module number and name and responsible engineer, with the module number being the key.

module_pcb_tbl - this table lists the module number in the first field linked to the module table, and the pcb number in the second field linked to the PCB table.

Am i doing this right?

 

[cuttsy]

Questions:

Can one board be part of more than one module?
Can one engineer be the leader of more than one project?
Can one project contain more than one module?
Can one module be designed by more than one engineer/contractor?
If Yes: Can one board be designed by more than one engineer/contractor?
Can one module be assembled by more than one Assembler?
If Yes: Can one board be assembled by more than one Assembler?

Can a module containing many boards have one board that is inhouse and one board that is outsourced?

 

[KurtL]

Questions:

Can one board be part of more than one module?
Can one engineer be the leader of more than one project?
Can one project contain more than one module?
Can one module be designed by more than one engineer/contractor?
If Yes: Can one board be designed by more than one engineer/contractor?
Can one module be assembled by more than one Assembler?
If Yes: Can one board be assembled by more than one Assembler?

Can a module containing many boards have one board that is inhouse and one board that is outsourced?

1 - No, each board can ONLY be in one module.

For the next 2 questions what are you refering to as a project? I will assume you mean the printed circuit board.

2 - Yes, an engineer can be (and is) responisible for more than one board.

3 - No, the modules are the top level assemblies that include lower level assemblies such as printed circuit boards, cables, hardware....

For your last 4 questions, the answer is no.

I hope that clears it up a bit.

 

[cuttsy]

For the next 2 questions what are you refering to as a project? I will assume you mean the printed circuit board.

You tell me. In your spec you mention a 'Project Leader'
Does this equate to saying 'Person in charge of module'?

I'll assume that it does.

Board([U]BoardNumber[/U], BoardName)
Module([U]ModuleNumber[/U], ModuleName, [I]ModuleLeader[/I], Source, [I]Assembler[/I], Directory)
ModuleParts([U][I]Module[/I][/U], [U][I]Board[/I][/U])
Engineer([U]EngineerID[/U], Firstname, Surname, OtherDetails)
Company([U]CompanyID[/U], CompanyName, OtherDetails)

Board table - stores details of the boards
Module table - stores details of the module
ModuleParts - Stores which boards are in which module
Engineer table to link to module table in field ModuleLeader
Company table to link to module table in field Assembler


The only bit I don't know how to go about is the designer. The fact that a designer can be an engineer or a company.