My only answer, dont use any of the sql date formats, text dates are tedious to manipulate, they have to be converted to numeric for any operation which takes al lot of time and processing
a timestamp takes 10bytes stores date and time in one column and can be mined for any part of the date and time, and need only be converted once on input
a text date time is
january 1 2009 10:45.22am (25 bytes) not a lot on 1 row, but a lot on a million rows
speed test,
examine 10000 text records of dates
to find dates between December 1 2008 at 12:15pm and 1 January 2009
echo $date;
or
examine 10000 TIMESTAMPs >1228133700 and <1230768000
echo date(d m y, $timestamp)

bit over passionate 'bout this ...

I don't see a need for the master_id. Could you explain it further? The problem I have, for example, is that the date and time of an activity is not related to the people.

Here is the design that comes to my mind:
person: id (primary key), first_name, last_name
category: id (primary key), category_name
activity: id (primary key), category_id (foreign key), activity_name
session: id (primary key), activity_id (foreign key), date, time
attendance: id (primary key), session_id (foreign key)

This design allows you to track each category, which activities are in each category, what times each session of the activity is at and who attended each session of the activity.

Thanks for the advice timothybard. The master-id thing came from a PHP MySQL database example for an address book that I found in Sam’s Teach Yourself PHP, MySQL and Apache, which seemed to have some elements that behaved somewhat like I am looking for in this project. Clearly I don’t know much about this stuff however I have six books on the table here beside me as I try to pick up some knowledge on the subject.

I think that the way that the address book PHP works is to insert the name of the person into the table and then extract it again using a piece of code that looks like this:
which is then used in other tables. What I am getting from your advice is that I don’t really need this field but instead can rely on the foreign key to make the associations.

I confess that I don’t fully understand your suggested design using a session, so I will ramble on in the direction that I was headed and maybe you will be able to extract enough information to get me on track.

I did consider using something like a “session” or an “activity event” as the core to all of this and I’m thinking that this is what you are pointing me toward. I opted to see if I couldn’t make it work with just the 4 tables. As stated this project is simply something that has the potential to improve significantly on a spreadsheet application that is currently used, and my interest is more in gathering some knowledge about databases and PHP (or something similar) than coming up with the most eloquent solution, so I am delighted to try a few things that might contribute to my education. And I have the luxury of lots of time. It is always more interesting for me to work on something that has a direct application in my world than exclusively textbook examples.

On further reflection, I decided to add two more fields. The first would be a field called “status” in the “person” table that would serve to indicate whether the member was current or lapsed. The other additional field would be a “comments” field that would live in the “activities” table. I’m not sure if this would actually get a lot of use, however it just seems like a good idea at the time.

Considering the relationship between the tables, here is what I came up with;

A person can participate in many activities and any activity could be attended by many persons. Therefore it is a many-to-many relationship requiring an association table.

A “category” can have many activities, however an activity can only belong to one category, hence a one to many relationship.

The “date and time” issue does perplex me. I am going with the idea that a date may include many activities and that an activity may be repeated on many dates so a many-to-many relationship with an association table.

Here is what the table and field design might look like then:

person: - id (primary key), master_id, first_name, last_name, status
category: - id (primary key), master_id (foreign key), category_name
activity: - id (primary key), master_id (foreign key), activity_name, comments
date_time: - id (primary key), master_id (foreign key), date, time


On to what I gather is the next phase or normalization. My take on it is that the 1st normal form conditions are satisfied. As for the 2nd normal form, this is getting a little blurry, but I think that the above complies as does the 3rd normal form, which isn’t quite so blurry.

So to summarize, I would be happy to use your ideas, since they are undoubtedly much better than mine, given the distinct lack of exposure to the subject, but it does beg the question “Would what I have got going here actually work, albeit crudely?” or would it flop.

My thanks also to “almostbob” for his comments. Performance isn’t really an issue for this particular application. There will not be a huge number of records in the database for a long while, and the number of users will be limited to a handful of interested parties who will simply print off reports. That brings up another subject, but at the pace that I am going, it could be a while before I get to that.

Thanks to all,

Sheila

The activity table that I suggested is simply the date_time table you had but with the addition of the activity id to track for which activity the date and time was for.

You mention that there is a many-to-many relationship between people and activity and a one-to-many relationship between category to activity. I agree with those two relationships; however, I do not see how they are implemented in your design. For example, in your design, I do not see how you track which activities are in each category.

Thanks again for your help. Evidently this is the place where I fall flat on my face. I guess that my thinking was that a foreign key in the activities table linked to the categories table would accomplish the many-to-one relationship between the two.

I am clearly in over my head at this point, and am not even sure of the questions that I need to ask to clarify this. A good reason to try to come up with a pencil and paper design before I try to implement it.

This may not be a question for this forum, however since it is relevant I will pose it anyway and risk the ire of the purists. In MS Access I know that the associations are established through a graphical interface where the user connects the tables and keys by dragging connecting lines that indicate the various associations. I am planning on parking this is a MySQL environment and I haven’t been able to figure out how the relationships are done using it.

As well I believe that I will need an association table to establish the many-to-many situations and I am wondering how to set this up too.

Just thinking out loud here and illustrating how little I know about this yet, so your help is greatly appreciated, and I apologize for being so dense.

So with your proposed design copied here:

person: id (primary key), first_name, last_name
category: id (primary key), category_name
activity: id (primary key), category_id (foreign key), activity_name
session: id (primary key), activity_id (foreign key), date, time
attendance: id (primary key), session_id (foreign key)

The relationships would be:

