MS SQL Server 2000 question

scojones

Hi there!
So I have designed a database, which was originally going to be created with MySQL, but the powers that be have decided that they want me to create it using MS SQL Server 2005, which is fine I guess. But I'm begining to notice slight differences with the data types. For instance, with MS, there is no "enum" datatype, you have to use a check constraint instead. Does anyone have any experience using check constraints? I just want a field I have, to have two values, either "Doc" or "Code".

So I have a table called ConfigItems, this table will hold info on either documents belonging to a project, or code belonging to a project. It's pretty basic, all it will show is how much code as been written for a component ( a project consists of n number of components), or how many pages a document is. So I have a components table and a documents table, these are static and never change, over time the documents grow and so does the code, so this is what the ConfigItems are. Now.....

My original idea was to have a Type field, which says it's a code(component) item or a doc item, and a TypeID, which would either be the id of the Documents table, or the id of the Components table. However I do not think it is possible to have a foreign key that links to two tables...

Does anyone know if this is possible with MS SQL Server?

If it is not possible, I guess I will just have to add a docID and componentID field to the ConfigItems table, and define extra constraints, saying that if Type is Doc then componentID must be null.

Is that the right way to go about it? If I have not been clear enough please let me know and I will try to clarify anything that may seem blurry to you

Cheers guys.

bonkey

sjones wrote:

My original idea was to have a Type field, which says it's a code(component) item or a doc item, and a TypeID, which would either be the id of the Documents table, or the id of the Components table. However I do not think it is possible to have a foreign key that links to two tables...

Does anyone know if this is possible with MS SQL Server?

Jein....as they would say here.

You can't do it with foreign keys...but you can implement the equivalent logic using Triggers. I wouldn't recommend it though...not a terribly efficient or maintenance-friendly way of doing things.

If it is not possible, I guess I will just have to add a docID and componentID field to the ConfigItems table, and define extra constraints, saying that if Type is Doc then componentID must be null.

Thats probably how I would do it.

If you really want/need a "combined" column (with whichever of docId or compId was filled), then either create a view, or simply select ISNULL(docId, componentId) as DocOrComponentId in your queries.

Alternately...is it possible to store Docs and Components in one table, with a D/C flag which says which is which? Then there's no problem at all.

scojones

bonkey wrote:

Jein....as they would say here.

You can't do it with foreign keys...but you can implement the equivalent logic using Triggers. I wouldn't recommend it though...not a terribly efficient or maintenance-friendly way of doing things.


Thats probably how I would do it.

If you really want/need a "combined" column (with whichever of docId or compId was filled), then either create a view, or simply select ISNULL(docId, componentId) as DocOrComponentId in your queries.

Alternately...is it possible to store Docs and Components in one table, with a D/C flag which says which is which? Then there's no problem at all.

Aye cheers bonkey, now I just have to figure out how the check constraints work, I want the Type field to be a flag for either Doc/Code, and if it's Doc, then the DocID will point to the primary key of the Documents table, the same for CodeID and the Code table.

I thought there might be another way around but this will have to do. Thanks again

mewso

You could have a bit field called something like IsDoc. So thats 1 for Doc and 0 for Code.
I assume you need to have the relationships because you can use one field to reference 2 tables easily without actually creating the relationship just forming your queries accordingly.

bonkey

sjones wrote:

Aye cheers bonkey, now I just have to figure out how the check constraints work,

Pretty straightforwardly really....unless my memory is failing, the "check" part is written more-or-less identically to a WHERE clause.

I'd imagine you want something like:

COALESCE(DocID, CodeID) IS NOT NULL 
AND ( DocID IS NULL OR CodeId IS NULL)

(Now, just look up how to add CHECK Constraints to tables and you're laughing.

musician wrote:

I assume you need to have the relationships because you can use one field to reference 2 tables easily without actually creating the relationship just forming your queries accordingly.

You can have also unique values without defining UNIQUE constraints and/or Primary Keys. In fact, there's almost no functionality that you can't avoid using...so where would you draw the line?

I generally recommend that ppl design the database correctly, unless there are very good reasons to do otherwise. I can't think of a reason why you'd want to remove referential integrity checking from the database when a slight redesign can leave it in. There's no performance, space-saving or other "significant" reason to avoid the redesign.

Trusting the client application to get it right is terribly risky, IMHO. It will also add work in ensuring things like parent records never getting deleted by accident, leaving orphaned children (and so on and so forth).

scojones

Cheers bonkey, that solved that issue. I had one more which is pretty similar to the previous one. It's about CHECK constraints again.

I have another Type field, which is in the Documents table, this type field specifies what type of document the document is. For instance, a HLD, PRD, LLD, PIP.... etc.

I have tried the following and it appears to have worked, so I'm posting it for everyone elses benefit

Type = 'HLD' or Type = 'LLD' or Type = 'PIP or Type = 'PRD' or Type = 'OTHER'

Thanks again lads, you all got me out of a hole there

Ivan Dunaev

am I the only one who think that this logic (putting different stuff in one basket) is incorrect? obviously, it will imply IFs and other conditions checking everywhere.

Merrion

am I the only one who think that this logic (putting different stuff in one basket) is incorrect?

Nope - Dr E. F. Codd was also of this opinion.
"The key, the whole key and nothing but the key; so help me Codd"