Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Database design - enforcing referential integrity on control tables? (sql server and asp 3.0)

Reply
Thread Tools

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

 
 
Guest
Posts: n/a
 
      08-12-2004
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


 
Reply With Quote
 
 
 
 
Tarwn
Guest
Posts: n/a
 
      08-13-2004
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

"(E-Mail Removed)" 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
>
>
>

 
Reply With Quote
 
 
 
 
Guest
Posts: n/a
 
      08-13-2004
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





 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Foreign keys in MySQL and referential integrity of Database Weasley Java 0 02-02-2010 06:12 AM
referential integrity xsd schema file Paul Daly XML 0 01-12-2007 05:12 PM
A matter of style and referential integrity! earthling XML 0 03-15-2005 09:01 AM
Business Rules & Referential Integrity Paul Johnson ASP .Net 0 11-20-2004 04:41 PM
Mysql referential integrity terry Java 1 09-09-2003 02:04 PM



Advertisments