A person can attend many activities and activities could be attended by many persons. (many-to-many)
A category can have several activities but an activity can only belong to one category (one-to-many)
A session could be attended by many persons and many persons could attend a session (many-to-many)
An attendance would be associated with only one person and only one person would be associated with an attendance (one-to-one)

Have I got this right? Do I seem confused?

Thanks a bunch.

Sheila

I have taken timothybard’s design and attempted to render it into an entity relationship diagram. If someone who understands where he was going with this could be kind and let me know if the drawing incorporates what he had had in mind I would be very grateful. I suspect that I am a long way from getting this right yet.

(It seems that you have to click on the image to view it in the original size.)

As I understand it, the attendance table is simply a starting point to represent the fact that a person attended a particular activity that belonged to a particular category on a date at a time. It is a vehicle to organize the tables so that the user could construct any type of report combination.

Some of the reports that I can envision are:

• List of active persons (or list of all persons active and lapsed) between two dates
• List of all activities and their date and time ordered by category between two dates
• List of all categories
• List of all of the activities that were attended by a particular person between two dates
• List of all of the categories that were attended by a particular person between two dates
• Number of activities that were attended by a particular person between two dates
• List of all the persons who attended a particular activity on a particular date
• Number of persons who attended a particular activity on a particular date.

I’m thinking that if I can come up with these reports, I should be able to get at anything.

I have been trying to do enough research to find out how to set up the entity relationships in MySQL and I am arriving at the conclusion that there is no equivalent of the graphic interface as is found in MS Access and that it is accomplished through the SQL query language. This must get quite fancy! The ISP where this will be hosted while I gain my education offers C-Panel and phpMyAdmin, but I have found no helpful shortcuts there to setting relationships. So some guidance on this theme would be really valuable as well.

I think that I should be OK with figuring out how to implement the associative tables, but it would be nice to have some confirmation that the relationships are proper before I wade in deeper.

Thanks for any help in furthering my education.

Sheila

It looks like you have been busy and made good progress. Before I go in to anything else, I noticed that the design I suggested is missing one crucial field.
The attendance table should have three fields: (1) Id, (2) session_id and (3) person_id
The design I posted originally was missing the person_id.

You mention the relationships tool in MS Access. MySQL does have features to handle the same function. Here is a link to a page showing how to define relationships in MySQL via phpMyAdmin. Please note that the primary purpose of defining the relationships is for referential integrity; at a minimum, defining the relationships with these tools is not necessary.

As far as the drawing you put together... you show a relationship between the category table and the attendance table; that relationship should not be there. Here are all the relationships you should have:
1) category to activity, one-to-many
2) activity to session, one-to-many
3) session to attendance, one-to-many
4) attendance to person, many-to-one

Notice that I don't mention any many-to-many relationships. When creating databases, many-to-many relationship are created by using an intermediate table and has two many-to-one relationships. For example, there is a many-to-many relationship between session and person; each person can attend many sessions and each sessions can have many people. However, when creating the database, I we used an attendance table as the intermediate table and created two many-to-one relationships from it to the session and person tables.

Lastly, if you notice that I don't respond to your posts, please send me a private message. I always try to respond to posts that are within threads I've posted on previously, but some times I overlook new posts.

Once again thank you timothybard. It looks like you may have taken on my education all on your own. I hope that you can stick with me a while longer.

There is a lot that I still don’t understand, however I am encouraged by your help. I think that it may actually be coming together for me. And of course I am still chewing through a few books on the subject, but still only know enough to be dangerous.

I did another entity relationship chart based on your last advice and re-organized it somewhat to park the attendance table between the person table and the session table so that visually for me it looks like an associative (intermediate) table.



One issue that is unresolved in my mind is the relationship between the session and the activity. It is true that an activity could have many sessions (or start times) however it is also true that session could have many activities that start at the same time. I’m not sure if this idea is accommodated in what we have so far, or even if it matters.

I decided on this project because it seemed to have enough complexity that I would learn a lot from undertaking it, however on the other side I figured “How difficult can it be?” Four items to track and fairly straightforward and clear relationships. Shouldn’t be too tough. But as we work through this I am discovering a lot of nuances that impact the design, however I think that when I am done, I should have the confidence to take on something a bit heavier.

I am hoping that when I move on to trying to create an SQL query to yield the kind of reports that I listed above, it will become clearer to me how the relationships work.

I didn’t find the link that you mentioned in your last post leading to the method to accommodate the relationship assignment using phpMyAdmin and would like to take a look at it please. I have spent hours searching for something that might enlighten me on the subject but failed come up with anything meaningful to me.

I take it from your comments that this part is not crucial to the ability to generate SQL queries anyway, and that whatever is necessary is somehow accommodated in the SQL language.

I am assuming that the next step will be to set up the tables in MySQL and then to create and test a few queries.

Then it is on to part “B” to try to figure out some PHP code to pull it all together.

Thanks again.

Sheila

After searching a bit more I discovered an example that might may lead me to addressing the relationship matter in MySQL, so I thought that I might take a run at creating the SQL code to create the tables based on the new entity relationship diagram that I posted in the thread above.

I am presuming that I can just ignore the things like TYPE=INNODB or MyISAM items and the CHARSET=utf8 parameters that I discovered in the example and that the table creation will default to something that will work for me.

I need to do a little research on the "ON UPDATE CASCADE ON DELETE CASCADE" thing to find out what that ia all about or if I need to include it at all.

I went on to load this into the SQL panel in phpMyAdmin and was successful in creating the tables.

The issue that I would appreciate some confirmation is that I have the foreign keys correct in the "attendance", "session", and "activity" tables.

Did I manage to get it right?

Thanks again,

Sheila