assiastance with starting my database design

[nwilcox]

I have started a new project and need some assistance

I am trying to set up a screen for a user to input data and it then gets saved in my database.

Example: Number of parts produced per hour
Hour 1 60
Hour 2 59
Hour 3 59
Hour 4 40

and so on.... 8 hours total

Do I first start with creating a Table with Hour 1, Hour 2, etc as the Filed names? Is there a easier way to design the screen and it them creates the fileds I need?

 

[Mike Lewis]

There are a couple of issues here. First, whether it is easier to design the screen first and then create the fields. My advice would be to concentrate on the design of the database. Once you have got that right, you can worry about the user interface you need to maintain it.

The database design is much more permanent and far-reaching than the screen layouts. Put all your initial effort into getting that right.

Second, whether to have a separate field for each hour's work. In general, it would be better to have two tables. The parent table would contain the date and related information. The child table would contain one record for each hour's slot per day (so if your data spans 100 days and you always have eight hours work per day, the child table would contain 800 records).

The disadvantage of putting eight hour fields in each record is that you would waste space in any days where there weren't eight hours of work, plus you would not be able to handle exceptional days where the number of hours exceeded eight. If the number of hours ever changed, you would have to re-structure the table. It also makes querying more difficult.

If you are still unsure about any of this, look for an introductory book on database design. This issue will discussed in the chapter on normalisation.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[jesylv]

Hi nwilcox,

I agree with Mike. You should first design your database then worry about User interface. It is usually a bad practice to put similar data in different columns. It makes computation very difficult. For instance, how would you compute average production per hour?

For your project (and not knowing your other specifications) I would suggest a table with the following structure:

ID -- Incremental integer - Primary Key
DateProd - Date - Date of production
HourSlot - I - Hour ID
nbParts - I - Number of parts produced


Then you could calculate total production by day:

SELECT DateProd, SUM(nbParts) as TotParts FROM myTable group by dateProd

Normalizing data into two tables like Mike suggested would be a superior design (better normalization) but my suggestion is probably simpler and could possibly meet your needs.




Jean

http://www.sigp.ca