CODEWITHSUNDEEP
sqldatabaset-sql
Ian R. O'Brien
Ian R. O'Brien

Reputation: 6920

How should I choose column data types in a database?

I have a staging table which has all different types of data being stored as strings. Specifically, nvarchar(300). These columns represent all sorts of things: dates; prices; quantities; etc.

I will be moving this data into another table and I am wondering how I should define the data types of the destination columns.

  1. Should I choose data types to most closely match the data I am inserting or,
  2. Should I leave the data as nvarchar(300) and make it the consumer's responsibility to convert or cast the data appropriately?

Edit: This is for a relational database.

Upvotes: 0

Views: 144

Answers (3)

Neville Kuyt
Neville Kuyt

Reputation: 29649

If you have the domain understanding to do the conversion, it is surely better to do it when moving out of the staging table - the consumers of your data would almost certainly want to re-cast the data into proper columns anyway, so the value of your work basically becomes a file copy.

If more than one consumer uses the table, the likelihood of bugs (due to different interpretations of type conversion) is high if you leave everything as a string - esp. if you have to deal with numbers (where precision is usually an issue) or datetime values (again, the difference between "1 Jan 2013" and "1 Jan 2013 12:00"). Do it once, make sure everyone uses the same data types.

The only two reasons not to do the conversion are not having the domain knowledge, or not having the time/money/energy to do it. In both cases, you're just pushing the problem downstream.

Upvotes: 1

Paul Alan Taylor
Paul Alan Taylor

Reputation: 10680

Use proper types, otherwise a good chunk of your code will end up being conversion routines that you'll use to access functionality you get 'for free' by using the correct type.

As a real simple example, consider all the functionality that you get with the DATETIME type, or the problems that different global representations can bring.

Take the hit, write an robust import routine and use proper types.

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460258

Always use the most appropriate datatype and convert it when it should be displayed/printed. Otherwise you always need to parse it back and forth.

You might also run into localization issues (some day) if you store nvarchar for everything(f.e. instead of datetime).

Upvotes: 1

Related Questions