Basic design question

[bigal.nz]

Hello,



A bit of a newbie question here about design.

I have inherited a flat database with one table (tbl_main). This table contains a record of events (Event_ID unique number) and each event has a date, time, person, address and a few other personal details.

Each record on the table should be emailed to one of two different organisations.

I am thinking I would have two buttons on the main screen "email to organisation 1" and "email to organisation 2" each time either of these buttons are clicked a report checks for all the records not previously emailed to that organisation and they are emailed.

To keep track of which records have been emailed I was thinking I would create "tbl_emailed" which would have:

Event_ID, Emailed_Date_Org1, Emailed_Date_Org2

I assume that every time I add a record to tbl_main it would need to insert any new events to tbl_emailed and then when the buttons are clicked it would need to query tbl_emailed

Is this the correct approach?

Cheers in advance

-Al

[pbaldy]

Let's clarify, as this to me indicates each record will go to one organization or the other, not both:

Each record on the table should be emailed to one of two different organisations

but your design would indicate it ends up going to both. if it's just one, then perhaps a field in the event table indicating which one, and a date field to indicate it was done.

[bigal.nz]

Let's clarify, as this to me indicates each record will go to one organization or the other, not both:

Each record on the table should be emailed to one of two different organisations

but your design would indicate it ends up going to both. if it's just one, then perhaps a field in the event table indicating which one, and a date field to indicate it was done.

Hi,

No each record will go to separate organizations.

Cheers

-Al

[pbaldy]

Then I'd just add the 2 fields to the event table.

[Micron]

If possible, I'd just add a field to the main table and flag it as emailed. I didn't read anything in your posts that suggest you want to know which emails went where, just that they were emailed. The table is already not normalized and I doubt you want to worry about that, so adding another field to the main table should be no big deal. You would not have to worry about synchronizing two tables either.

[pbaldy]

I didn't read anything in your posts that suggest you want to know which emails went where, just that they were emailed.

How are you handling which organization to email the record to?

Each record on the table should be emailed to one of two different organisations

[bigal.nz]

Hi Guys,

Thanks for the replies. Yes putting it all in one table is the simplest option, and I only considered using another table because my main event table is beocoming rather large!

The actual email address is handled by the user, but they will know whether they hit the button for Org1 or Org2 and the relevant field in the database will record it was emailed.

I have a slightly different question about the same database but perhaps better handled in a different thread?

Regards,

-Al

[John_sc]

It is best to design your database such that fields are placed in the table that they are related. It is not generally good practice to split tables up just to reduce the number of columns in the table.

If might be a better idea to have a separate table with the different corporate email addresses so that the attendees aren't required to know and enter the different email addresses.