View Full Version : Begginner Help

05-03-2010, 04:50 AM

I'm new to php(self taught from a book 'head first php&mysql').

I have started a small janitorial business in CA. I have some background with html and flash, and recently jumped into php because of an idea a had!

I am building a website for my business:

My goal is to create a web application for me to add employee's, & clients to give username/passwords for:
1. The employee's to check their schedule and request time off etc.
2. The clients to see who and when the janitorial service will be performed.

I am baffled at how this will all work.

My Question:(forgive me if it's a dumb Q!)

How will I build a schedule and attach it to a an employee and location? In otherwords, how can I create a SELECT statement to view the 'EMPLOYEE' & the 'SCHEDULE & LOCATION' assigned to them? It's not like I can put a field in every employee table with 7 days and also the hours for the days!?

I'm pretty lost with this one...maybe because I haven't had enough experience in php..

Thank you for taking the time to read my post!
Cory Robinson

05-03-2010, 06:12 AM
That's not really a question for PHP but just databases in general. This is not something that can really be easily taught either. Even experienced programmers sometimes need to take a step back and think about the best way to design their database.

Basically the usual way to create tables is like this:
1. Create 1 table for each "entity" you want to put in the database. Create a users [employees?] table, create a messages table, create a locations table, etc.
2. For each of those ONLY add details that are INHERENT descriptions of each thing. So each employee will have a name, an id, years of experience, an email, etc., but do NOT try to force each message they send or each hour they work into that.

To summarize those two: think of the simplest system where you can split everything into entities and properties [of those entities].
After that, you will figure out how to cross reference and search to find the data you want.

3. NEVER duplicate information in the database. For example, do not attach usernames to events; attach user IDs to events. This way you will be able to search for user "8" and cross-reference the name to the event. The only information that should get duplicated is this ID. This means that you NEVER change the IDs but you can change the names or the events but both are still attached to each other through the shared id. This is where it gets complex, but it'll make sense soon enough.

So if you get all three of those worked out, you have a well designed database. The final rule: keep it simple. But don't limit it too much to start that you end up lost or duplicating information later.

There's one way this gets a bit confusing: everything is a property of something else in some sense. When do you split it into a new category? That's very simple:
Keep everything in a single table with one entity modified by properties, UNLESS you have a property that needs properties itself. Let's say that you want to store "phone" for each employee. In this case you'd want to have a new table for "phone" because you'll want to modify it by "type" (for day/work/evening/cell phone) and for "preferred" for the first number you should try. (Ok, that's not a great example, but I hope you get the idea). Also, you will want to split any property into a new table when that property forces you to make multiple entries: in the same sense with the phone example above, if you are going to list multiple numbers for some of your employees, you must have a separate phone table or you'll have to have multiple entries for each employee-- that wouldn't make any sense.

So here's a rough example of how a system might be setup and this might work for you:

You have a table called "employees":

You have a table called "locations":

You have a table called "schedule":

That's very clean and makes sense. No information is duplicated and everything is cross referenced by an ID.

Now if you want to gather all of the days that "Joe" is working, you will first find Joe's ID, then you will search for all entries in "schedule" with EMPLOYEE_ID matching that. Then if you want to find out WHERE Joe is working, you will find those LOCATION_IDs and then match them to the NAME of that LOCATION_ID in the "locations" table.

It sounds complex, and it can be a lot to setup, but this makes for very fast and very efficient cross-referencing.

Of course you can also be lazy and not set all of this up, but if you end up duplicating a lot of information and finding it hard to manage, then it would have been worth it to set this all up from the start. If you are always going to have a limited, simple system, then starting simple won't hurt.

You can build any kind of database you want, but it has to be efficient or it'll end up being more work later than just setting it up the "hard way" now.

By the way, here's a great tutorial for understanding php and mysql if you want something else to reference. It's organized reverse-chronologically, so start on the last page and work your way back. (Aside from that layout issue, it's a really good guide :)).

05-03-2010, 03:53 PM

Dude, right on! Thanks for breaking it down like that. It really makes sense to use the id's to link employee's to locations.

Very cool, and thanks for the tutorial also, thats helps a ton.

I'll post the website when I get it done.