help me visualize my database

Anon

heres my problem.. i'm making this website for my dad.. he runs a consulting thingy.. i'm having trouble visualzing how i should structure my mysql databases and i was hoping you could help me out really quick..

in this company, there are 100 employees. all of the employees teach at least one class, a few employees teach more than one class (maybe two or three).. and some employees teach the same classes as others because there is high demand for that specific training course. (e.g., two people may both teach class XYZ).

The classes are divided into different sub-sections.. for instance, math, science, english.. as an example.

each employee will have his/her seperate "home page.. with bio.. resume, etc." I want to structure it so that users can post comments on individual employees and also view previous comments that other customers have left. In addition I would like to have a unique page for each class.. although more than one employee may teach the same class.. also i would like to have user feedback on each class.

Of course, I want to make the site fully searchable by either employee name or class ID #.. and also have the ability to browse.. (i.e. view all class headings (math, science, etc) and be able to select a course # from there.

I'm having trouble visualizing how a database should be structured, so if you could give me any pointers on that, it'd be much appreciated.. i know it might be a b!tch to think about.. thanks in advance..
wallace

Anon

Um.. get a book on Database Systems.. particularly the entity-relation ship model..

its as easy as o-o programming..

Anon

Hi Wallace,

Here's how I think about it.

Object/tables
1.Employees
2.Classes
3.Comments
4.Bio/Resume
5.Class Subsection

Relationships
1.Employees - Classes
- many to many
ie Each employee teaches many classes
and each class may have many teachers
2.Classes - Subsection
- one to many
Each class may have many subsections
3. Employees - Bio/Resume
- one to one
Each employee has only one Bio/Resume
4. Comment - Employee
One to many
Rule: allow only one employee per comment eg by a drop down
Each employee may have many comments
5. Comment - Class
One to many
Rule: allow only one class per comment eg by a drop down
Each class may have many comments

Many to Many:
Thus you need a table for each object, and a bridging table for each many to many relationship. ie between classes and employees you need a table, say called Rosters, with:
Roster id
Class id
Employee id

One to Many:
For each one to many relationship, you need a field for the many relationship in the table with the one relationship eg comment to employee, you need to have an employee number in the comment table. Though perhaps if you use one comments table for both employees and classes, you would have a second field for type of comment ie type 1 = employee, type 2 = class.

Hope this helps to get you started.

I find it easiest to draw the objects on a sheet, and then draw connections between the objects indicating the type of relationship, 1 to 1, 1 to many, or many to many, the follow the rules for the 1 to many and many to many as above.

Happy databasing.

Regards
Richard
THEJOIE Web Solutions
www.thejoie.com.au
Brisbane, Australia

allgood2

Richard Ferrers posted response on 2001-04-12 03:05:37:

Hi Wallace,

Here's how I think about it.

Object/tables
1.Employees
2.Classes
3.Comments
4.Bio/Resume
5.Class Subsection
...........

Hi Richard & Wallace:

Just one thought about Richard's proprosed table structure. If each employee has only one resume, and the table strucuture for employees is otherwise fairly simple (consisting of less than 10 fields), then you may want to consider the optionof combining the employees and the resume table together.

Since the relationship is essentially a one-to-one relationship. This will make your mySQL queries to display the employees resume easier.

Structure wise, I thought Richard's overview was great, and that would have been my design schematic if I was developing the database in a client-database system (Access, 4th Dimension, FileMaker, Omnis, etc,)

But as I've been playing around with PHP, I've noticed a certain inefficiency in creating one-to-one relationship tables in mySQL. This seems especially true, when the tables are fairly simple. This of course could just be because my PHP skills aren't strong enough, but...

Think of the final output options of employee info:
1.) employee by name: query just name field
2.) employee brief: name, contact, brief descript.
3.) employee resume: name, contact, bio

These are all one-to-one relationships and it's more efficient to send a mySQL query, that says:

$results=mysql_query ("SELECT * FROM employees");

then define the fields to be used in display ($address = $row["address"]😉, then it is to send multiple queries ...

$results=mysql_query ("SELECT FROM employees");
$results2=mysql_query ("SELECT FROM resumes WHERE employeeid='$id'");

then attempt to join the results in one display.

There's a great article on data normalization at this site, probably one of the easiest to understand explanations that I've seen...
http://www.phpbuilder.com/columns/barry20000731.php3

But sometimes even though the objects are different (employee vs resume) a structure can be reduced by combining tables, when this causes no other discernible inefficiencies. Meaning if the resume table will most likely always be related just to the employee table. The likelihood of you creating a comment system where people ca comment on the resume independently of commenting on the employee is thin, and the employee table is fairly simple...then combining the two tables into one may be benificial.

Alnisa

Anon

Why can't you just say:

select employees., resumes. from employees, resumes where employees.emp_id = resumes.emp_id

allgood2

You can. You could also use a join statement. I wasn't arguing against doing it in that manner. I was just mentioning that on top of the excellent advice offered by Richard Ferrers, that he may also want to read up on data normalization. Because occasional logic can produce ineffiencies, and each developer has to decide how to handle them.