Topic: Archive Development

schmidt408    -- 11-04-2007 @ 7:48 PM
  I think I might have dug myself in a hole here, so could use some advice on database design.

Currently, I have a table that records meeting minutes. The design is:

minutesid | int | 4 | no nulls
minutescat | varchar | 255 | allow nulls
minutesnotes | text | 16 | allow nulls

What this does is set up a minutes category, such as "Members Present", and the notes for that category, such as "Mrs. Peacock, Mr. Green, Professor Plum, Colonel Mustard, Miss Scarlett". Each individual category has it's own ID.

I need to develop an archive of each of the minutes. So if there are 15 categories and associated notes for the 15 OCT 2007 meeting, how can I design the archive table to include all of those categories and notes into one record for 15 OCT 2007, then for 15 NOV 2007 and so on ...?

Hope that makes sense. Thanks in advance ...

Much appreciated

megan    -- 11-04-2007 @ 9:08 PM
  I'm not sure I really understand what you just said ;P but I'll give this a try

If you go the best "normalization" route you would need a second table, say tblMinutesDates with 2 fields, minutesDateID (pk/primary key) and minutesDate (datefield)

then you would need a third table that would join the first 2 - it would not need a primary key, just 2 foreign keys, the minutesDateID and the minutesID - one row for each cat for a certain date.

then when you do your selects etc, you would have to do  joins of the tables to get correct rows - does that make sense? do you know how to do joins in your sql statements ??

hth ~megan

ps - when you do your inserts, you would have to be sure and insert appropriately into each table etc.

We have art so that we shall not die of reality ~ Nietzsche

This message was edited by megan on 11-4-07 @ 9:09 PM

schmidt408    -- 11-07-2007 @ 3:11 PM
  I figured I wasn't being too clear .. sorry for that.

I think I know what you are proposing and I'll give that a shot. I'm pretty new at database design and writing SQL statements (as if you couldn't tell:)), but I understand the use of JOINs and keys.

You'll probably be hearing from me again soon ... just wish this day job didn't interfere with my side work - hehe.

Much appreciated

EasyCFM.COM ColdFusion Forums :