2

A column/field will only have 2 possible values, either "Transplanted" or "Direct-seeding". Is it better to create a table for these options and give them unique IDs or are they better off put as raw string values in the table?

4

I would start with a check constraint that limits the column to those two values.

If the table containing those values is expected to get really big (tens or hundreds of millions of rows), then using a lookup table and only storing a foreign key reference as a number might save some space and might increase performance if you don't need that value all the time.

Plus it will make adding new values easier because you don't need to re-create the check constraint.

If the end-user is expected to maintain the list of allowed values, then a check constraint is not an option at all.

Another thing to consider is localization (translating those labels). If you have to translate those display values into other languages, then using a lookup table is the better way to go.

4

Create a lookup table for these two values, with a foreign key in each table that needs it.

A quick example:

create table SeedOrigin
(
  SeedOriginID int primary key,
  SeedOrigin   varchar(50)
)
insert into SeedOrigin values (1,'Transplanted')
insert into SeedOrigin values (2,'Direct-seeding')


create table Crop
(
  CropID       int primary key,
  CropName     varchar(100),
  SeedOriginID int
)

ALTER TABLE [dbo].[Crop] WITH CHECK ADD CONSTRAINT
[FK_Crop_SeedOrigin] FOREIGN KEY ([SeedOriginID])
REFERENCES [dbo].[SeedOrigin] ([SeedOriginID])

If you need to change the spelling of the SeedOrigin you only need to change it in the SeedOrigin table. It is a data change, not a structural change. You would not have to modify a constraint or recompile an app.

This method is scalable in case you need to add more SeedOrigin types in the future, or possibly support other languages.

  • This is typically the approach I take when there might be more than two choices. The string descriptions I only use for making sense of the data (labeling a magic number). The presentation layer is a better place IMO for translations and/or final authority on what is displayed. That being said my key-value pairs are essentially static and not user generated. If the content is user generated then the database should definitely be the authority. – Erik Dec 4 '15 at 16:29
  • If you are certain there will never be more than two values, then I suppose let the presentation layer handle it. However, is there more than one app accessing the data? Would that be considered the same presentation layer? – datagod Dec 4 '15 at 19:31
  • In general I try to always defer presentation concerns to the presentation layer. There are always exceptions of course. With multiple clients, I still think it is best to let them decide what to do. One example is a mobile app that I worked on which stored gender/sex as a nullable boolean. Our iOS app used the male/female signs, but our admin web backend used strings. Furthermore I would argue that you'll get a (small) performance bump if you send integers/booleans instead of strings to the presentation layer when reasonable, especially if the client can handle the i18n for you. – Erik Dec 4 '15 at 19:54
1

I'd suggest using a bit field to store the values. Then either allow the application that is using the database to do the work of presenting the end user with the string or just have a case statement in your query to present the string value you mentioned for the true/false condition. If you expect there may come a time that there would be more than two possible values, I'd suggest having a lookup table with a unique id and the strings and referencing them that way.

  • The problem with this approach is that every application that reads that table would need to know the proper strings. If the string changed, the applications would have to change as well. – datagod Dec 4 '15 at 16:04
  • @datagod How should the localized/translated strings be associated? If the options are truly binary, I don't think its a big deal to make the presentation layer handle displaying the appropriate string. – Erik Dec 4 '15 at 16:20
  • The assumption is there will only be two values, which is fine if that is the case. But when marketing decides they want a change on the fly by adding a third option or adding "2000!" to the end of everything, then you may find it to be more problematic than making a change in the database. I suppose applications can simply store the data in config files. – datagod Dec 4 '15 at 19:34
  • @datagod very true. You always need to give yourself a way to gracefully expand ̶i̶f̶ when things change. – Erik Dec 4 '15 at 20:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.