Enforcing integrity w/multiple foreign tables

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

SQL Server 2000 Forums

SQL Server Development (2000)

Enforcing integrity w/multiple foreign tables

Author

Topic

KHeon
Posting Yak Master

135 Posts

Posted - 2002-10-09 : 11:49:51

Hello!

This is probably and odd question but I'm hoping that maybe somebody can give me some insight on the best way to handle this.

I have a table which I want to use as a map table, referencing a foreign key in a child table. Problem is the child table could be one of numerous. How can I define a FK constraint that doesn't reference the same table for each record?

For instance (psuedo version):

USERS table -
- user_id

REQUESTS table -
- request_id
- user_id
- request_code
- form_id
- child_id
- create_date
- modify_date

FORMS1 table
- form1_id
- form1_data_1
- form1_data_2
- form1_data_3

FORMS2 table
- form2_id
- form2_data_1
- form2_data_2
- form2_data_3

In this example, request_code would be the identifier for determining which of the "FORMS" tables is referenced. I can't figure a way to enforce integrity in this situation.

Could I (should I?) use a trigger in this situation? Does this design even make sense? Is it a bad idea to do this? If so I'm open to options. I've run into this before and know I'll run into this again, would like to know how to best handle this from a db point of view.

The concept here is that a user registers, fills out form a, we track the results in the FORMS1 table. This is then considered a "request" and a record is entered in the REQUESTS table and the detail in FORMS1. If this user fills out form b then another record would go into REQUESTS and the detail in form b. The reason multiple tables are needed is because the forms are not alike, the data captured changes per form, so the structure has to accomodate this.

Any thoughts?

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-10-09 : 12:05:17

2 comments... why are form1 and form2 different tables? Couldn't they just hold the same info? Might wanna look at how the tables were designed.

Only other option I can see working is an instead of insert trigger that validates the data before its entered.

Theres probably more options then that, but I'd probably take a good look at my table design first.

-----------------------
SQL isn't just a hobby, It's an addiction

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-09 : 12:22:49

If you're only gonna have 2 or 3, maybe 4 form tables, I suppose you could get a trigger to handle it, but anything more than that and you'll go crazy eventually. Even with 2 or 3 form tables it's a lot more work than is necessary.

If you do want to stick with multiple form tables, why not just add the user_id, create_date etc. columns to the forms tables? Then you don't need to have a requests table at all. You could create a view that UNIONs all of them together if need be.

Another option is to change the forms tables into one, using an attribute/value structure:

CREATE TABLE Forms (FormName varchar(20) NOT NULL,
ElementName varchar(20) NOT NULL)

Unfortunately you're stuck with using varchar to store all of the data values, but if that's not a problem, then you can define your forms like this:

INSERT INTO Forms VALUES ('Form1', 'FirstName')
INSERT INTO Forms VALUES ('Form1', 'LastName')
INSERT INTO Forms VALUES ('Form1', 'Address')
INSERT INTO Forms VALUES ('Form1', 'City')
INSERT INTO Forms VALUES ('Form1', 'State')
INSERT INTO Forms VALUES ('Form1', 'Zip')
INSERT INTO Forms VALUES ('Form2', 'UserID')
INSERT INTO Forms VALUES ('Form2', 'FavoriteColor')
INSERT INTO Forms VALUES ('Form2', 'FavoriteMusic')
INSERT INTO Forms VALUES ('Form2', 'FavoriteBeer')

You'd use this to generate the form structure that the user fills out, then store the results in a new Requests table like this:

CREATE TABLE Requests (user_id int NOT NULL,
FormName varchar(20) NOT NULL,
ElementName varchar(20) NOT NULL,
ElementValue varchar(8000) NOT NULL,
create_date datetime NOT NULL DEFAULT (getdate()),
modify_date datetime)

And if I filled out form 1, the data would look like:

user_id   FormName      ElementName     ElementValue     create_date      modify_date
1         Form1         FirstName       Rob              10/9/2002
1         Form1         LastName        Volk             10/9/2002
1         Form1         Address         123 Main St.     10/9/2002
1         Form1         City            Gainesville      10/9/2002
1         Form1         State           GA               10/9/2002
1         Form1         Zip             30504            10/9/2002

I know you think this looks ugly, but it's a lot more flexible than having 10 forms tables and trying to maintain a foreign key with all of them. The one major downside to this structure is the fact that all data ends up as varchar, and you need to add validation for non-character data.

I'll be honest, I really don't like the attribute/value structure myself (I once had an assignment to generate reports from this kind of structure, I walked away from it after 1 hour) but other than my original suggestion I don't see another method of doing it.

KHeon
Posting Yak Master

135 Posts

Posted - 2002-10-09 : 13:18:45

