CODEWITHSUNDEEP
sqlsql-serversql-variant
mark
mark

Reputation: 62764

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

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:

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.

Upvotes: 1

Views: 482

Answers (5)

Alsin
Alsin

Reputation: 1618

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

Upvotes: 0

HLGEM
HLGEM

Reputation: 96572

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.

Upvotes: 0

M.Turrini
M.Turrini

Reputation: 738

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).

Upvotes: 1

Rich.Carpenter
Rich.Carpenter

Reputation: 1056

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'.

Upvotes: 1

marc_s
marc_s

Reputation: 754488

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

Upvotes: 12

Related Questions