Database design - enforcing referential integrity on control tables? (sql server and asp 3.0)

G

Guest

Tables:
=====

User
------
ID
Name
NationID
StateID

Nation
-------
ID
Nation

State
-----
ID

Question:
Should an asp database solution enfore referential integrity on control
tables like:

- country
- State

....in a primary Website User Table [Users]?

Or,

Should these be hard-coded into the web page and simply inserted into the
Piimary [User] Table:

User
----
ID
Name
Nation
State

Why do I ask?
----------------
By enforcing a relationship between the tables I am creating extra work for
future stored procedures and allow for possible errors when inserting a new
registrant
into my system if for some reason one of the control table values was
deleted without cascading it to the Primary?

Appreciate any advice on this scenario...

Thanks
Jason
 
T

Tarwn

My suggestion would be to include a boolean field in your state and country
tables to control whether they are selectable or not. When you select from
those tables to display them to the end user then select only where the
boolean is set to whichever you regard as "selectable". Then disallow any
dba's/what-have-you from being able to delete entries from the state/country
tables. Maybe make a tiny web interface to allow them to set the boolean flag.

This way you keep critical user data in the system but can also "remove" a
country/state as a selectable option for future customers/users.

If you decide not to have referential intergity on these tables (in order to
keep the all-important user data) then I would suggest using LEFT OUTER JOINs
in retrieveing the information, in that manner you would receive valid
state/country values for the users that still had valid assignments, but
would eceive nulls for users that did not. If you used an inner join you
wouldn't receive those users' data back because the data ould not match the
keys in the accompanying state/country tables.

-T
 
G

Guest

That is an interesting solution.... In other words, never 'delete' the
record - merely change the 'displayStatus'.....

Surely this premise should then be used for each and every lookup table
which will have REF INT enabled?

Do you use this solution yourself in all your applications?

Finally, I have only recently mastered the INNER JOIN in sql server:

Example:


CREATE Procedure spr_GetUserSelections
@u_ID int

As
set nocount on
Select *
FROM t_user
INNER JOIN Source ON
t_user.u_SourceID = Source.SourceID
INNER JOIN YachtPlacement ON
t_user.u_YachtPlacementID = YachtPlacement.YppID
INNER JOIN Broker ON
t_user.u_BrokerID = Broker.BrokerID
INNER JOIN State ON
t_user.u_StateID = State.StateID
INNER JOIN Nation ON
t_user.u_NationID = Nation.NationID
WHERE t_user.u_ID=@u_ID



return
GO


....Is it possible to adjust the above to OUTER JOINS without affecting the
results...I am slightly confused on when to use one or the other?

Many thanks for your advice...I would never have thought of thought of that
solution in relation to Ref Integrity (ie Bln values)

- Jason
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top