Table Relationship or only relation in queries?

[03Explorer]

I have been exposed to a few developers who only design their databases where the relationships are at the query level. But now that I am tasked with building a database I have not read anything about this option. Is this a wrong approach and stick with all I am reading about making the relationships at the table level?

[green]This question is a small part MS Access (beta version, to show to upper management), but mainly for a more enterprise platform (roll out version will not be MS Access).[/green]

 

[johnherman]

Whether you enforcer referential integrity at the database level or only apply foreign keys at the query level could be dependent on many things. Data quality, for instance, could make joining difficult and declarative referential integrity might fail. Use of blanks or zeros or phony dates instead of nulls could result in inappropriate joins. Or if the tables need to be denormalized to support performance, then joining selected fields could have intra-record dependencies. That is, only link to the Pregnancy record if the Gender is 'F'. You get the idea, I think. For better answers, provide more detail on your situation.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[gmmastros]

Put the relationships on the table.

Think of it this way... Have you ever made a programming mistake? I bet you have. By putting the relationship on the table, you safeguard yourself against certain types of mistakes.

-George

"the screen with the little boxes in the window." - Moron

 

[03Explorer]

johnherman, I kind of got your jist... The Primary and foreign keys will not contain nulls, so either way of relating (tables or query level) will allow them to connect. It just seems odd that a few developers independant of each other program using the query level of realating the tables.

gmmastros, I like your idea... I have programmed with mistakes in the query level which as you clearly describe. Safeguard the data by not implying the relationships at the query level but make relationships at the table level.

Thanks to you both... end result is to best document your intent as a programmer is to relate the tables and not expect the relationships be soly constructed at the query level.

 

[SQLSister]

Relationships which are critical to data integrity should always be created at the database level just as business rules should always be enforced at the database level (even if they are are also enforced in the GUI).

As a designer, you never know who will be programming queries or adding records through bulk insert or otherwise impacting the data in your database. You must consider this is designing or you will have data integrity problems.

Data integrity problems often become impossible to fix. For instance if I rely on queries to enforce my relationships and a new prpogrammer comes in and I tell him to delete any customers from the database who have not ordered in the last six months. Then two months later I run a quarterly report and find that I have 100000 in unpaid bills that are not directly related to a person (because their customer record was deleted but the new person didn't realize there was a realtionship to the financial records), how am I to find who those customers were?

We have a table in one database where I work that has 99 foreign key relationships (that's a problem too but I didn't design that one and I'm stuck with it right now until we completely restructure). Could anyone remember all of them when writing delete queries? Without foreign key constraints, the chances of a data integrity problem on this database would be 100%.

Programmers who are not database designers often do not think about data integrity. Any programmer who would rely on queries to enforce critical relationships needs to educated on why that is an extremely poor programming practice.

Questions about posting. See faq183-874

 

[03Explorer]

SQLSister, What would be a normal amount of foreign keys in a single table? I have a table with client information with 17 fields. Seven of them are foreign keys. Is that acceptable or excessive? Some of the foreign keys are to external tables for Cities, States, Phone numbers, SICcodes, and Job title tables.

 

[johnherman]

If your data is clean and not de-normalized, do it at the database level.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[SQLSister]

That depends on the database. What it's being used for, how many records, how many users and how complex the information being stored is. Lots of things in a database design are balancing acts. The more foreign key relationships, the harder it is to delete records for instance. But the more likely it is that the data will be correct.

Most tables would not have more than a few relationships. But a critical primary key table (like say Customers) could have many more. And a few critical child tables might have many primary key realtionships (Something like orders comes to mind which might need to have relationships to sales reps, customer, product, inventory, shipping location, etc.)



Questions about posting. See faq183-874