Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Managing one-to-one OR one-to-many relationships?

    Hi.... ok, I'm starting to put together the schema for a rather complex (by my standards) chunk of business logic. In trying to encapsulate various rules, I'm finding that I'd need tables that don't really adhere to my idea of a solid structure, and I'd appreciate some guidance. Apologies in advance for my convoluted explanations but hopefully it'll indicate how I've arrived at the predicament I'm in.

    The basic idea: Users can send Greetings to each other. So I've got a Users table and a Greetings table.

    Now, the purpose of sending greetings is that the sender donates to a Charity in order to send it. So each Greeting is tied to a Charity.

    So far, so simple. Now we bring Events in. And I get stuck.

    Users can join or create Events, and have Greetings sent to them for that Event. An Event can be tied to one or more Charities. There are three types of Event.

    "Individual" events are created by individual users. One Event, one User. Like, "My Birthday".
    "Global" events are, well, global. Users can't create them, but they can join them. One Event, many Users. Like, "Christmas".
    "Branded" events are... semi-global. Some information, like the event title, is global. But when a user joins the event, they can set their own start and end dates.

    Now, at the moment, I've got four tables managing this:
    Users
    Events
    EventTypes
    UserEvents

    So... hopefully self-explanatory. EventTypes holds the three types, and Users are tied to Events through UserEvents.

    But...

    Every "individual" Event would have to go into the Events table, and have a corresponding entry in UserEvents. I'd be using a one-to-many structure to store a one-to-one relationship. This seems bad.

    If I get round this by having an Events table with a User ID in, then I'd need two Events tables - one to store events with an individual user, one to store events with multiple users.

    So... I'm kind of running myself in circles. And every solution seems to involve storing the same stuff across different tables. And it all seems very messy.

    If you've read this far, and not gone cross-eyed yet, can you suggest anything?

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Your current design seems reasonable. Does UserEvents really represent a one to many relationship though? I would expect a user can participate in more than one event, so the key of UserEvents would be something like (User, Event) ie "many users to many events". The "many to one" example is just a not-so-special case.

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Ok, yes sorry I didn't make this very clear

    A diagram may help - see attached 1.gif (why can't I post images?!)

    So: UsersEvents ties many Users to many Events.

    However, I see two problems with my schema as it stands. I think the first one is relatively minor, but I can't get my head around the second.

    • For an Event created by, and joinable by, an individual User, I'd have to create an Event and add the User to it through the UsersEvents table. This seems overkill and, more importantly, doesn't explicitly enforce the business logic that only that User can join that Event. There's nothing in the schema in that case that would stop me adding another User to that Event.
    • Each and every Event would need to go into the Events table. For these "Branded" Events, I'd need to create a different Event each time, with a different start and end date but the same core info (ie: title, description etc).


    I think I can see two solutions, and I'm not sure which is best.

    Diagram 2 (attached GIF) adds an EventTemplates table. If it's just an individual Event created by a User, everything goes in the Events table. But if it's a global Event, then the core info goes into the EventTemplates table. This is good because it gives me a way to identify the same "event" that different users have joined - they might be held at different times, but they're all the same template. However, it means that I've got a FK in the Events table that might be NULL (if it's an individual Event). This seems bad. Like, don't-cross-the-streams bad.

    Diagram 3 (attached GIF) seems a bit more elegant - take the information that's specific to a User Event, and put it into the UserEvents table. I've still got duplicate fields in different tables, and it still doesn't address the first problem listed above, but it's about as much as my fried brain can manage.

    Does any of this make any sense? Ever?
    Attached Thumbnails Attached Thumbnails 1.gif   2.gif   3.gif  

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if certain events are for an individual user only, then you could have a foreign key right in that event for that user, and that one user would of course be the only user possible in that FK column -- and the event would not participate in any many-to-many relationship via the UsersEvents table

    so that's your "relatively minor" issue resolved (except for the fact that to list all events for a given user, you'd have to use a UNION -- one SELECT for this FK relationship, and another SELECT for the relationship via the many-to-many table -- and so you've shifted the convenience of not needing to insert a many-to-many row into the complexity of the retrieval query))

    regarding global events, put all the information into the Events table, and don't repeat them in the UsersEvents table

    if you also want to have a template (e.g. for recurring monthly staff meetings, or such), then simply designate an event in the Events table as a template event (so that it cannot participate in the many-to-many relationship) and use it to copy (via your application) column values into each new event that is built from that template

    in other words the templates are ~in~ the Events table

    finally, one small point: the UsersEvents table does ~not~ require its own (surrogate) id -- the PK of that table should be the composite of the two FKs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2002
    Posts
    189
    Really? Just a boolean isTemplate field in Events? So there would be Event records in there that aren't really Events, in the sense that Users would never be linked to them?

    No, I don't like the sound of that, although I've probably misunderstood you. Say I've got a template Event called... "Car Boot Sale". Lots of users add their own "Car Boot Sale" Events. This would mean that:
    a) I've got a whole lot of records in Events, with the same data in at least one field.
    b) I've got no way of tying those actual Events back to their template parent - are you suggesting I add an EventTemplateId field to Events? It sounds a bit... self-referential to me. Maybe that's not a bad thing, but it's making my brain hurt and I've not started programming against it yet...

    Regarding the surrogate ID - yeah... it's mainly because I don't like tables without a nice autoincrement ID field. It just looks... wrong If it's not doing any actual harm, can I leave it in? Please?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    self-referential links are better than useless autoincrements

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by Spudhead
    Really? Just a boolean isTemplate field in Events? So there would be Event records in there that aren't really Events, in the sense that Users would never be linked to them?
    I thought the problem was precisely that you had some types of Event that could only be associated with ONE user. I guess this just shows how impossible it is to do effective design in an online forum. Once you go beyond the principles and get into specifics any answers you get can only be based on guesswork and assumptions.

    You've actually hit on a common gripe with SQL. The constraint support in SQL DBMSs (especially for referential constraints) is extremely limited. Does your DBMS support Assertions? An assertion is a way of creating more complex constraints on your data. They don't necessarily optimise well however.

    Quote Originally Posted by Spudhead
    Regarding the surrogate ID - yeah... it's mainly because I don't like tables without a nice autoincrement ID field. It just looks... wrong If it's not doing any actual harm, can I leave it in? Please?
    The important thing from the data integraity perspective is that uniqueness is enforced for the composite key. Adding an auto-incrementing key as well is just decoration and overhead.

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    re the pk. In order to ensure that records are not duplicated, you need to make a unique key based on the combination of the two foreign keys, and neither field should be null. Which is the exact definition of a primary key. There's no logical reason to maintain two primary keys for this table. If you want, you could remove the PK definition of the existing identity & leave the field in place, but, it would slow down inserts, and waste space.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  9. #9
    Join Date
    Jan 2002
    Posts
    189
    Umm... apologies for revisiting this, if anyone's still reading, but I have a related question. Well, it's the same schema.

    Greetings and Events are bugging me. Users do Events; you can only send a greeting to a User, you can't send one to an Event. That seems logical. But that would seem to suggest that my Greetings table should have an FK in the UsersEvents junction table, rather than both FKs for a UserId and an EventId. Although I'll need a UserId FK in Greetings anyway, because you can send a Greeting to a User, that's unrelated to an Event.

    I guess my question is... does it matter? Linking Greetings to UserEvents seems more accurate than just sticking an EventId in the Greetings table, but then I've got to go joining an extra table in if I want to find out the Event that a particular Greeting was for. And using that junction table as an FK like that... doesn't seem... right?

    Ugh. I should stick to JavaScript

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, spud, but if you're going to ask a question about some of your tables, you should post the latest version of those tables (don't expect us to remember what they look like)

    or were you just whining out loud?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2002
    Posts
    189
    I agree, and apologise. Hopefully the attached schema diagram will clarify.

    That diagram is what I have: Users can do Events, and can send each other Greetings.

    My question is: given that Users can also send each other Greetings in relation to a particular Event, should I link the Greetings table to the Events table or to the UsersEvents table?

    Giving a Greeting an EventId makes it simpler to find out the Event for a given Greeting. But it smacks of inaccuracy; you don't send a greeting to an event, you send it to the user. On the other hand, tying a Greeting to a UserEvent seems to me to conform better to theoretical ideals (such as I understand them, which is very little) but actually makes working with the thing just a little bit more complicated.

    Is there a rule? A best practice? Or is is simply a matter of taste and you're looking at it thinking "Just join the d*mn things. Who cares?"
    Attached Thumbnails Attached Thumbnails schema.png  

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Spudhead
    My question is: given that Users can also send each other Greetings in relation to a particular Event, should I link the Greetings table to the Events table or to the UsersEvents table?
    your use of the verb "link" is ambiguous -- it could mean a foreign key in one table, a foreign key in the other table, or the creation of a third many-to-many linking table to represent, respectively, a one-to-many, a many-to-one, or a many-to-many type of "link"

    my feeling is that since the greeting is in reference to a specific user event, the FK should be in the greetings table, referencing the PK of the userevents table

    unless, of course, you want to allow the ability of a single greeting to be in relation to multiple events

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2002
    Posts
    189
    No - greetings can only be in relation to one event, if any. Thanks, you've at least reassured me that I'm not going crazily wrong with this

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    As I understand it, and in terminology that I am not sure whether is standard or not, you have an "arc" relationship such that each Greeting must relate either to "just" a User, or to a UserEvent.

    This can usually be achieved in SQL databases like this:
    Code:
    create table greetings
      ( greeting_id integer primary key
      , userid references users not null
      , eventid integer
      , foreign key (userid, eventid) references userevents
      );
    So:
    - userid is mandatory and must always match a row from the Users table
    - eventid is optional, but if populated then the combination (userid, eventid) must match a row in userevents.

    Column userid is taking part in two foreign key constraints - unusual but valid.

  15. #15
    Join Date
    Jan 2002
    Posts
    189
    Ah. Well, that does describe exactly what should happen. But... well, I was sort of assuming I could get away with just having a nullable FK for UsersEvents in the Greetings table Is that bad? Like, crossing-the-streams bad?

Posting Permissions

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