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

Discussion in 'ASP General' started by Guest, Aug 12, 2004.

  1. Guest

    Guest 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
     
    Guest, Aug 12, 2004
    #1
    1. Advertising

  2. Guest

    Tarwn Guest

    RE: Database design - enforcing referential integrity on control table

    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

    "" 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
    >
    >
    >
     
    Tarwn, Aug 13, 2004
    #2
    1. Advertising

  3. Guest

    Guest Guest

    Re: Database design - enforcing referential integrity on control table

    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
     
    Guest, Aug 13, 2004
    #3
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Paul Johnson

    Business Rules & Referential Integrity

    Paul Johnson, Nov 20, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    566
    Paul Johnson
    Nov 20, 2004
  2. terry

    Mysql referential integrity

    terry, Sep 9, 2003, in forum: Java
    Replies:
    1
    Views:
    507
    SS Tsay
    Sep 9, 2003
  3. earthling
    Replies:
    0
    Views:
    392
    earthling
    Mar 15, 2005
  4. Paul Daly
    Replies:
    0
    Views:
    532
    Paul Daly
    Jan 12, 2007
  5. Weasley
    Replies:
    0
    Views:
    402
    Weasley
    Feb 2, 2010
Loading...

Share This Page