Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Posts
    3

    Data modeling scenario

    I'll try to explain this scenario that I'm having a tough time modeling. I'll use events / schedules for objects since it is very similar to what I'm doing.

    CITIES ---< EVENTS ---< EVENTACTIVITIES

    At the top is CITIES. Each 1 CITY host many EVENTS. Each 1 EVENT has many EVENTACTIVITIES.

    CITIES ---< LOCATIONS

    Now, 1 CITY has many LOCATIONS within it.

    The problem:

    The EVENTACTIVITIES occur in 1 or more possible LOCATIONS. But at the same time I have to make sure that the LOCATIONS that the EVENTACTIVITIES is occuring in, is in the same CITY as the EVENT. In other words, when you're assigning LOCATIONS for a particular EVENTACTIVITY, it would be invalid to have a LOCATION that's in some other CITY that this event is not even in.

    Shouldn't I try to model this properly rather than handling it at the GUI? I could just say here's the valid locations for this activity, choose one. But the DB would still allow "invalid" rows, by my definition.

    Any ideas?
    Last edited by gc76; 04-10-03 at 13:18.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    >> "Shouldn't I try to model this properly
    >> rather than handling it at the GUI?"

    yes

    not sure how you do it in whatever modelling methodology you happen to be using, but you've explained it rather well

    yes, in the gui, you would offer only 'valid" locations for the activity, being a list of locations for the event

    in the database you can declare a CHECK constraint to ensure that if the gui is somehow circumvented, and an invalid location is submitted, the database will disallow the insert/update

    rudy

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Data modeling scenario

    Originally posted by gc76
    I'll try to explain this scenario that I'm having a tough time modeling. I'll use events / schedules for objects since it is very similar to what I'm doing.

    CITIES ---< EVENTS ---< EVENTACTIVITIES

    At the top is CITIES. Each 1 CITY host many EVENTS. Each 1 EVENT has many EVENTACTIVITIES.

    CITIES ---< LOCATIONS

    Now, 1 CITY has many LOCATIONS within it.

    The problem:

    The EVENTACTIVITIES occur in 1 or more possible LOCATIONS. But at the same time I have to make sure that the LOCATIONS that the EVENTACTIVITIES is occuring in, is in the same CITY as the EVENT. In other words, when you're assigning LOCATIONS for a particular EVENTACTIVITY, it would be invalid to have a LOCATION that's in some other CITY that this event is not even in.

    Shouldn't I try to model this properly rather than handling it at the GUI? I could just say here's the valid locations for this activity, choose one. But the DB would still allow "invalid" rows, by my definition.

    Any ideas?
    Yes, the DB should enforce this rule. The best way would be a kind of cross-table check constraint, but these don't currently exist in any product AFAIK.

    You could do it with a check constraint if the CITY PK was propagated down through EVENTS to EVENTACTIVITIES PK's and to LOCATION's PK, but that may not be acceptable.

    You could use a database trigger on the EVENTACTIVITYLOCATION table to lookup the cities on both sides to see that they match.

    Another alternative is to prevent direct insert into the table (do not grant INSERT privilege to users), and make the application call a packaged procedure instead. That procedure would then enforce the rule before inserting the row.

  4. #4
    Join Date
    Apr 2003
    Posts
    3
    Thanks for the quick replies. I'm using SQL server 2000 by the way.

    So you're saying this is more of a rule rather than something that can be enforced through a relational design? If this is the case, I guess I'll create a table to hold the EVENTACTIVITY's LOCATION's. Is this the correct way to implement the many-to-many between activities and locations?

    ACTIVITYLOCATIONS
    -----
    ID
    EventActivityID
    LocationID

    If so, should I have an ID column to be the PK or do I have the other two columns as a composite key? How is this done in SQL server 2000?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my advice is not to have a separate surrogate primary key for relationship or junction tables like this unless the relationship table has children -- but most of them you find in real life don't

    just declare a composite PK --

    create table ACTIVITYLOCATIONS
    ( EventActivityID integer references EVENTACTIVITY (EventID)
    , LocationID integer references LOCATION (LocationID)
    , primary key (EventActivityID, LocationID)
    )

    one of the problems with a surrogate key for relationship tables like this is that it then becomes possible to enter the same event at the same location more than once, so you end up declaring a composite unique constraint for it, making the primary key index on the surrogate key rather superfluous

    rudy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •