Reputation: 9
I'm designing a table. One field apparently can have either numeric input or text input (e.g., 700 or 789.0123, or Trump or Clinton), depending on the subject matter of the application.
Would it be better to store the numeric inputs as varchar text (e.g., "700.0000" or "789.0123" formatted to 4 decimal places (or some other common number of decimal places) in the same field as the obvious text input (Trump or Clinton), or would it be better to have two fields and have the several applications (and applications programmers) direct their data to the appropriate field?
Upvotes: 0
Views: 104
Reputation: 27464
What is the meaning or definition of the field, and what will you do with it?
A field that can be either "Trump" or "789" raises alarm bells for me as sounding very much like you are using a single field to hold totally different data.
If the field is something like, "identifier of the uploaded document", and sometimes the user gives a text description, and sometimes he gives the Dewey Decimal code for a book, then it's fine.
But if the definition is something like, "when the record type is 1, this is a State record and this field is the name of the candidate who won the primary, but when the record type is 4 than this is a Precinct record and this field is the number of registered voters in that precinct", I can only say ... don't. Make two separate fields.
What advantage is there to combining two complete different fields into one? Save having to add a field to your table? It's going to cause all sorts of difficulty. If you ever need to sort by the numeric value or do a range check, it's not going to work, because of course the number 780 > 82 but the string '780' < '82', etc. You can't do arithmetic on a varchar
field: '780' + 23 is either an error or '78023', depending on the flavor of SQL. And for human beings trying to make sense of your code ... what do you even call this field?
I don't know your application. It's possible that it makes sense in context. Perhaps if you told us what the field represents it would make sense. It certainly looks suspicious.
Upvotes: 1
Reputation: 18940
In a well formed database, every field's contents are an instance of an attribute, and an attribute will have a specific domain, the set of values that are possible. It's very unusual to have a domain that spans multiple datatypes. It's more likely that you are trying to store multiple attributes in a single column.
This is nearly always bad design. You are better off to start with a conceptual model of the data, one which will clarify both the form and the semantics of every attribute. Then design columns that match, and tables that contain appropriate columns.
Normalize your tables unless you have a real good reason not to. (this does happen in real life, but if you are a newbie, don't start out that way).
Upvotes: 0
Reputation: 4647
The answer depends on your desired level of Normalization. If you desire 1NF then you need to design attributes that are atomic. In other words, if you never plan to use any subdivision of the attribute, then by all means combine them together. If you don't need to validate, or sort or query by any subset of the attribute then you can combine the two seemingly disparate data items.
Upvotes: 0