Results 1 to 11 of 11

Thread: Database help!

  1. #1
    Join Date
    Mar 2007
    Posts
    6

    Question Database help!

    Hello to all you good people - I'm having a bit of trouble working out a suitable design for a system I am developing

    Here's the general idea of what I want to model:

    My system basically consists of users and events.

    Each user can be linked to an event in 2 ways - either by having subscibed to that event, or having created it.

    The way I have modeled this is as follows:

    I have an events table - containing a unique event ID
    I have a user table - containing a unique user ID
    I have a user_event table- containing a unique user_event ID and event id/user id as 2 foreign keys

    I have a user_event_creation table - with a unique ID and a user_event_id as a foreign key

    I have a user_event_subscription table - with a unique ID and user_event_id id as a foreign key

    Does this seem like the correct way to have designed the database? I am developing the system in ruby on rails by the way!

    Any help is appreciated!

    Cheers,

    Michael

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Got any DFD's you can upload - having a tired morning, struggling to visualise this atm.
    I think I get the problem and the first 3 tables appear to be correct.
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2007
    Posts
    6

    DFDs

    Sorry I don't actually, I'll knock one up and post it - thanks for the swift reply though!

  4. #4
    Join Date
    Mar 2007
    Posts
    6

    Question Design

    OK -it isn't so much a DFD as an outline of how my tables shall look.

    as you said - I am quite sure the link between users and events is sound, but I'm not so sure how to build a method of determing whether that link is subscription or creation based. I have attached the current database design which outlines how I have currently built the database.
    Attached Thumbnails Attached Thumbnails dbdesign.png  

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    If you only want to show if the link is subscription or creation based then how about adding a yes/no field to your userevents table?
    I.e. User_Events(ID, Users_Id, Events_Id, IsThisASubscription)
    George
    Home | Blog

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I assume an event can have more than one subscriber. Can an event have more than one creator?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Mar 2007
    Posts
    6
    nope, it can only have one creator

  8. #8
    Join Date
    Mar 2007
    Posts
    6
    Quote Originally Posted by georgev
    If you only want to show if the link is subscription or creation based then how about adding a yes/no field to your userevents table?
    I.e. User_Events(ID, Users_Id, Events_Id, IsThisASubscription)
    I shall try this method thanks - although I think I might have tried it before and there was a reason why I didnt implement it!

    Thanks for your help though!

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by michaelsterling
    nope, it can only have one creator
    Then you do not need a separate table for storing event creators. The creator is an attribute of event, and should be stored in the event table.

    Users (UserID, UserDetails...)
    Events (EventID, UserID, EventDetails...)
    Subscribers (UserID, EventID, SubscriptionDetails...)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Mar 2007
    Posts
    6

    cheers!

    Quote Originally Posted by blindman
    Then you do not need a separate table for storing event creators. The creator is an attribute of event, and should be stored in the event table.

    Users (UserID, UserDetails...)
    Events (EventID, UserID, EventDetails...)
    Subscribers (UserID, EventID, SubscriptionDetails...)
    you. are. a. genious.


    Thanks a lot!!

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Yes. I. Know.
    You. Are. Welcome.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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