Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2007
    Posts
    4

    Table Design - Foreign Keys/Primary Keys

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Posts
    4
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is nothing else in those tables? okay, ditch them all

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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2007
    Posts
    4
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is other stuff in those tables? okay, don't ditch them

    make sure each of them has both a PK and an FK
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2007
    Posts
    4
    So can the PK be the same as the FK? Because if not then there is nothing else UNIQUE That can be the PK.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes it can
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2007
    Posts
    3
    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,

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by wdoliver
    ... 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.
    you make it sound like those are separate columns -- they aren't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •