Should I use the primary key identifier if the table does not mean anything?

Question

I have a table called Date Limit and it basically contains the following properties:

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

Now, when I get access to this, I need to get the event that I want, and then look at the related date restrictions.

Is it good practice or recommended to add a primary key column to this table if I don't need to refer only to a date limit?

Answer 1

You should always have a primary key. Having a primary key enables SQL Server to physically store data in a more efficient way. The primary key also allows the Entity Framework to easily uniquely identify a string.

Look for the natural key in the columns. If for one EventId there will be only one row in this table, create a primary key in EventId.

If there is no natural key, add the surrogate key column to your table and make it private.

Answer 2

As a database design practice, it is always recommended that you have primary keys. Even if your application does not directly refer to the DateRestriction table, having a unique identifier for the row will give you advantages not only from the SQL side, but also allow the entity infrastructure to easily map the table (without going through any additional hoops).

Answer 3

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

 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;} } 

Answer 4

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

Answer 5

If this table in your database directly refers to another table, then no. Without the rest of your structure, I'm not quite sure. Therefore, I will give a general rule with an example:

Customer table:

• Id
• Name
• Last

But you will not ask for an individual address without associating it with a person. So, you have this second table called the address.

• CustomerId
• Street
• Town
• Zip
• State

So, this client will always have an associated address, so the definition of Foreign Key acceptable. Now, if the client is allowed to have more than one address, then yes, you will need a Primary Key , so the structure will stand by itself.

If this data is always bound to another table, you can simply define the Foreign Key . Obviously, your implementation and access to your database can have an impact. Therefore, you need to be careful. If you use various technologies, such as:

• Entity Framework
• Ado
• etc.

Knowledge of the implementation and design of your database and application.

However, without a Primary Key you will need to define Indexes so that you can optimize your database. Because Primary Key always Indexed .

A surrogate key with an index will suffice.

Sorry if I misunderstood your question, but hopefully this indicates that you are in the right place.