Database Design 1

[SPrelewicz]

I have 3 relevant tables. The first is a content table that lists all content available on our site(About 700 entries). the second is an affiliation table which lists all our affiliate institutions (About 30 entries). The third is a content table of contents which matches institutions with which content they are allowed to get.

My question--Most institutions get just about all content. Is it bad form to have that table of contents table hold which content affiliations DONT receive, as all reveive more than not. This would allow for a much smaller table. I'm not sure why this would be bad form, but I'm making sure since I'm learning as I'm going. Thanks

Scott

 

[sleipnir214]

I would leave it the way it is.

I don't have any logic for my recommendation, it just seems a bad idea to use the database to record negative definitions. It just seems to me you are more likely to need to answer the question, "Who's subscribed to Foo?" than "Who's not subscribed to Foo?"

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[SPrelewicz]

Okay, cool thanks. Now, however, I have more questions. I still have those tables. Again, they are

1.) Content table listing all available content (content_id)

2.) Affiliation table listing all our affiliations (affil_id)

3.) Content Table of Contents matching affiliation with subscribed content (affil_id, content_id)

I want to have an admin page where our administrators can check or uncheck content to subscribe or unsubscribe a user. How can I do this? I can present all the content and have subscribed checked (I'm using Perl btw). However, if an admin say unchecks 5 journals or so, how do I delete these from the db, since they are not passed as params? Should I have a separate table for each institution and after a submission--

1) Empty the table
2) Fill it back in with all checked content

Or is there another way with one table?

Thanks again!

Scott

 

[sleipnir214]

What is your interface?

What I might do with an HTML interface is produce a set of checkboxes with all the possible content, and check by default all those which represent content to which the user is subscribed.

I would also place a set of related hidden HTML form elements in the form to record what which checkboxes were already selected checkboxes at the time the page was produced.

When the form is submitted, another script can scroll through the checkboxes that are selected at the time the form is submitted. For each checkbox submitted, your script can compare the value as it is with the hidden value as it was.

If a checkbox is checked, but there is no related hidden field, your script can assume that the content was previously unsubscribed,and subscribe your user.

If a checkbox is not returned it is because it was not checked. You will have to loop through the hidden fields (those checkboxes already checked) to see which ones were checked previously, but are now not checked. Unsubscribe your user.

If there is a submitted checkbox and a related hidden field both, then the user was already subscribed and elects to remain subscribed. Do nothing.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[SPrelewicz]

Great solution. Yes, it is an HTML interface, and that's exactly what I'll do. If I have problems with that, I'll take it over to the perl forum

thanks for your time,

Scott