I am designing a DB at work in PostgreSQL. It has a main facility table with an autogenerated primary key "id". I have a bunch of tables that spawn off this with more specific info about each facility. These tables all have a foreign key constraint that references facility. Is this the correct way to normalize the DB? Should each table have a PK AND a FK? Thanks
Ok well there is a main facility table with the name address zip etc. Then there are more specific tables like network, server, APplication, etc. Each table that is not the facility table has a foreign key referencing an autogenerated number for each facility. But there is nothing else in each table that is unique. Is it OK that all I have is a FK in each table that is not the facility table?
Normally you completely normalize a database and then make adjustments for performance. Here are some simple guidelines for normalizing the database from the information provided.
Main Table(s): Facilities - Unique Key - only required columns (Must contain a value ALL of the time) should be in table.
There can be several Main Tables (I am using this term loosely) each based upon a specific area, entity, etc.. regardless the above rule applies.
If there is a many-to-many relationship then create a cross-reference table. (Contains primary keys of related records from both tables)
If there are optional columns associated with a table make a table for each. The only time a record will be added to the table is when there is optional information available. The table will contain a primary key (to ensure each record is unique) and the FK of the main record for which it is associated.