I have a question to ask regarding the setting up of tables.
I am developing Postgresql for an engineering application. This application will have an initial user succeeded by multiple users in multiple engineering disciplines downstream.
Level One
What I would like to do is to have a minimum number of tables gathering information from the base engineering document (P&ID). Each physical element produced on the P&ID drawing will find its way to one row of one of three tables. The user will be responsible for establishing the category (pipe, vessel, etc.) that each element falls into. (Each table represents a particular function of the P&ID.)
Level Two
I could create a separate table for each category of elements (pipe, vessel etc.) with columns that represent the second level information the user requires. The challenge is that the P&ID is a creative document. There is always a distinct possibility of the user inserting an element that we haven’t considered. The above approach would force him to stop what he is doing and create a new table(s) for the new element(s).
Is there a method, in SQL, of setting up a table for the second level that can be used as a template for each category of elements found in the rows of tables in the first level?
(For instance – set up a table for”pipes” and have the category “vessels” initiate its own table.)
I’m new to SQL so, if this is possible, I may only need the terminology used in order to find this methodology in my Postgresql book or through the Postgre on-line help.
Thanks
Bob Pawley