Table design: whether to have a primary key or not RRS feed

  • Question

  • User-501461518 posted

    I would normally always have a unique primary key in a table. However, I have an application with several tables but two tables are relevant to this question: Table1 and Table2.

    Table1 has a primary key. Table2 contains additional information for table1. Table2 will always be accessed with queries such as db.table2.where(x=>x.field == table1key)

    ie I will get all of the records from tabel2 that relate to a table1 record. I don't need to do db.table2.where(x=>x.key == 123) to access a single record.

    In general there will be zero or one table2 records for each table1 record.

    If I define a primary key for table2 then the database will index it and it will consume space but I am never going to use the key for accessing the table. Therefore should I just accept a non unique field that links to my table1 primary key in a one-to-many relationship and index table2 on that non unique field?

    Thursday, April 16, 2015 3:10 PM