Database for Wind Turbine Equipment

Dear all,



I am designing a database to handle equipment that will be used when building wind turbines.



Basically, the wind turbine details will be kept in one table (tblTurbine) which defines the unique "turbine number" (WT1,WT2,WT3) as well as different information pertaining to that wind turbine such as type, size etc.



There will also be several component tables - a new table for each component - such as:



"tblBlades" with fields like "serial number";"part number";"pitch";"length"



Or:



"tblGenerator" with fields like "serial number";"part number"; "Power Rating"; "Weight"



plus other similar tables.



My question is this; I want to find a way to show that a specific component is allocated for use with a certain turbine. This is complicated by the fact that each component will have a "primary turbine" and a "secondary turbine" at the same time.



My initial thought was to have 2 separate columns in each component table as below:



tblBlades:



serial number; part number; pitch; length; primary turbine; secondary turbine



The relationship would be created by showing a copy table in the relationships view ie "tblTurbine" and "tbleTurbine_1" and taking the primary key of "turbine number" and dragging it to the foreign keys of "primary turbine" and "secondary turbine".



I feel that this might be a rather clumsy way to set up a relationship and am not sure how it will work when it comes to querying the tables.



Can anybody offer any advice on this? Any help would be greatly appreciated.



Thank you,



Sean

Database for Wind Turbine Equipment