Data Storage Dilemma - need suggestions!

[DanShadow]

Hey all.

I'm working on a web-application for a client (who doesnt know computers too well), and he wants 3 main sets of information stored for online access:
#1) - Client's (basic) Business Information
#2) - User Login Data
#3) - Client's (not so basic) business schedule information

I'm thinking of setting up #1 & #2 in Database tables, and because the requirements for #3 aren't 100% finite, and their could be a lot of multi-dimensional array data, was thinking of storing #3 in a Unix file-folder with user read/write permissions only.

Security wise, the client's Business Information & User Login Data will be secure in a Database with SHA1 password encryption.
The Business Schedule Information security isn't the best, but he says it doesnt really need to be, as long as I make it so users logging in can only access schedules pertaining to the business they work for.
(#1 is assigned an auto-incrementing BusinessID primary key, #2 is assigned a BusinessID for the business they work for, and a UserID primary key, #3 file records have been assigned a BusinessID to identify which business the schedule information is for, and a list of UserID's to identify which users can access it).

Sorry if that was a bit confusing, still trying to wrap my head around it.

Soo.. my question is: does anybody else think this is an ok idea? If not, any suggestions??

Thanks!

[Drahcir]

So is the client's business schedule in something like a .doc file?

And yeah, separating the login logic from the business information is good.

However, for your business schedule table, you said it will have a "list" of UserIDs to identify it. I hope you're not using a comma separated list for permissions. So instead of your table looking like

id business_id permission
1 (1)google larry, eric, sergey
2 (2)apple steve, larry

It should look more like
id person permission
1 larry (1)google
2 larry (2)apple
3 eric (1)google
4 sergey (1)google
5 steve (2)apple

Although this type of table has more rows of data, it's generally easier and more optimal to do a SELECT for the person and check if he has permission rather than selecting the business id, and parsing through the permission to check if they exist.

See, select id from table where person='larry_page' AND permission = 1
And if you get a result that has a row number > 0, then you know he has permission and you're done.

But if you were to do it in a list form, you'd have to go
SELECT permission from table where business_id = 1
And then you have to use another function to look for the name inside the permission.

It's a tradeoff between storage vs speed and simplicity, but since storage is cheap, the other is better.

If that's what you were doing in the first place, then forgive me for misunderstanding.

EDIT: Ugh, there's supposed to be spaces for my example, but I forgot HTML doesn't care how many spaces are between each thing...

[DanShadow]

I'm not too worried about the DB end of the program, mainly the Business Schedule Information.
(Which will hold a variety of data, including digital Work Schedules, with employees shifts on it)

As I said, because the Business Schedule Information is pretty complex, with the potential for a lot of information that will be stored in multi-dimensional arrays, I was thinking of using a server-side file storage for it, with user read/write permissions only.
The web-site itself will be able to load the Business Schedule Information by users who login with the right permissions to view them.

My main concern was whether the file-based storage for the Business Schedule Information was a bad idea. The client says he doesnt care how its stored as long as the appropriate users can access them, but im a bit concerned about security. (Say somebody figures out where the Business Schedule Information files are stored server-side, and even though read permissions are for user only, figures out how to read the schedule data anyway - thus giving them access to a business client's work schedules, etc.)

Thanks for the table optimization suggestions though

[Drahcir]

Can you give us an example of what the schedule looks like, and perhaps we can suggest a type of table that may be able to cover it?

[DanShadow]

_____MON___TUES___WED___THURS___FRI___SAT___SUN___

____John____Mike____John___John____Adam__Mike___Mike
____10-3____7-5_____8-3____5-7_____3-9____4-12___10-3
____April____Martha_________Stewie__Mark__________Phil
____10-3____7-5____________5-7_____3-9___________10-3


This is one example, the work schedule.
7 days, each day can have any number of employees and any number of shifts.
There can be several schedules.
Each schedule has certain information, like the BusinessID its associated with, and the UserIDs that can access it, as well as the weekly period it is. It should also contain how many labor hours per person, per day, and per week are stored (but this can be done on the web-site).

This is one of the Business Schedule Information examples that i`ll have to store.

[Drahcir]

Actually, I don't really see what's keeping you from using a table with that schedule information.

This type of data can be stored the same way as my previous database schema example.
So for columns, you can have id (auto_increment), schedule_id, business_id, day_of_week, name, shift
Each of the workers have their own row in each schedule. So your example schedule there will add 12 rows in your table. One for each time a person appears.

Personally, I don't like storing files unless they're image files. Storing images in a database is a big no-no. But for tabular data like a schedule, it's perfect for being entered into a database. Almost anything that can be stored in a column or a row should be stored in a database.

Personally, I would advise against file storing solutions because it's not as easy to maintain. If you're trying to move servers, you'd have to copy all of your files over to the next server while maintaining the same file structure tree. If it was in a table, all you'd have to do is export the table, and import it into your new server. You can even make simple backups in a click of a button if it was in a mysql table.

And if you were to change directories, accessing files can be a pain. If you were fetching from the top level directory, perhaps your file fetching location might look like /files/schedule. And then you had a subfolder which you needed to find the schedules again, which would be ../files/schedules. Then it becomes a nightmare to maintain when you move around files, or if you're trying to fix a bug across all the files. A table would not have any of these problems, since the location of the data is abstracted.

[DanShadow]

Yeah, I was thinking about that more, and there are several ways I could use tables to represent schedule data.
I was also thinking of an approach of storing elements in a "shifts" table, so that I can do a SQL search for shifts with a date on a particular schedule, and just set the row on the column specific to the UserID of the shift. I could also offer a feature to display shifts previously worked by a User too.

Thanks again!

[yoursecretninja]

Why not use two tables for this. Have an employees table where you store the employee information. Have a shifts table where you have four fields: id, employee_id, shift_start, and shift_end, where employee_id is a foreign key which references employees.id and shift_start and shift_end are datetime data types. This would be efficient, flexible and easy to extend later if requirements change. A schedule would then be formed by a query for shifts within some date range. No need to store days of the week or anything like that as this can be derived from the datetime.

[DanShadow]

Thanks for the suggestion, thats pretty close to what I think ill be doing .