Geek Answers Handbook

SQL database stores different types of values ​​(in one or in one field)

In the database, I want to be able to assign variables to variables as a variable in a variable table. So do I need a separate table of values ​​for each type of value? If so, I'm not sure how you actually associate the values ​​with the right table, and therefore the right value. How can I achieve what I want?

Variables ID Name VariableValuesLink ID IDVars IDVals Values IDvals ValuesValueLink ID IDvals IDval ValuesInt IDval IntVal ValuesFloat IDval FloatVal ValuesDouble IDval DoubleVal etc... etc... etc... etc...

The goal is to get something like this:

 Variable: ezas123 Values: 1 (Int) 2.0 (Float) 3.0 (Double) Variable: QuickFox Values: The (TinyText) Quick (TinyText) Brown (TinyText) Fox (TinyText) Jumped (TinyText) Over (TinyText) The (TinyText) Lazy (TinyText) Dog (TinyText) Variable: Pangrams Values: The Quick Brown Fox Jumped Over The Lazy Dog (Text) How quickly daft jumping zebras vex (Text)

So when I query the DB, I could return this result set (where the values ​​are of a different type)

 Variable Value ezas123 1 ezas123 2.0 ezas123 3.0 QuickFox The QuickFox Quick QuickFox Brown QuickFox Fox QuickFox Jumped QuickFox Over QuickFox The QuickFox Lazy QuickFox Dog Pangrams The Quick Brown Fox Jumped Over The Lazy Dog Pangrams How quickly daft jumping zebras vex
+4
source share
2 answers

A few points:

  • In your example, the ezas123 variable has three values ​​with different data types, which means that the variable itself does not actually have a specific data type. This is likely to cause problems in the opposite direction and is likely to indicate that the data is rather poorly defined. I would see, including the restriction, that all values ​​for a given variable must have the same data type.

  • The Hogan SQL query states that whenever you specify values ​​in the query you requested (that is, between variables with different data types), you will have to return the result to varchar or display it in the same way (starting with you cannot have values ​​with different types data in one output column). With that in mind, do you really need different data types, or does the varchar type work well for all the data you deal with?

If different types are needed, I would look to put all IntVal, FloatVal, DoubleVal, ... tables into one table. Then your table definitions might look something like this:

 Variables ID NOT NULL ,Name NOT NULL ,DataType NOT NULL CHECK (DataType IN ('INT','FLOAT','DOUBLE','TEXT')) ,CONSTRAINT PK_Variables PRIMARY KEY (ID) ,CONSTRAINT UQ_Variables_1 UNIQUE (Name) ,CONSTRAINT UQ_Variables_2 UNIQUE (ID,DataType) Values IDvals NOT NULL ,ID NOT NULL ,DataType NOT NULL CHECK (DataType IN ('INT','FLOAT','DOUBLE','TEXT')) ,IntVal NULL ,FloatVal NULL ,DoubleVal NULL ,TextVal NULL ,CONSTRAINT PK_Values PRIMARY KEY (IDvals) ,CONSTRAINT FK_Values_Variable FOREIGN KEY (ID,DataType) REFERENCES Variables(ID,DataType) ,CONSTRAINT CH_Values CHECK ( NOT(DataType <> 'INT' AND IntVal IS NOT NULL) AND NOT(DataType <> 'FLOAT' AND FloatVal IS NOT NULL) AND NOT(DataType <> 'DOUBLE' AND DoubleVal IS NOT NULL) AND NOT(DataType <> 'TEXT' AND TextVal IS NOT NULL) )
  • You may need a UNIQUE constraint for variables (ID, DataType) (DBMS?) So that you can make it an FK object;
  • CHECK constraints ensure that only valid data types are used and that only columns of the correct value can be populated;
  • The presence of a DataType in the values, as well as Variables, means that the combination of FK and CHECK can be used to ensure that all values ​​for a given variable have the same data type, instead of using triggers or application logic.

Then the query for the tables looks something like this:

 SELECT v.name as Variable, COALESCE(cast(a.IntVal as varchar(max)), cast(a.FloatVal as varchar(max)), cast(a.DoubleVal as varchar(max)), cast(a.TextVal as varchar(max)), '') as Value FROM Variables V JOIN Values a on V.ID = a.ID AND v.DataType = a.DataType

It can also be written (perhaps more correctly) using CASE based on Variable.DataType used to select the appropriate column.

Having all the values ​​in one table means fewer tables / constraints / indexes in the database and means that expanding the solution to store new data types means only adding new columns to the value table (and changing constraints), and not adding new tables.

+3
source

Quick point, you can simplify your design this way. Just each table of values ​​points to a table of variables. No need for a link table. The only reason I can think of a link table is if you want the “simpler” way to have a sequence across all types of variables. If this is not required, use this project below:

 Variable ID Name ValuesInt IDvariable IntVal ValuesFloat IDvariable FloatVal ValuesDouble IDvariable DoubleVal etc... etc... etc...

How is your sql easy:

 select v.name as Variable, coalesce(cast(vi.IntVal as varchar(max)), cast(vf.FoatVal as varchar(max)), cast(vd.DoubleVal as varchar(max)), '') as Value From Variable V JOIN ValuesInt vi on V.ID = vi.IDvariable JOIN ValuesFloat vf on V.ID = vf.IDvariable JOIN ValuesDouble vd on V.ID = vd.IDvariable
+5
source

More articles:


All Articles