Home Forums Database Design Design Ideas and Questions Table design and PK design RE: Table design and PK design

  • November 15, 2013 at 6:24 am

    #1666712

    mstanl (11/15/2013)

    I have a team of Dev. that created a database with tables that each table has only one PK called ID as integer, in the child tables they join to tables with that PK from the parent to the child based on that key.

    <snip>

    They did put unique second keys on the logical primary key of the table, so that the database will have only one StateCode for example.

    My question is has anyone worked with a database like this, where ever table has only one PK column called ID?

    I have asked that they change the name of ID to the table name id, example State table the ID would be called StateID to match the table where it will be used in.

    I know SQL would like to join the table on a single integer ascending one from each table but what other problem would I see?

    Any other pit falls for support?

    Thanks

    For the naming convention, where everything is "ID", well it's against best practice, for sure.

    PK should always be tablename + ID, is what i've been told, and seen in my career.

    the key in the foreign table must always contain the full name of the key from the referenced table.

    so if you needed, two stateID in an address table for example it would be columns HomeStateId and BusinessStateID, which join to State.StateID

    .

    that is very beneficial when you are creating joins, obviously.

    can you make the developers follow that best practice?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!