Schema Relationship Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JamesHoward - 03 Oct 2007 22:44 GMT

I have a set of predefined constants with a set of predefined
relationships and am looking for a way to link them if possible to
ensure data integrity.

An example would be if I wanted to keep track of programs and I have a
table Programs with rows operating system type and programming
language. If both operating system type and programming language are
foreign keys to reference tables (just tables with a primary key and a
name to the element they are referencing) is there a way to ensure
data integrity for possible nonsensical pairings such as programming
language c# and operating system unix?

Thanks in advance,
Jim Howard

Reply to this Message

JOG - 04 Oct 2007 01:15 GMT

> I have a set of predefined constants with a set of predefined
> relationships and am looking for a way to link them if possible to
[quoted text clipped - 10 lines]
> Thanks in advance,
> Jim Howard

Well, those non-viable pairings are part of your data. You could have
an ugly hard coded check constraint, or alternatively create a table
containing those disallowed pairings, and check select against it on
any insertion into your programs table. I'd check out the particular
flavour of RDBMS you are using on google for appropriate syntax. Good
luck.

Reply to this Message

Ed Prochak - 04 Oct 2007 22:09 GMT

> > I have a set of predefined constants with a set of predefined
> > relationships and am looking for a way to link them if possible to
[quoted text clipped - 17 lines]
> flavour of RDBMS you are using on google for appropriate syntax. Good
> luck.

better is a table of allowed pairings and a Foreign key constraint
(IMHO)

Ed

Reply to this Message

JamesHoward - 05 Oct 2007 18:43 GMT

Thanks for the answers. It sounds like the best solution between
allowed pairings and disallowed pairings comes down to how much data
you will have to enter. If one of those is dense in a large possible
data space then it wouldn't be ideal to enter that much manual data.

I think I am going to go for the disallowed pairings in this case as
it is less data to enter.

Jim

Reply to this Message

David Portas - 06 Oct 2007 19:55 GMT

> Thanks for the answers. It sounds like the best solution between
> allowed pairings and disallowed pairings comes down to how much data
[quoted text clipped - 3 lines]
> I think I am going to go for the disallowed pairings in this case as
> it is less data to enter.

Check the features of your DBMS product first. Some SQL DBMSs don't support
multi-table constraints other than foreign keys. If that's the case with
your DBMS then you may have to resort to procedural code to enforce the rule
about disallowed pairings. On the other hand, the rule for valid pairings
CAN be implemented using only a foreign key.

Signature

David Portas

Reply to this Message

David Portas - 04 Oct 2007 01:27 GMT

>I have a set of predefined constants with a set of predefined
> relationships and am looking for a way to link them if possible to
[quoted text clipped - 10 lines]
> Thanks in advance,
> Jim Howard

That could be four tables for example. The constraint you refer to would be
a referential constraint between Programs and ValidPrograms.

Programs {os, language}
ValidPrograms {os, language}
OS {os}
Language {language}

Signature

David Portas

Reply to this Message