Have Geek Brain

What is the best way to define a column in SQL Server that can contain either an integer or a string?

I have a situation where two objects of the same type have different types of parents. The following pseudocode explains the situation better:

TypeA a1, a2;
TypeB b;
TypeC c;
a1.Parent = b;
a2.Parent = c;

To further complicate matters, TypeB and TypeC can have different primary key types, for example, the following statement can be fulfilled:

Assert(b.Id is string && c.Id is int);

My question is, is this the best way to define parent-child relationships in SQL Server? The only solution I can think of is to define that the TypeA table has two columns - ParentId and ParentType, where:

  • ParentId is sql_variant - for storing both numbers and strings
  • ParentType is a string - to store the qualified assembly name of the parent type.

However, when I determined the user datatype based on sql_variant, it set the field size to be fixed at 8016 bytes, which seems to be much larger.

There must be a better way. Anyone? Thanks.

+1


a source to share


5 answers


If the column will NEVER EVER be involved in any math operations, do them CHAR () or VARCHAR (), since you will be dealing with a sequence of characters, not numbers. '1' is valid in this case as "A".



+1


a source


One word: NOT

It is very bad practice - columns have ONE ONE DATATIC for some reason. Don't overuse it and turn everything into options ........



Mark

+12


a source


I'm not sure I fully understood your case, but in similar circumstances, I created TWO columns in TableA, one to hold the string key and one to hold the int key; they can end up as NULLable (but not in the same record).

+1


a source


By using a single column, you eliminate the possibility of setting up a foreign key relationship, thereby introducing the potential for bad data. You need each table key stored in a differnt field as they are different data that means different things. It would be very bad if they were stored in one column.

0


a source


Well, there are two problems. The first is OO design, your TypeA model can have different types of parents, and these types (TypeB and TypeC) do not have a common parent. It is clear that I do not believe that this can be so in reality. But I don't know the meaning of these types ... This problem can be solved if you inherit TypeB and TypeC from some TypeX, in which case I will refer to TypeX in TypeA.

The second is the database design. Due to a mistake in OO design, you have problems from the DB side. The solution is the same: create a separate table for TypeX and put all the common attributes between TypeA and TypeB there, create separate tables for TypeA and TypeB. TypeX will refer to TypeA as both 1: 1 and TypeB. In this case, the creation of TypeA looks like this - insert a new line into TypeX, get an ID, insert a line into TypeA. In this solution, you will have matching strings in TypeX and TypeA or TypeX and TypeB.

TypeX (TypexID int has no primary key ID (1,1), SomeCommonColumn int) TypeA (TypexID int is not null primary key, TypeASpecific int) TypeB (TypexID int is not null primary key, TypeBSpecific varchar)

This is the only way to implement such a situation in relationship theory - clear and irregular. It doesn't look very simple, but usually these tables are covered by the view and stored procedures, so these tables can be used by the application as a single (virtual) table.

Thank you, Alexander

0


a source






More articles:


All Articles