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. |
#2
|
|||
|
|||
Table Design for Operating Permit
Michael, since no one else has stepped forward with any advice, I'll give it
a stab. It seems to me from looking over the outline at the top of your post that you deal with "Processes," which involve one or more "Systems," which involve one or more "Devices," which are subject to one or more "Conditions." The first question I would ask is "Is a given System always associated with a single Process, or can a System be associated with multiple processes?" In other words, can System 1 - Emergency Power only be part of Process 1, or can it also be part of some other process as well? If a System can only be part of a single Process, then you are on the right track with the way you have your four tables set up (but hold on -- more about that later). If, on the other hand, a single System can be part of any number of Processes, you would need to set up your tables thus: 1. Process (ProcessID [PK], ProcessDescription) -- holds Process information 2. System (SystemID [PK], SystemDescription) -- holds System information 3. ProcessesAndSystems (ID [PK], ProcessID, SystemID) -- ties Processes and Systems together The same questions need to asked concerning Systems and Devices. Depending on the answer, you table structure will either be as you described it, or it will mirror the structure above. Your post seems to imply that a System goes with one Process only, and a Device goes with one system only. Let's assume that. This will be your table structu 1. Process (ProcessID [PK], ProcessDescription) 2. System (SystemID [PK], ProcessID, SystemDescription) 3. Device (DeviceID [PK], SystemID, DeviceDescription) 4. Conditions (ConditionID [PK], ConditionDescription) Now here is the missing piece. Since any Device can have any condition, you need this table: 5. DevicesAndConditions (ID [PK], DeviceID, ConditionID) -- ties Devices and Conditions together. Link tables (1) and (2) one-to-many, with (1) on the "one" side; tables (2) and (3) one-to-many, with (2) on the "one" side; (3) and (5) one-to-many, with (3) on the "one" side; and (4) and (5) one-to-many, with (4) on the "one" side. From here you can build your queries, forms, and reports. -- DDM "DDM's Microsoft Office Tips and Tricks" www.ddmcomputing.com "Michael DiCostanzo" wrote in message om... 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. |
#3
|
|||
|
|||
Table Design for Operating Permit
Based on your sample report you would want to setup up
your tables in the following manner, note: it is possible to have a primary key consisting of multiple attributes Process (ProcessID, ProcessDescription) primary key: ProcessID System (SystemID, SystemDescription) primary key: SystemID Device (DeviceID, DeviceDescription) primary key: DeviceID Conditions (ConditionID, ConditionDescription) primary key: ConditionID SystemProcesses (ProcessID, SystemID) primary key (ProcessID, SystemID) DeviceConditions (DeviceID, ConditionID) primary key (DeviceID, ConditionID) SystemDevices (SystemID, DeviceID) primary key (SystemID, DeviceID) The following query will give you all the information you need to know. It can be modified to give you specific fields and conditions can be added to limit the records returned. SELECT ProcessID, ProcessDescription, SystemID, SystemDescription, DeviceID, DeviceDescription, ConditionID, ConditionDescription FROM Process, System, Device, Conditions, SystemProcesses, DeviceConditions, SystemDevices WHERE Process.ProcessID=SystemProcesses.ProcessID AND System.SystemID=SystemProcesses.SystemID AND System.SystemID=SystemDevices.SystemID AND Device.DeviceID=SystemDevices.DeviceID AND Device.DeviceID=DeviceConditions.DeviceID AND Conditions.ConditionID=DeviceConditions.ConditionI D -----Original Message----- 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 | |
|
|