Rob - I actually thought about the option of defining elements like you mentioned but didn't just to see if it would be suggested. Was actually hoping someone would suggest it. I've done something very similar for a products and product specs database. The downside like you said is that the value is always varchar(x), which might not be a big deal for web-submitted data. I agree, it's considerably more flexible then having a table for each form.

M.E. - Bad example on my part. FORM1 and FORM2 would contain different types of data, not the same. Think of FORM1 as a Contact Us form and FORM2 as a Request Info form (or something of the ilk).

This is for a tool I'm building to manage leads, of various types and from various sources, thus the reason for multiple types of "requests". Thanks for the suggestions, gives me something to consider as I move forward with the design.

Thanks!

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

Lavos
Posting Yak Master

200 Posts

Posted - 2002-10-10 : 01:10:49

An actual example of mock data and the table layout for your two different forms would be helpful here. However, I *think* I understand what is going on, so let me explain my verison of what I think you're saying.

A person fills out a request. Depending on which form is filled out, the details are input into one of the forms tables. An entry is input into the requests table at the same time to show that a request has been made, which also has a field to indicate what type of form (and hence forms table) the details are in.

In this instance, I think you have the primary and foreign keys mixed up. The unique instance is the RequestID, and the form tables should reference *that* as a foreign key. In addition, you could define a unique index on that column to insure that a form table has only one row of details for a given request.

There are problems with this approach to maintaining integrity. What happens if the "form type" column is edited to a different form? Do you have to insure that the different form detail lines match the form type in the main table? (i.e. for request 2, it should have an entry in forms table AB only, but someone inserted a detail row in form table CD.) You can handle this with triggers, but I have a different approach to handle it with constraints. Namely, add a new unique index on the RequestID and the FormType columns. (Please retain the unique or primary key constraints on RequestID). You can then add a FormType column to each of the form tables with a CHECK constraint that only accepts the value that is supposed to be in that table. From there, you declare a composite FOREIGN KEY on both the RequestID and FormType columns.

That seems confusing to me, so let me add some example code


CREATE TABLE Requests (
  RequestID INT IDENTITY PRIMARY KEY,
  FormType varchar(10),
  CreationDate DATETIME DEFAULT(GetDate()),
  CONSTRAINT Request_UK UNIQUE(RequestID, FormType)
)

CREATE TABLE FormA (
  RequestID INT,
  FormType varchar(10) CHECK (FormType LIKE 'FormA'),
  FirstName varchar(10),
  LastName varchar(20),
  CONSTRAINT FormA_FK FOREIGN KEY (RequestID, FormType)
     REFERENCES Requests(RequestID, FormType) ON CHANGE CASCADE,
  CONSTRAINT FormA_UK UNIQUE (RequestID) -- Note that due to the CHECK constraint, only RequestID needs to be unique
)

CREATE TABLE FormB (
  RequestID INT,
  FormType varchar(10) CHECK (FormType LIKE 'FormB'),
  AmountOwed NUMERIC(10,2),
  CONSTRAINT FormB_FK FOREIGN KEY (RequestID, FormType)
     REFERENCES Requests(RequestID, FormType) ON CHANGE CASCADE,
  CONSTRAINT FormB_UK UNIQUE (RequestID) -- Note that due to the CHECK constraint, only RequestID needs to be unique
)

I haven't checked the syntax, but that would work.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-10-10 : 01:49:00

quote:

The concept here is that a user registers, fills out form a, we track the results in the FORMS1 table. This is then considered a "request" and a record is entered in the REQUESTS table and the detail in FORMS1. If this user fills out form b then another record would go into REQUESTS and the detail in form b.

Emphasis mine..

This seems to me to be a 1:1 relationship between a Request and type of form.

Therefore a User has many requests and each request has one and only 1 form. Have I interpreted that right?

DavidM

"SQL-3 is an abomination.."

Edited by - byrmol on 10/10/2002 02:07:50

Something like this...pseudo code

USERS(UserID) KEY (UserID)

REQUESTS (RequestID, UserID, FormType)
You have a choice for your Key for this table..
--KEY (RequestID, UserID) or (RequestID, FormType)
Lets choose (RequestID, UserID) as the KEY and place a UNIQUE CONSTRAINT on (RequestID, FormType)

FORM_A(RequestID, UserID, FormType)
KEY(RequestID, UserID) CHECK CONSTRAINT (FormType = 'FORMA')

FORM_B(RequestID, UserID, FormType)
KEY(RequestID, UserID) CHECK CONSTRAINT (FormType = 'FORMB')

Add Foreign Key constraints and repeat as necessary.....

Edited by - byrmol on 10/10/2002 02:33:37

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources