What would be the Primary key?

I have a question about Database design. I have two tables, usertable and historytable, which are one-to-many relationship, which usertable contains

• username primary key
• passwordname
• email

and historytable has

• username foreign key
• date
• visted url

I am not sure if there are any negative effects in terms of performance if historytable has no primary key defined , if there are , what would be the best option i开发者_开发问答n the historytable to be the primary key?

Thanks.

---

You would need to define a new column: UserHistoryId Make it identity column

The reason for this is no combination of the other columns could be unique in all cases.

e.g. If a history record is created twice in one day for a user from the same url.

Performance:

Depends on how the history table is used - If you only ever SELECT data from it by means of a query on userId, or date, or Url, then the ID column would serve no purpose.

However, if you ever perform any Update/Delete operations on the table then, the Id would be useful.

Regardless of current requirements, it costs you almost nothing to include the extra column now, and it's something I would always recommend.

---

If you can't see an obvious primay key then just add an auto-incremending integer /identity column as your primary key - this goes for almost any table.

There may be a couple of situations where there is a better choice, but most of the time an auto-increment integer field is a good choice of primary key regardless.

In fact I would probably recommend an auto-incrementing integer / identity column as the primary key for your users table too - there are a couple of situations (e.g. deleted users) where you might want to have two rows with the same username.

---

you could check this one. Though this question is more generic than yours, it should give you a wider view on what should be primary keys.

---

My guess is the likely key of the history table would be (username, date, url). This is only a guess based on the names of the attributes however. You should determine the keys based on business analysis and your requirements.