Need help with data base design

[jefbowden]

I'm creating a maintenance database where each time maintenance is done on a machine it will be recorded, providing information listed under Maintenance Event table. I eventually want to be able to use a form to search by WO#, Date Range, Machine and/or Section and/or Component. 1st I want to make sure i'm setting up my tables correctly. This is what i'm thinking so far.

tblCompany
-name
-phone #
-Address
-WO#

tblMachines
-Machine ID (pk)
-Machine

tblSections
-MachineID (fk)
-SectionID (pk)
-Section

tblComponent
-SectionID (fk)
-Component ID
-Component

tblMaintenance Event
-Begin Date
-End Date
-Machine
-Section
-Component
-Part
-Description
-Labor Cost
-Material Cost
-Scheduled Downtime
-Unscheduled Downtime
-WO#

 

[WIS]

I'm creating a maintenance database where each time maintenance is done on a machine it will be recorded, providing information listed under Maintenance Event table. I eventually want to be able to use a form to search by WO#, Date Range, Machine and/or Section and/or Component. 1st I want to make sure i'm setting up my tables correctly. This is what i'm thinking so far.

tblCompany
-name
-phone #
-Address
-WO#

tblMachines
-Machine ID (pk)
-Machine

tblSections
-MachineID (fk)
-SectionID (pk)
-Section

tblComponent
-SectionID (fk)
-Component ID
-Component

tblMaintenance Event
-Begin Date
-End Date
-Machine
-Section
-Component
-Part
-Description
-Labor Cost
-Material Cost
-Scheduled Downtime
-Unscheduled Downtime
-WO#

A few thoughts on the above:

Will there be more than 1 record in tblCompany. If so I would definitely put in CompanyID pk for tblCompany.

tblMaintenanceEvent:
Get rid of the space between Maintenance and Event. Does this relate to any of the other 4 tbls - it appears that it does. If so I would put in "MEID", pk and any other ID's (eg MachineID instead of Machine) where there will be a join as fk's. One problem could be that if a machine changes, you would have to enter it as a new machine in tblMachines, rather than change the name in an existing entry. However, if you left the fld as Machine you could change the existing entry in tblMachines as records in tblM..E.. would show Machine1 up to the pt of change then Machine2 whereas with MachineID all records would show Machine2.

I would remove all spaces in fld names and get rid of the # as well. These can cause problems in VBA and SQL.