-1

I have to create a new table "holidays" into a a SQL Server 2005 DB.

first option:

ID Int **PK**
CustomerId nvarchar (10)
HolidayDate Smalldatetime
Description nvarchar (50)

second option:

CustomerId nvarchar (10) **PK**
HolidayDate Smalldatetime **PK**
Description nvarchar (50)

I think that tipically I will need only to do some queries with join on HolidayDate and CustomerId.

I would be more focused on the latter, because I would have a field less, and it appear 'logically' better to me, even if I have to worry about not insert duplicate records.

What do you think?

Pileggi

Serg
  • 2,346
  • 3
  • 29
  • 38
lamarmora
  • 1,116
  • 4
  • 16
  • 32
  • 1
    Using the first option will allow you to use that ID as a foreign key in another table. – swandog Dec 27 '13 at 15:29
  • yes but tipically i will need only to do some queries with join on HoidayDate and CustomeId... – lamarmora Dec 27 '13 at 15:40
  • 2
    There's nothing stopping you from making a unique key on `{CustomerID, HolidyDate}` even if you use the surrogate key. But here are some questions I would want to consider. How does the CustomerID and holidayDate get populated? What happens if there's a mistake? Is it possible for CustomerID and HolidayDate to be Updated? – Conrad Frix Dec 27 '13 at 15:51
  • What if you have, in the future, several religions with overlapping dates for their holidays? – Eran Boudjnah Dec 27 '13 at 15:52
  • Great point @Eran! What if you have a multi-religious household so the user is the same. – logixologist Dec 27 '13 at 15:55
  • ok I can implement, in my inserting and updating logic some filters to prevent error about duplicate records... – lamarmora Dec 27 '13 at 16:03
  • 1
    There's an old religious war about whether or not to use surrogate or natural primary keys (i'm in the surrogate camp), and although there's no enforcement to it, I would think it would be better to at least be consistent with the choices you make. In other words, do other tables use surrogate keys? If so, I'd do the same here. – Stuart Ainsworth Dec 27 '13 at 20:28

2 Answers2

1

Traditional database design recommends (strongly) that your PK should be a value that has meaning within the DB (an Int, auto-number or something equiv) and not something meaningful to users (such as dates) because meaningful values tend to result in PK collisions and awkward validation logic. Things are much simpler if you stick with the recommended/classic approach.

Sometimes, a complex key may seem like a better approach, but you don't usually gain much. However, you usually do incur complexity.

So, stick with option 1 and maybe apply an index to represent option 2 instead of a PK.

tgolisch
  • 6,549
  • 3
  • 24
  • 42
  • Thank you to all, and sorry to the one than has downvoted my question, maybe my question is boring and not useful, for someone, fortunatelly not all, in this forum has the same opinion... – lamarmora Dec 27 '13 at 21:43
1

You need to use the int key. The reason is simple. Holidays are not confined to a single date. There are multiple days in Hannukah. Ramadan's a month long. So even in your first answer you need to update the schema to have a start date and end date

Brian White
  • 1,265
  • 1
  • 10
  • 16