Choice of a primary key in SQL table

Question

I want to make an SQL table to keep track of notes that are added/edited/deleted. I want to be able to display the state of each NOTEID at this moment in a table, display log of changes of selected note and be able to delete all notes marked with a given NOTEID.

create table[dbo].[NOTES]{
  NOTEID [varchar](128) NOT NULL,
  CREATEDBY [varchar](128) NOT NULL, /*is this redundant?*/
  TIMECREATED  DATE NOT NULL,          /*is this redundant?*/
  MODIFIEDBY [varchar](128) NOT NULL,
  TIMEMODIFIED  DATE NOT NULL,
  NOTE [VARCHAR}(2000) NULL,
  PRIMARY KEY ( /* undecided */ ),
};

What is the natural way of making this table? Should I autogenerate the primary ID or should I use (NOTEID,TIMEMODIFIED) as the primary key? What kind of fool proof protection should be added?

I would like to be able to display all notes in a "Note history" window. So, I should store note from 3 days ago, when it was created, note from 2 days ago and from today, when it was modified.

However, the "Notes" table will show the final state for each NOTEID. That is

SELECT NOTE from NOTES where NOTEID = 'selected_note_id' and date = latest

I think your createdby and timecreated are redundant. The record with the earliest timemodified value would be the time created. — Rob K, May 10 '16 at 14:45

Juan Carlos Oropeza · Answer 1 · 2016-05-10T14:49:32.347

3

The best way is create two tables.

 NOTES (
     NOTE_ID        -- primary key and autogenerated / autonumeric
     CREATEDBY      -- only appear once
     TIMECREATED    -- only appear once
     NOTE 
 )

 NOTES_UPDATE (
     NOTES_UPDATE_ID   -- primary key and autogenerated / autonumeric
     NOTE_ID           -- Foreign Key to NOTES
     MODIFIEDBY 
     TIMEMODIFIED  
     NOTE 
 )

You can get your notes updates

 SELECT N.*, NU.*
 FROM NOTES N
 JOIN NOTES_UPDATE NU
   ON N.NOTE_ID = NU.NOTE_ID

and to get the last update just add

 ORDER BY NOTE_UPDATE_ID DESC
 LIMIT 1   -- THIS is postgres sintaxis.

edited May 10 '16 at 14:49

answered May 10 '16 at 14:35

Juan Carlos Oropeza

47,252
12
78
118

If you are duplicating the `NOTE` in both of your tables, you might as well just have one table which can have multiple notes. – Tim Biegeleisen May 10 '16 at 14:52
I like this. In fact, that's how I do it too. Your primary key is also your your foreign key in the history table. – Captain Kenpachi May 10 '16 at 15:01
@TimBiegeleisen You need multiple table because in the first one you get the `NOTE_ID` from autonumeric for new notes. In the second one you reference that one. Also you only save the original `NOTE` there. – Juan Carlos Oropeza May 10 '16 at 16:08

Tim Biegeleisen · Answer 2 · 2016-05-10T14:54:27.703

1

I think your current table design is fine, though you might want to make the NOTEID the primary key and auto increment it. I don't see the point of making (NOTEID, TIMEMODIFIED) a composite primary key because a given note ID should ideally only appear once in the table. If the modified time changes, the ID should remain the same.

Assuming we treat notes as files on a computer, then there should be only one table (file system) which stores them. If a given note gets modified, then the timestamp changes to reflect this.

edited May 10 '16 at 14:54

answered May 10 '16 at 14:37

Tim Biegeleisen

502,043
27
286
360

They probably want some kind of audit table, where you can track `NOTE` changes over time, that is why you may need a PK separated from `NOTE_ID` – Juan Carlos Oropeza May 10 '16 at 14:45

score 1 · Answer 3 · answered May 10 '16 at 14:40

SIMPLE ANSWER:

The PRIMARY KEY should be the value that unique identifies each row in your table. In your particular case, NOTEID should be your id.

ELABORATING:

It is important to remember that a PRIMARY KEY creates an index by default, which means that whenever you do a query similar to:

SELECT * FROM table WHERE NOTEID = something

The query will execute a lot faster than without an index (which is mostly relevant for bigger tables). The PRIMARY KEY is also forced to be unique, hence no two rows can have the same PRIMARY KEY

A general rule is that you should have an INDEX for any value that will often be used within the WHERE ... part of the statement. If NOTEID is not the only value you will be using in the WHERE .... part of the query, consider creating more indexes

HOWEVER! TREAD WITH CAUTION. Indexes help speed up searches with SELECT however they make UPDATE and INSERT work slower.

Choice of a primary key in SQL table

3 Answers3