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!

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

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!

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

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.

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.

well spotted

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

well spotted

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

that is correct