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

is this correctly normalized? that depends on why you feel the "bunch of tables" had to be spawned off

should each spawned table (i.e. not including the main facility table) have both a primary key and a foreign key? yes!!

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?

there is nothing else in those tables? okay, ditch them all

add a "type" column to your main table and you're done

no there is other stuff in the tables, just nothing else unique. The reason I separated the tables is because I grouped like information together.

there is other stuff in those tables? okay, don't ditch them

make sure each of them has both a PK and an FK

So can the PK be the same as the FK? Because if not then there is nothing else UNIQUE That can be the PK.

yes it can

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.

Hope this helps,

you make it sound like those are separate columns -- they aren't