Best SQL Data Type for x.x.x Format

datatypessql serversql-server-2008

What is the best data type for field with format like this X.X.X.x

Here is the sample field data

15.8.0
15.8.1
15.8.2
15.8.3
15.8.4
1.8.0
2.8.2
9.8.2
9.8.3
9.8.4
10.8.0
13.15.0.1
13.15.1.1
13.15.2.1
13.15.3.1
13.15.4.1
13.0.0
26.0.0.1
26.0.0.2

This field use as primary field of table

Thanks

Best Answer

I can't say it would be the best ("best" in what sense?) but one way to do this is to have the 4 numbers in 4 separate TINYINT columns.

You can have the PRIMARY KEY as a composite 4-column one or as a persisted column. Considering from the data provided that the 4th number can be NULL, I'd choose the persisted as primary key and add a unique constraints on the 4 columns:

create table versions
( a tinyint not null,
  b tinyint not null,
  c tinyint not null,
  d tinyint null,
  version as cast( concat( a, '.', b, '.', c, 
                           case when d is null then '' else concat('.',  d) end
                   ) as varchar(15)) 
      persisted,
  constraint version_uq
      unique clustered (a,b,c,d),
  constraint versions_pk
      primary key (version),
) ;

Tested at rextester.com.

Another option would be to use the HierarchyID type.

Related Solutions

Database Design – Choosing Between INT or CHAR for a Type Field

You'd typically use tinyint which is 1 byte too

char(1) will be slightly slower because comparing uses collation
confusion: what is S: SUV or Saloon or Sedan or Sports?
using a letter limits you as you add more types. See last point.
every system I've seen has more then one client eg reporting. The logic of changing V, S into "Van", "SUV" etc will need repeated. Using a lookup table means it is a simple JOIN
extendibility: add one more type ("F" for "Flying car") you can one row to a lookup table or change a lot of code and constraints. And your client code too because this has to know what V, S, F etc are
maintenance: logic is in 3 places: database constraint, database code and client code. With a lookup and foreign key,it can be in one place

On the plus side of using a single letter... er, don't see any

Note: there is a related MySQL question about Enums. The recommendation is to use a lookup table there too.

Sql-server – SQL Data type for “Reference” fields in table – SQL Server

Here is what I would do based on your requirements:

CREATE DATABASE [demo];
GO

USE [demo];



CREATE TABLE [dbo].[Player](
    [playerID] [int] IDENTITY(1,1) NOT NULL,
    [firstName] [varchar](50) NULL,
    [secondName] [varchar](50) NULL,
    [fk_team] [int] NULL,
 CONSTRAINT [PK_Player] PRIMARY KEY CLUSTERED ([playerID] ASC)
 );
GO


CREATE TABLE [dbo].[EventTypes](
    [EventTypeID] [int] IDENTITY(1,1) NOT NULL,
    [EventType] [varchar](50) NULL,
 CONSTRAINT [PK_EventTypes] PRIMARY KEY CLUSTERED ([EventTypeID] ASC)
 );

GO


CREATE TABLE [dbo].[Events](
    [eventsID] [int] IDENTITY(1,1) NOT NULL,
    [Minute] [varchar](50) NULL,
    [fk_EventType] [int] NULL,
    [fk_Player] [int] NULL,
 CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED ( [eventsID] ASC)
 );
GO
SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Events]  WITH CHECK ADD  
CONSTRAINT [FK_Events_EventTypes] FOREIGN KEY([fk_EventType])
REFERENCES [dbo].[EventTypes] ([EventTypeID])
GO

ALTER TABLE [dbo].[Events] CHECK CONSTRAINT [FK_Events_EventTypes]
GO

ALTER TABLE [dbo].[Events]  WITH CHECK ADD  
CONSTRAINT [FK_Events_Player] FOREIGN KEY([fk_Player])
REFERENCES [dbo].[Player] ([playerID])
GO

ALTER TABLE [dbo].[Events] CHECK CONSTRAINT [FK_Events_Player]
GO



INSERT INTO [dbo].[EventTypes] ([EventType]) 
      VALUES('Try'), ('Conversion'), ('Penalty');
GO
select * from EventTypes;

This should be what you need. Have a great day!

Best Answer

Related Solutions

Database Design – Choosing Between INT or CHAR for a Type Field

Sql-server – SQL Data type for “Reference” fields in table – SQL Server

Related Question