 |  |  |  |  |  |  |  |
advice on a database design please
Please support our Database Design advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
 |
Hi all;
I’m a newbie here and it looks as if I might be able to get a bit of advice on what I think should be a fairly simple project that would actually have some value in my real world if I managed to get it working.
I have successfully set up a web site with a MySQL database and I can connect to it using some simple PHP code, so that part is out of the way. The challenge will be to build on it.
Here is the background. I would like to build a database to record attendance at a variety of activities that are organized into categories. The first name and last name of the person attending, the activity category such as Outdoor Sports, Cultural or Intellectual and the actual activity is really all that is required.
Examples of activities by category might be as follows:
Category = Outdoor Sports (Activities = Archery, Swimming, Tennis)
Category = Cultural (Art, Music, Drama)
Category = Intellectual (Lectures, Seminars, Discussion Groups)
The categories are fairly static, however the actual activities are somewhat dynamic in that new ones are regularly introduced and some are dropped from the program.
Reports that would be desired might be as follows:
• All activities attended by a person between two dates, or for the current or last month, etc.
• Persons who attended a particular activity.
• Persons who attended activities in a particular category.
• Count of attendance for a particular activity between two dates.
• Count of attendance for a particular category between two dates.
• And probably a whole lot of goodies that I haven’t thought about yet.
Some database maintenance requirements would be:
• Add, delete or modify a person (First name, Last Name)
• Add, delete or modify an activity, and possibly a category.
For the record, there could be a few hundred people in the program, possibly about 10 categories of activities, and approximately 50 unique activities. Occasionally an activity might be scheduled more than once on a particular day, so I will need to think about how to deal with that situation.
Currently entering the data is a tedious task, and I am thinking that I would like to set up a data entry form where the user could use drop down list boxes to select the person who attended with a mouse click, another drop down box for the category that would then offer a list of specific activities. It would be handy if the date that the activity could be entered by means of a calendar. I actually built something like this using VBA in Excel, so it will be an interesting challenge for me to see if I can make it happen in PHP.
So to the question on the design.
I’m thinking that I should have a “person” table that contains the first and last name of the person and a master_id field. Then a table that has the date and a master_id field that would be the same as and linked to the master_id in the person table. Then the same idea with the category table that would have a master_id field linking back to the “person” table’s master_id. And the same design for the activity table.
Table = person
Fields = id (index), master_id. first_name, last_name
Table = date_time
Fields – id (index), master_id, activity_date, activity_time
Table = category
Fields = id (index), master_id, activity_category
Table = activity
Fields = id (index), master_id, activity
I would like to have some confidence that I am on reasonably solid ground with this database design before I get to far along with this. This is the only design that I can come up with that I think will satisfy the reports that I would like to extract from the database. However I know very little about this, hence my pleas for some help.
Any advice or suggestions would be mighty welcome, and if you could point me to some PHP code and a MySQL database that might behave somewhat like I am after, it always helps my learning process.
I hope that this all makes sense.
Thanks for the time that you are taking to help me.
Sheila
I’m a newbie here and it looks as if I might be able to get a bit of advice on what I think should be a fairly simple project that would actually have some value in my real world if I managed to get it working.
I have successfully set up a web site with a MySQL database and I can connect to it using some simple PHP code, so that part is out of the way. The challenge will be to build on it.
Here is the background. I would like to build a database to record attendance at a variety of activities that are organized into categories. The first name and last name of the person attending, the activity category such as Outdoor Sports, Cultural or Intellectual and the actual activity is really all that is required.
Examples of activities by category might be as follows:
Category = Outdoor Sports (Activities = Archery, Swimming, Tennis)
Category = Cultural (Art, Music, Drama)
Category = Intellectual (Lectures, Seminars, Discussion Groups)
The categories are fairly static, however the actual activities are somewhat dynamic in that new ones are regularly introduced and some are dropped from the program.
Reports that would be desired might be as follows:
• All activities attended by a person between two dates, or for the current or last month, etc.
• Persons who attended a particular activity.
• Persons who attended activities in a particular category.
• Count of attendance for a particular activity between two dates.
• Count of attendance for a particular category between two dates.
• And probably a whole lot of goodies that I haven’t thought about yet.
Some database maintenance requirements would be:
• Add, delete or modify a person (First name, Last Name)
• Add, delete or modify an activity, and possibly a category.
For the record, there could be a few hundred people in the program, possibly about 10 categories of activities, and approximately 50 unique activities. Occasionally an activity might be scheduled more than once on a particular day, so I will need to think about how to deal with that situation.
Currently entering the data is a tedious task, and I am thinking that I would like to set up a data entry form where the user could use drop down list boxes to select the person who attended with a mouse click, another drop down box for the category that would then offer a list of specific activities. It would be handy if the date that the activity could be entered by means of a calendar. I actually built something like this using VBA in Excel, so it will be an interesting challenge for me to see if I can make it happen in PHP.
So to the question on the design.
I’m thinking that I should have a “person” table that contains the first and last name of the person and a master_id field. Then a table that has the date and a master_id field that would be the same as and linked to the master_id in the person table. Then the same idea with the category table that would have a master_id field linking back to the “person” table’s master_id. And the same design for the activity table.
Table = person
Fields = id (index), master_id. first_name, last_name
Table = date_time
Fields – id (index), master_id, activity_date, activity_time
Table = category
Fields = id (index), master_id, activity_category
Table = activity
Fields = id (index), master_id, activity
I would like to have some confidence that I am on reasonably solid ground with this database design before I get to far along with this. This is the only design that I can come up with that I think will satisfy the reports that I would like to extract from the database. However I know very little about this, hence my pleas for some help.
Any advice or suggestions would be mighty welcome, and if you could point me to some PHP code and a MySQL database that might behave somewhat like I am after, it always helps my learning process.
I hope that this all makes sense.
Thanks for the time that you are taking to help me.
Sheila
•
•
Join Date: Jan 2009
Posts: 1,396
Reputation: 
Solved Threads: 170
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 ...
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 ...
Last edited by almostbob; Aug 27th, 2009 at 1:29 am.
Failure is not an option It's included free, you don't have to do anything to get it
If at first you dont succeed, join the club
Of course its always in the last place you look, you dont keep looking after you find it
Please mark solved problems, solved
If at first you dont succeed, join the club
Of course its always in the last place you look, you dont keep looking after you find it
Please mark solved problems, solved
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.
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
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:
master-id = mysql_inseret_id ( );
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.
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 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:
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 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 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.
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
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
CREATE TABLE IF NOT EXISTS person
(
id INT(11) NOT NULL AUTO_INCREMENT,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
status ENUM('Current','Lapsed') NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS attendance
(
id INT(11) NOT NULL AUTO_INCREMENT,
session_id INT(11) NOT NULL,
person_id INT(11) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (person_id) REFERENCES person(id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (session_id) REFERENCES session(id) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS session
(
id INT(11) NOT NULL AUTO_INCREMENT,
activity_id INT(11) NOT NULL,
date date NOT NULL,
time time NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (activity_id) REFERENCES activity(id) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS activity
(
id INT(11) NOT NULL AUTO_INCREMENT,
actiivity_name VARCHAR(30) NOT NULL,
category_id INT(11) NOT NULL,
notes VARCHAR(500) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (category_id) REFERENCES category(id) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS category
(
id INT(11) NOT NULL AUTO_INCREMENT,
category_name VARCHAR(30) NOT NULL,
PRIMARY KEY (id)
);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
|
Similar Threads
- Database Design Questions (Database Design)
- Help with database design? (MySQL)
- Database Design for Dating Site (Database Design)
- database design advice (Database Design)
- Browser based game, database design (Database Design)
- Database design - subtypes and instances of an entity (Database Design)
- Database Design - Supertypes and Subtypes (Database Design)
- Database Design Advice (MySQL)
Other Threads in the Database Design Forum
- Previous Thread: Complex Database Searching...........
- Next Thread: online library management system
Views: 2147 | Replies: 21
| Thread Tools | Search this Thread |
Latest Database Design Posts
- Today's Posts
- Unanswered Threads
Related Forum Features
Tag cloud for Database Design
