Need simple db incorporating employee fields: name, img, description, schedule

[caltech]

Hi, I'm rather new to MySQL/db management, though I do have a more substantial background in web development with HTML, CSS and PHP, as well as some C++... so while I may be sort of familiar with some of the general concepts needed to understand and utilize MySQL database design, I really know nothing about it specifically. But with some guidance I should be able to fare fairly well on my own.

So anyway, I'm redesigning a website for somebody's business. I need to create a database with records for each employee. The record table should incorporate the following fields of information for each employee: name, photo, description, schedule.

The name and description fields are a given; what I need help with are the image and schedule fields. I'm using MySQL Workbench, because I thought that would be more palatable for the business owner to use for managing the database. Please keep in mind that this database design must be super user-friendly, so that she can manage it simply and easily enough once created.

So how can I go about setting up the fields for the employee's photo and work schedule? The schedule will go by days of the week -- for example, employee XXX works Monday, Friday and Saturday. Is there a way to create a field which includes multiple values from a list of accepted ones or something? I really don't have any clue, so please do give me any and all relevant information and pointers, explain it to me thoroughly so that I can better understand my options and limitations here.

Ultimately these records are to be implemented on the site using PHP. So far the plan is to have 1) an Employees page, which will call the name, image and description fields to be formatted and displayed, and 2) a page for Today's Schedule, using a simple PHP script to determine and display the date, and based on which day of the week it is, display the employee info -- specifically what or how it's laid out is TBD -- but the point is that it can list the employee records for those whose schedules (schedule field) include that particular day of the week, whatever it is. Or the Employees page might just display an image gallery of all the employees' photos from the records, and then display more information from the corresponding record when you click on one's photo or something -- but you get the idea; that's not important yet anyway, as I'm not yet at that point. But you see the end objective I'm trying to get at. For now I just need help effectively constructing the database.

Thank you kindly in advance. Much love guys, and high hopes.

[caltech]

For the schedule field I was thinking of whether I could use an array, maybe that would accept integer values corresponding to the days of the week... however another aspect I forgot to mention is that some employees' schedules consist of days where they're "on call". How could I incorporate that into the design?

Thanks again.

[seslie]

Hello caltech,

what i usually do before i begin any of my applications is to sketch how the interface is gonna look like on a piece of paper, somehow this helps me figure how to design the database...

So, also wanted to ask about the schedule. Is it static? for example; for the period employee xy is with us, he'll work every mon, tue and sat? or u have something like this;
for the period this employee is with us, he'll work on mon morning tue evenings and sat mornings for the first few weeks and would be changed..do u understand what i mean?

[caltech]

Hey Seslie, thanks for responding, hopefully I'm not getting back to you on this too late... I would sketch it out for myself if it would make any real difference, but I really know nothing about mySQL databases or field types or anything. I've tried reading up on it a little but it didn't exactly all come together for me too well, hence my posting here...

The schedule is static, with exceptions periodically, but I wasn't really planning on accounting for those, because it'll say that the schedule is tentative, and to call ahead to confirm. However, some employees only work on call... which I guess I would just say with the schedule also... or they're on call for certain days... oy. So I guess people who are on call on particular days could show on the calendar by as such... so maybe in a sub-class... as you see I really don't know what I'm talking about... and then have another class (or whatever) for those who are on-call only, and either link to a page that will show their profiles only or list them below or something...

But for now please disregard all that actually, all that about the schedule. I think I'll just implement that whole thing more manually, like use something super user-friendly and simple, like Google Calendar or anything... This place isn't huge, it won't be too much of a hassle to have to simply update the schedule if everything else is pretty much automated. What would help though, in that case, is if I knew how to have the names in the calendar link to their profile pages automatically... which I'm sure wouldn't be hard, and I mean even if that's not done it's not the end of the world... plus the other stuff needs to be done first anyway, so forget about the schedule entirely for now, unless you know offhand an easy universal way of linking their names in the calendar like I said.

And I'm sorry that was so confusing or scattered. Thank you.

[thegerm]

Hi Caltech,
WIth MYSQL, you can create a field that accepts only specific values and this may be what you want, something like this

CREATE TABLE `employee` (
  `E_id`       smallint(6) NOT NULL auto_increment,
  `E_Name`     varchar(60) character set utf8 collate utf8_unicode_ci default NULL,
  `E_Schedule` enum('Sun','Mon','Tues','Wed','Thur','Fri','Sat') default NULL,
....
  PRIMARY KEY  (`E_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Of course you will need to use the PHP to make sure that you don't try to store any other values in that field.
This problem is not trivial and you really need to know the answer to Seslie's question about changes to the schedule. Also you might want to think about whether or not you need to save historical data, this is often an implied requirement.