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

Question

I have a situation where two objects of the same type have parents of different types. The following pseudo code explains the situation the best:

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

To complicate things even further TypeB and TypeC may have primary keys of different types, for instance, the following assertion may be true:

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

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

• ParentId is sql_variant - to be able to hold both numbers and strings
• ParentType is string - to keep the assembly qualified name of the parent type.

However, when I defined a user data type based on sql_variant, it specified the field size as fixed 8016 bytes, which seems to be way to much.

There have to be a better way. Anyone? Thanks.

Answer 1

One word: DON'T

This is very bad practice - columns have ONE SINGLE DATATYPE for a reason. Do not abuse this and make everything into variants.......

Marc

Answer 2

If NEITHER column will EVER be involved in any mathematical operations, make them CHAR() or VARCHAR(), as you will be dealing with a sequence of characters, not numbers. '1' is just as valid in that case as 'A'.

Answer 3

I'm not sure I fully understood your case, but in similiar circumstances I created TWO columns on TableA, one to store the string key and one to store the int key; eventually they could be both NULLable (but not in the same record).

Answer 4

By using one column you eliminate the ability to set up a Foreign key relationship thus introducting the potential for bad data. You need each table's key stored in a differnt field as they are differnt data that mean differnt things. It would be a very bad idea to store them in one column.

Answer 5

Well, there is two problems. First is OO design, in your model TypeA can have parents of different types and these types (TypeB and TypeC) have no common parent. Clearly speaking I don't believe it can be so in real word. But I don't know meaning of these types... This problem can be solved if you inherit TypeB and TypeC from some TypeX, in this case I'll refer to TypeX in TypeA.

Second one is DB design. Due to error in OO design you have problems on DB side. Solution is the same - create separate table for TypeX and put there all common attributes between TypeA and TypeB, create separate tables for TypeA and TypeB. TypeX will relate to TypeA as 1:1 as well as TypeB. In this case TypeA creation looks in this way - insert new row in TypeX, get ID, insert row in TypeA. In this solution you'll have matching rows in TypeX and TypeA or TypeX and TypeB.

TypeX(TypexID int not null primary key identity (1,1), SomeCommonColumn int) TypeA(TypexID int not null primary key, TypeASpecific int) TypeB(TypexID int not null primary key, TypeBSpecific varchar)

It is only way to implement such situation in relation theory-clear and non-redundant way. It looks not very simple, but usually these tables covered by a view and stored procedures so these tables can be used by application as single (virtual) table.

Thank you, Alexander