New Scheduling System - Advice Please

[pendragon]

Hi All

 

I have been asked at work to write a production scheduling system and was wondering if anyone can give me some pointers on how to go about it.

 

We have 12 machines that need to be included in the system, each machine needs a day record and each day is to be broken up into 15 minute segments.

The system also has to hold 2 years data at a time, this year and next Year, with all preceding years being archived for analysis purposes.

 

What I have come up with so far (after some playing) is a database with 3 tables.

 

Table 1 will just hold the machine names and description.

 

Table 2 Holds the day information i.e. is this machine available today, how many hours have been scheduled and how much of the work has been completed.

 

Table 3 Holds the day�s hourly information broken up into 15 minute intervals, again with the same info as the day table.

 

As you can imagine there are a lot of records and at the moment, all stored in an Access database.

 

What I am wondering is:

 

1) Is this the best to hold the data? Or would a different data structure be better?

 

2) What is the best way to display the data? - Should I create my own control / display logic or is there a control that I can use. (I have tried displaying it in a listview and changing the background colour of the cells depending on the status of the time frame, but this seems to be quite slow and has a few display problems.)

 

Any pointers will be gratefully received

 

Thank You.

[mskeel]

It sounds like this would be more appropriately written as a web app, though there is some overhead invovled in doing that (setting up a server machine, scripting support etc...) which may be enough of a deterant to write a custom .Net app. The best thing to do for the UI is to be sure to include enough information and flexibility that the user can actually determine the statistics/information they are interested in seeing. (look at AWStats for an example of a web stat tool that might be along the lines of what you are looking for display wise...?). Unfortunately, it is difficult to offer much advice becuase I am unsure what informatoin your users will want or all the informaiton that is available.

 

A database sounds pretty good for holding data... You might consider flat file logs for long term archiving but, then again, a DB would probably work just fine. You may want to consider normalizing your table layout a little more. It sounds like you have repeated informaiton based on your description ("...again with the same info as the day table.")

[pendragon]

Thank you for the Reply mskeel.

 

Unfortunately they will not want it done as a web app due to the cost of setting up and the learning curve involved as I have not done any web apps yet.

 

I will leave it as an Access database for the moment but I think I may start looking into getting some sort of SQL server, MySql / MSDE etc, as there is a large amount of data being kept and It might be quicker. Table 2 and 3 have similar data fields but one is for the day and one for the time interval.

 

What I am looking to show is the schedule for a machine on a certain day

 

i.e.

Machine 6:00 6:15 6:30 ....

 

I don't think there is a control that will do what I want so will make my own display.

 

If you have any other ideas I will be grateful to hear them as this is giving me a headache :)

 

Thanks

[Machaira]

I think you could probably do with using a datagrid. It wouldn't be the prettiest in the world, but it would probably work. Attached is a sample project and SQL to create the tables I used. I used SQL Server, but MySQL would work nice if you can't afford SQL Server (pretty expensive I believe)

Scheduler.zip

Edited May 10, 2005 by PlausiblyDamp

[pendragon]

Thank you Machaira for the program, I will down load it and have a look at home (I have SQL Server Developer addition there).

[pendragon]

Thank you for your help Machaira

 

I don't really like the dataview control so I think I will make my on way of doing this.