Database Structure Question

[eluser]TerryT[/eluser]
Just started with CodeIgniter. Have a question related to an app I want to start with to learn the framework. I want to do a meeting minute app in CodeIgniter. I am pretty sure I need a users table, a meeting minutes item table and I think a meeting type table.

Our company has three types of standard meetings (safety, progress, issues). I want to be able to sequentially number the meeting minute items in each of the three meetings. For example, after a few weeks the safety meetings may be up to item 25, while the progress meeting might be to 150. I would also want the administrator to be able to print a report of all outstanding items across the 3 meetings.

How would you set this up? I thought of one table of all the meeting minute items with its own key field and a field relating it to the meeting type table. I am not sure of an easy way to get sequential items for each of the 3 meetings without having a separate field and looking for the last number used and updating that field.

Any suggestions would be appreciated. Thanks

Terry

[eluser]jmadsen[/eluser]
Hi Terry,

Still on my first cup of coffee, so maybe that's it :-) , but I'm not sure I see what your problem is.

Yes, set up the tables the way described. I would add datecreated field, as well, then you know when the meetings happened and can always sort them in sequential order. Alternatively, your primary key (assuming you use an autoincrement value) will tell you what order they were created in.

If you need meetings from a single type with id's of 1,3,5,6,9,12 to show up as 1,2,3,4,5,6,7, do that in your output. I can't think of any reason why you would want to save those in the db.

Did I answer your question, or completely miss the mark?

[eluser]TerryT[/eluser]
There would be three separate people taking the minutes for the three meetings. The minutes would also include a field for who is responsible for the item, when they promised to complete it and when it was actually done. Therefore there is a need to be able to search on a meeting minute item to be able to update it.

If I change the numbers during the output, I wouldn't be able to run a query on them. And if the numbers are not sequential within each separate meeting, someone will ask, "How come #24 is missing? Was it deleted?".

I hate to do three separate tables for 3 sets of meetings since the fields are the same. Then what happens when there are 8 types of meetings? 8 tables?

Terry

[eluser]jmadsen[/eluser]
Just store your actual id in a hidden field/etc, and use it to search on while you display a sequential number, like how a hyperlink works. I assume "run a query on them" mean from the same page.

Alternatively, make a "code" that displays nicely for your users but also tells you the id info you need. (i.e., "SAF-012-001" is meeting 1 to them, but you know it is id 12 and "SAF" just makes it look sophisticated :-) ).

If you REALLY have to search on id, only display 1,2,3..., but don't want to store it in a field, I could write you a query that searched on "the 5th safety meeting", but I'm not very inclined to and recommend very strongly against it. You'll hose your data at some point.

If you want to do updates to your data, you need to have a unique key of some sort. which means you have store that unique key somehow. Your question seems to boil down to "How do I get around that rule?", but you can't.

[eluser]InsiteFX[/eluser]
I would add a table for groups which would hold the meeting types
safety, progress and issues. This way you can later add new meeting
groups to your meetings.

InsiteFX