Composite Keys and Zero padding in EF6 database first project

Question

I have three tables in a SQL Server 2016 db, Events, Races, Seasons. The Races table is a composite primary key of the PK for Events table and PK for Seasons table. I want to zero pad the primary key for the Events table primary key so that it is 3 digits. The desired Composite key for races would be in a format like 2018001 where 2018 is the seasonId and 001 is the eventId.

I am also using EF6 and database first approach for this. Is this a reasonably plan in first place and if so how do I set up the Events table to auto-increment with the proper zero padding?

No. This is not reasonable at all. Padding zeros and stuff like that belongs to the presentation layer, not to the database. — Zohar Peled, Feb 08 '18 at 15:03
Yes. A composite key would have two separate key columns, not some catenation. — David Browne - Microsoft, Feb 08 '18 at 15:12

score 0 · Answer 1 · answered Feb 08 '18 at 15:02

When non database first approach is used, it is why sql guys are needed ;).

Zero padding seems like an arbitrary requirement and while it looks cool, functionally it will cause problems. Look up zip code issues via google to learn more about similar challenges.

I would suggest tableid int identity(1,1) not null for a surrogate key. Padding is done after the fact in the usp_ that you return the results with.

HLGEM · Answer 2 · 2018-02-08T15:32:22.987

I would suggest three columns, eventid, seasonID and DisplayId. That way you can join correctly on the int fields for queries and use a calculated field to pad the numbers for the DisplayID. Note this will slightly slow inserts and updates but given the nature of the table it will probably not be noticable. It is better to do the padding once in the database on insert that to have to waste time do in the calculation every time you call up the data, so no this does not belong in the presentation layer.

The way you pad in SQl server is to use right and replicate together similar to this

right(replicate ('0',4)+ cast(event_id as Varchar(20)), 10)

Make sure to pad enough spaces to cover the maximum number of events you can conceive that database having in one year.

Then to combine with the seasonid:

Cast(seasonId as varchar(4)) + right(replicate ('0',4)+ cast(event_id as Varchar(20)), 10)

Do this as part of creating the calculated field (and persist the calculated field, so it doesn't have to keep recreating.

See this question for how to create a calculated field:

How can I set up a simple calculated field in SQL Server?

Composite Keys and Zero padding in EF6 database first project

2 Answers2