E-mail documents page design 1

[lanm]

I'm new to PHP, and am assigned a project to allow users to click checkboxes for which documents they want e-mailed to them monthly.

There are five documents they can choose from, and I was thinking the database would be 6 columns:
1. E-mail address as PK
2. 5 other columns representing the 5 documents as a bit type

What I don't know is how to start with getting a SPROC on mySQL for scheduled e-mailing, or if this is even available.

We've chosen a hosting site, so I'm wondering if there's anything I need to check with them first to see if this is possible.

Any suggestions are welcome!

 

[sleipnir214]

I certainly wouldn't use that database schema. What happens, for example, if you decide to offer a sixth document? Such a change should not require a change to the structure of the database.

If I were doing it, I would have three tables. The first table records email addresses and will have, at minimum an unsigned integer primary key and an email column. The second table records what documents are available and will consist at minimum of an unsigned integer primary key and some kind of a reference to a document. The third table will contain two unsigned integers -- a record here means that user with ID foo is subscribed to document with ID bar.


Second, it should not be MySQL that performs the emailing. It should be a script. Since you posted this question in the PHP forum, I'll recommend that that script be in PHP. Some mechanism on the system will automatically run the script at regular intervals -- and the script will send the emails. Of course, the script could make use of MySQL stored procedures if you like, or perhaps even views. But even if MySQL is capable of sending the emails, it should not.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[lanm]

Thanks sleipnir214!

I was thinking of using the e-mail address since it's already there, and we don't want duplicates. But now, I see that using that PK in the first table would allow the join to the third table. It'd be easier to join the two tables via this integer rather than the e-mail address. I guess I could do some type of check to avoid duplicates, or maybe even a unique index.

Any more suggestions are welcome! I appreciate your tips!

 

[lanm]

Hi sleipnir214!

I was thinking about the schema after your suggestion, and came up with the following to make sure I understand and am getting this right:

A three table design:
Parent table:
1. PK integer
2. string column with e-mail address

Child table [Document]:
1. PK integer
2. Document stored in this column

Child table [DocSelected]...or something like this:
1. PK integer / FK for linking to parent table PK
2. PK integer / FK for linking to chilc table [Document]

I'm thinking this way if there are more documents added, I only have to udpate the [Document] table

Should I use [DocSelected] as a join table?

Thanks....any suggestions are welcome!

 

[sleipnir214]

I'm not sure what you mean by "join table". If you mean "a table which records relationships between records in two other tables", then DocSelected is already a join table.

But you are correct. Changes to documents will only entail recording them in the Document table.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[lanm]

Sleipnir214!

Yes, the flow would be:
[ParentTable] -- [DocSelected] -- [Document]

Thanks for your help on this! Much better than with what I started!