If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table Design for Operating Permit
I have a project where I need to maintain an operating permit for a
client. The permit has the following format: Process 1 - Engines System 1 - Emergency Power Device D1 Conditions 1-1, 2-1 Device D2 Conditions 1-1 System 2 - Cogeneration Device D3 Conditions 3-1, 3-2, 3-3 Device D4 Conditions 3-1, 3-2 The process and system numbers are unique. The device numbers are also unique - i.e. there can only be one device D1. The conditions are also unique. There can only be one condition with the ID 3-1. As you can see, however, a device can have multiple conditions. A condition can also be used for multiple devices. Unfortunately, the agency that oversees these operating permits does not have a way to issue the permits in pieces. If I want to see all equipment in system 2, or if I want to see all equipment that is subject to condition 3-2, there's no easy way to view it. The fields are all pretty much text based. I'm looking for some input in the table design. It seems to me that I could have the following tables and fields: 1. Process (ProcessID, ProcessDescription) 2. System (SystemID, SystemDescription) 3. Device (DeviceID, DeviceDescription) 4. Conditions (ConditionID, ConditionDescription) All the ID fields would be the primary keys. The Description fields would be text. I would need a fifth table and that's the table whose design I'm asking for help on. Would I simply create some sort of composite table in the following format: 5. Composite (CompositeID, ProcessID, SystemID, DeviceID, ConditionID) The composite ID would be the primary key and all the other fields would be foreign keys. It seems to me that I could then create reports and queries to display the permit in the various formats as needed. Thanks in advance for you help. |
Thread Tools | |
Display Modes | |
|
|