4

I have a table called 'Date Restriction' and it basically holds the following properties:

DayId    : int
DateFrom : datetime
DateTo   : datetime
EventId  : int       // this is a foreign key

Now the way I would access this is to get the event I want and then look at the associated Date Restrictions.

Is it good practise or recommended to add a primary key column to this table if I never have to reference the date restriction alone?

Ed Schwehm
  • 2,163
  • 4
  • 32
  • 55
Base33
  • 3,167
  • 2
  • 27
  • 31
  • Why is not one of (EventId,DateFrom) or (EventId,DateFrom,DateTo) not a natural PK for this table. Do you really want to allow duplicates? – Pieter Geerkens Mar 13 '13 at 20:30
  • 3
    I believe you need a PK anyway, otherwise you'll end up having duplicates. The question is do you want natural or synthetic one. You're planning to manage that table updating, deleting records from it, right? If so, then synthetic PK is a good and consistent way to target specific rows for that, especially if in your app (client code) ids used for that matter all over the place for other entities (tables). – peterm Mar 13 '13 at 20:34

5 Answers5

5

You should always have a primary key. Having a primary key will allow SQL Server to physically store the data in a more efficient manner. A primary key also allows Entity Framework to easily uniquely identify a row.

Look for a natural key across your columns. If a single EventId will only ever have one row in this table, create a primary key on EventId.

If there is not a natural key, add a surrogate key column to your table and make it an identity.

supergrady
  • 1,322
  • 10
  • 13
4

As a database design practice, it is always recommended to have primary keys. Even if your application does not directly reference the DateRestriction Table, having a unique identification for a row will give you benefits not only on the SQL side but it will also allow entity framework to easily map the table (without going through any extra hoops).

matei.navidad
  • 676
  • 6
  • 18
1

You need to tell EF how to uniquely identify a row in your Database. If each event only appears once in the table you can make the EventId both the primary key and a foreign key. You could also make all three columns a composite primary key. For example: 

class DateRestriction {
  [Key, Column(Order=0)]
  public DateTime DateFrom {get;set;}

  [Key, Column(Order=1)]
  public DateTime DateTo {get;set;}

  [Key, Column(Order=2)]
  public int EventId {get;set;}
}
Michael Dunlap
  • 4,300
  • 25
  • 36
1

I recommend having a surrogate key and creating a unique index using the natural key.

Keith John Hutchison
  • 4,955
  • 11
  • 46
  • 64
0

If this table in your database directly referenced by another table, then no. Without the rest of your structure I'm not entirely sure. So I'll provide a general rule of thumb with an example:

Customer Table:

  • Id
  • Name
  • Last

But you wouldn't Query an individual Address, not without linking it to a person. So you have this second table called Address.

  • CustomerId
  • Street
  • City
  • Zip
  • State

So this one Customer will always have a tied address, so defining a Foreign Key is acceptable. Now, if a Customer was allowed have more then one Address then yes, you would need a Primary Key so the structure stands on its own.

If that particular data is always tied to another table, you can just define a Foreign Key. Obviously your implementation and access to your database may have an impact. So you'll have to be wary. If your utilizing different technologies such as:

  • Entity Framework
  • Ado
  • etc

So being aware of implementation and design of both your database and application.

However, without a Primary Key you'll have to define Indexes so that you can optimize your database. As the Primary Key is always Indexed.

The surrogate key with an index will suffice.

Sorry if I misunderstood your question, but hopefully this points you in the right location.

Greg
  • 11,302
  • 2
  • 48
  • 79