Single column of strings or two columns ?

Question

I am saving a single string to a table, it must be unique i will check that it doesnt already exist before saving it. THere are no other fields.

can I just have a single column table with the string as the primary key or should I have a uniques identifier ID column as well? Why in either case?

Answer 1

Can the string change? What is the length of the string (important if other tables are going to use it as a foreign key)?

Generally if the value can change, or you need other tables to relate to it, I would recommend having a designated ID field.

Answer 2

If your string is unique (and will always be unique), then it's fine to have that as the only column and primary key.

The only reason I would use a separate ID is if either

1. it will become non-unique at some point in the future; or
2. if it's a large string and you want another table column to reference it (foreign key).

I would apply the YAGNI principle to both those situations and worry about it when it happens.

As an aside, with database applications, it's better not to "check that it doesn't already exist before saving it". I tend to just attempt to save it and catch the error if it alredy exists. Since it's a primary key (or unique constraint), that will work.

Checking for existence then inserting often leads to race conditions.

Answer 3

If the strings are always going to be unique, you should just use one column. To save on space (albeit might be little) you don't need another ID column. Add a unique ID constraint to the column to force unique values only.

Its up to you if you want to do a check to see if the value exists. Although having it always through a unique ID error IS NOT a proper way of checking if the value already exists in the table. Save the error handling for real errors and do the check manually. Exceptions can take a small performance hit and should be kept for real program exceptions, not the norm.

However, if for some reason, the string is going to be referenced in another column, I would create an ID field. If the string length is 32 characters, it will require 32 bytes of space (assuming ASCII) per record. A 32-bit int as a primary key will only take 4 bytes (32/8=4). So, if you're referencing the string in another table, you'll be saving space by using an integer ID.

Also, if you use an integer ID for the primary key, you might look into clustering the index by the string (if you will do a lot of lookups by the string rather than the ID). Grouping by the string instead of the primary key might do a lot for performance in this case.

Answer 4

Are you sure this string will be unique ? no one will be ever able to change it ? if so it is ok . otherways just use an id