Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2010
    Location
    UK
    Posts
    2

    Help with Table Design

    Hello,

    I am fairly new to DB design, and have come across an issue with my first "live" DB.

    I will try to explain this in the best way I can...

    The DB holds temperature readings from remote temperature probes. I have 6 sites, and 6 departments on each site.

    Staff that use the front end web site will be presented with a page that allows them to "subscribe" to receive email alerts for temperature readings for particular departments on particular sites. They may want one or two departments readings for one or two sites or they may want more, meaning that there is a large number of combinations of options they may choose.

    I assume one option would be to have a column for each site/deparment combination and have "Y/N" ie

    UserId
    Site1Dept1 Y
    Site1Dept2 N
    .
    .
    Site2Dept1 Y
    Site2Dept2 Y
    .
    .

    But this doesn't seem very neat or efficient. Is there a better way to do this?

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    from your explanation, it would appear that a department belongs to a site

    so a user subscribes to a department, and not to a department/site, because the department determines which site it is (unless the same department can belong to more than one site

    so you would record which users have subscribed to which departments

    the table would have two columns:

    user1 dept4
    user1 dept6
    user1 dept9
    user2 dept9
    user3 dept3
    user3 dept7
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2010
    Location
    New Zealand
    Posts
    15
    If you're new to databases then there are quite a few concepts to get your head around before diving into this. There are many ways you can achieve what you're trying to do. I made a diagram to show you how I would build the database for your system: http://img191.imageshack.us/img191/3235/exampleg.jpg

    Each department belongs to a site. You can see that the > indicates that a site can have many departments, and the | indicates that a department can only belong to one site.
    The user table stores the information for users who log into the website.
    When one of your users subscribes to a department, they can specify the frequency of their emails on the website. Their subscription would be stored in the subscription table. You can see that a user can have many subscriptions (as indicated by the >) but a subscription can only belong to one user.
    Each subscription links to a department. Again, one subscription can only be for one department, but a department can be subscribed to many times by many users.

    That's about all there is to it on the database end of things. To manage the frequency of subscriptions you would need a script to run every XX minutes/hours that looks at all the records in the subscription table and depending on their "frequency" will send out a temperature reading for the department that the subscription is linked to. The "frequency" value might be simple words like "daily", "weekly", or you might have codes like 01, 02, ...

    I hope somewhere in all that babbling you find some useful information! Please let me know if you need me to expand on or explain anything.

  4. #4
    Join Date
    Jun 2010
    Location
    UK
    Posts
    2

    Thumbs up

    Quote Originally Posted by ProphetX View Post
    If you're new to databases then there are quite a few concepts to get your head around before diving into this. There are many ways you can achieve what you're trying to do. I made a diagram to show you how I would build the database for your system: http://img191.imageshack.us/img191/3235/exampleg.jpg

    Each department belongs to a site. You can see that the > indicates that a site can have many departments, and the | indicates that a department can only belong to one site.
    The user table stores the information for users who log into the website.
    When one of your users subscribes to a department, they can specify the frequency of their emails on the website. Their subscription would be stored in the subscription table. You can see that a user can have many subscriptions (as indicated by the >) but a subscription can only belong to one user.
    Each subscription links to a department. Again, one subscription can only be for one department, but a department can be subscribed to many times by many users.

    That's about all there is to it on the database end of things. To manage the frequency of subscriptions you would need a script to run every XX minutes/hours that looks at all the records in the subscription table and depending on their "frequency" will send out a temperature reading for the department that the subscription is linked to. The "frequency" value might be simple words like "daily", "weekly", or you might have codes like 01, 02, ...

    I hope somewhere in all that babbling you find some useful information! Please let me know if you need me to expand on or explain anything.
    At first read I wasn't sure, but know I understand - having multiple rows in the subscription table that are associated with the same user, each with different departments. I can then run a query on the subscription table that selects department where user_id = the user I am looking for.

    Thanks for your help!

  5. #5
    Join Date
    Jun 2010
    Location
    New Zealand
    Posts
    15
    Quote Originally Posted by min1me View Post
    At first read I wasn't sure, but know I understand - having multiple rows in the subscription table that are associated with the same user, each with different departments. I can then run a query on the subscription table that selects department where user_id = the user I am looking for.

    Thanks for your help!
    That's exactly right. Good luck with your project!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just be careful ~not~ to implement the unnecessary ids in prophetx's data model
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2010
    Location
    New Zealand
    Posts
    15
    Quote Originally Posted by r937 View Post
    just be careful ~not~ to implement the unnecessary ids in prophetx's data model
    It's a bit difficult to decide whether they are necessary or not until we see some data. Sometimes a surrogate key is the only practical option.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Knowing Rudy as I do I am pretty sure he is referring specifically to the surrogate on subscription. Unless that table relates to another then it serves no purpose.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    Knowing Rudy as I do I am pretty sure he is referring specifically to the surrogate on subscription.
    well spotted

    Quote Originally Posted by pootle flump View Post
    Unless that table relates to another then it serves no purpose.
    i think you meant "unless some other table refers to it" because the subscription table actually does refer to two tables, the users and the departments

    the surrogate id on the subscriptions table is useless
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937 View Post
    i think you meant "unless some other table refers to it"
    well spotted

  11. #11
    Join Date
    Jun 2010
    Location
    New Zealand
    Posts
    15
    Sorry to hijack this thread, but while we're on the subject... From what you're saying, in this example transfer_item and invoice_item should not have id fields because no other tables are referring to them? Instead should the primary key be a combination of those two foreign keys? Thanks :P

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ProphetX View Post
    Instead should the primary key be a combination of those two foreign keys?
    that is correct
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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