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

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

[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

"ja***@catamaranco.com" wrote:

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

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