Problem retaining referential integrity in my data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Problem retaining referential integrity in my data

Author

Topic

littlecharva
Starting Member

4 Posts

Posted - 2007-04-12 : 07:47:04

I'm designing a database to store information about jobs that are in progress at a property. More than one job can be in progress at a property at one time and each different kind of job can contain different data, although they all share some common fields such as StartDate.

So I have a table that stores the property details PropertyDetails:

*ID
PropertyAddress
PropertyPostCode

Then I have a table that stores all of the jobs' shared details:

*PropertyID
*JobID - These three make up a compound primary key
*JobType
StartDate
EndDate

Then I have individual tables for each of the Jobs, for example BuildingWork:

*JobID
BuildingContractor
InsuranceCompany

Which works great, and enables me to query all basic job details from one table (JobDetails) rather than multiple tables for every job type.

BUT: I don't know how to enforce the referential integrity of the database. Obviously I can use a constraint to cascade deletes from the PropertyDetails table to the JobDetails table through the PropertyID, but there doesn't appear to then cascade the deletes from the JobDetails table to the individual Job tables as JobDetails has no idea what tables are there.

If I store the relevant individual table name as the JobType in the JobDetails table, could I use a trigger to somehow delete the related record from that table?

Littlecharva

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-12 : 08:19:26

Are you creating new Job tables on the fly as jobs come up? That is a bad idea. Consider grouping the jobs by type (having similar attributes) and having a table for each type. Or, consider placing attributes specific and unique to each job in a single XML column (especially if you are developing on SQLServer 2005).

e4 d5 xd5 Nf6

littlecharva
Starting Member

4 Posts

Posted - 2007-04-12 : 08:27:11

Not on the fly as jobs come up, but there will be new Job tables added as our company branches out into different areas.

We currently have two different systems because our business expanded into a different market area and a system was built to manage those jobs. I'm trying to design a system from scratch that will allow both these systems to run in one and allow for future expansion.

Essentially the other tables are extension tables, the JobDetails table contains the main details of the job and the other tables contain any additional data.

I'm using 2000 not 2005.

Littlecharva

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-12 : 09:43:06

Consider using an XML datatype.

e4 d5 xd5 Nf6

littlecharva
Starting Member

4 Posts

Posted - 2007-04-12 : 10:31:15

I don't think an XML datatype will work for me further down the line.

Is there no way I can achieve a cascading delete using a trigger then?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-12 : 12:07:07

You would have to use dynamic SQL to check for existing related tables, and I don't think triggers allow dynamic SQL.
Could you work around this limitation? Yes.
Should you work aroung this limitation? No.
Triggers should not be used to compensate for poor design.

e4 d5 xd5 Nf6

littlecharva
Starting Member

4 Posts

Posted - 2007-04-12 : 13:19:32

Okay then, how do I improve my design without using an XML datatype?

I've drawn a diagram of my tables, each Property can have more than Job.

So I figured if I could remove all the duplicated fields to another table I could replace the first three tables with one table and reduce the number of fields in the other two tables.

It also means I could pull off a list of all jobs with a simple select query rather than having to union five queries together.

As you can imagine this is an over-simplified version of the actual db. Can you offer me any advice on how to fix my design?

Littlecharva

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-12 : 14:17:06

If you are ruling out XML then your options are:


1) Separate table for each job type to hold its unique attributes.
	Best if you rarely add job types or each type has many unique attributes.
	Pros:	Efficient, well-defined schema.
	Cons:	Will have to modify triggers and other code whenever you add a job type.
2) Maintain separate tables for each Job type, and use a UNION view to query all simultaneously.
	OK for applications that are primarily read-only.
	Pros:	All data for each job in a single table simplifies coding.
	Cons:	Will have to write trigger code to insert into UNION view.
		UNION view may not be as efficient.
3) Throw all the attributes into the Jobs table and put up with lots of NULL values.
	Simplest if each job type does not contain many unique attributes
	Pros:	Easiest to administer and write code around.
	Cons:	May have lots of null values (not that big a deal).
		Will have to write code that doesn't break if columns are added to the table.
		Should create independent views off of this table for each job type.
3) Implement an EAV (Entity/Attribute/Value) design.
	Most robust and flexible method if you refuse to use XML.
	Pros:	Can modify the virtual schema without changing physical schema or code.
	Cons:	Welcome to SQL Hell.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources