CodingForums.com - Creating a Database

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - Creating a Database (http://www.codingforums.com/showthread.php?t=280991)

Creating a Database

I have started voluntary work for a charity who would like me to create a database for them which records the number of volunteers who come in, why they have came in and what they done.

I have used databases before but never set one up so I was wondering what software to use and for advice on how to create it?

Thanks

Maybe you need some drag-and-drop solution, like mytaskhelper?

Drag and drop will only be useful if you know how to design the database schema in the first place. And if you can do that, issuing a create table in any dbms is a breeze anyway, so a drag and drop solution would become more or less irrelevant. Since you are asking the question, I'll assume you don't have previous design experience.
This is not something that can be explained in a single post. I did find a crash course online article though, that with a quick scan over looks like it covers the basics including why normalization is important. You can see that here: http://www.informit.com/articles/art...02167&seqNum=2
You can search for additional with a simple query of "database design principles". Storage design is the single most important step during the design phase. If it don't work on paper it won't work in practice. Proper design is the difference between impossible/horrendously difficult queries, and easily managed queries.

A question for you, Charlie: How will the date be ENTERED into this database? That is, who will be recording all the volunteers names, activities, etc?

If one person is going to be doing all of it--if you don't need a web page where each volunteer does it for himself/herself--then maybe you don't really need a database. Maybe just an Excel spreadsheet will be sufficient.

If you don't have experience creating integrated websites (that is database integrated with web server code), you may be biting off way more than you are ready to chew. Maybe a simple spreadsheet is a more practical solution. And even if it's not the best final solution, it might be used as an interim answer.

Thanks for replying, I will look into Database Design Principles.

An excel spreadsheet could be enough in the mean time but a database would be better, the charity have all of their information stored in filling cabinets atm and want everything organized on the computer, it will not be just information about the volunteers.

The charity has quite a few voluntary administrators so there will probably be a few different people entering information, they would like to be able to access information quickly so that if somebody questions the charity they will be able to easily answer instead of searching through piles of information.

For example if someone asks...
How many new members do you have?
Who visited the charity this week?
What volunteers are in on a Wednesday?

They can find the information at the click of a button.

The only experience I have with PHP and MySQL came from creating a website in university with a user log in, so not much.

Well, to get this started, I think you want to go back to your university days and, indeed, create a user login. For the administrators, if nobody else.

You could probably combine that with the tracking stuff.

Maybe this:

Code:

CREATE TABLE volunteers (

    volid INT AUTO_INCREMENT PRIMARY KEY,

    loginName VARCHAR(30),

    firstName VARCHAR(30),

    lastName VARCHAR(30),

    loginPassword VARCHAR(20),

    isAdministrator BOOLEAN,

    isSupervisor BOOLEAN,

    isXXX BOOLEAN,

    isYYY BOOLEAN

);

isXXX and isYYY are not real... just placeholders for other possible levels of responsibility you might want to assign. You could also (or instead) have a numeric adminLevel field. All depends on how you want to control access to various web pages and/or data in the DB.

And then, for the project you metioned:

Code:

CREATE TABLE activities (

    actid INT AUTO_INCREMENT PRIMARY KEY,

    activity VARCHAR( 200 ),

    ... any other fields to describe the activity ...

    ... maybe even something like ...

    requiresAdministrator BOOLEAN, /* only admins can do this activity when true */

    requiresSupervisor BOOLEAN, /* only supervisors when true */

    ...

);

    

CREATE TABLE volunteerActivities (

    volid INT,

    actid INT,

    startTime DATETIME,

    endTime DATETIME,

    reason VARCHAR(1000),

    ... other fields to describe what volunteer did ...

    CONSTRAINT FOREIGN KEY volid REFERENCES volunteers(volid),

    CONSTRAINT FOREIGN KEY actid REFERENCES activities(actid)

);

So your volunteerActivites table is actually a MANY-TO-MANY table (look it up if you aren't familiar with the term).

And now you have a design that will support a myriad of interesting queries.