Database problems

[Fistandantilis]

OK so I am trying to creat this database for the plant manager for the company that I work for, this is totally being done on my personal time and I am not being paid for it, I am just trying to be helpful. no here is my proble, I am makeing the dtabase to reflect employees production, here is a basic rundown of things,

each employee has many production dates, each production date has many production periods.

for instance, at 7:00am I get into work and from 7:00am to 9:30am I work on one machine, then I goto break. at 9:45pm to lunch I work on another machine, and so on and so forth for the day.

I want to be able to track my production from work period to work period for different products on a daily basis for many employees, this is what I have for tables:
(Primary keys are in bold)

EMPLOYEES
Emp_Num, Emp_F_Name, EMP_L_Name.

PRODUCTION DATES
Production Date, Emp_Num.

PERIODS
Production Date, Period, Machine, Position, Product, Time on Machine, Parts Per Minute.

How can I make this work? does anyone have the insight that I am so desperatly seeking?

Thanks

Jawsh

 

[Tawnos]

Your periods table will be problematic, because, at least how I'm understanding it, the period will not be unique, invalidating it as a usable primary key. I don't see it as unique, as every day the period restarts at 1.

While I'm still pretty new to database normalization, I'd likely approach it like this:

Employees have an ID, their employee number, that's unique. The first name and last name of the employee can be stored in the same table with that. Dates where employees worked are unique, they get their own table. On a specified date, and period, multiple employees may work on the same product/position/whatever. I'd create a table with two foreign keys that reference the date and employee tables, and contain unique log info.

EMPLOYEE_INFO
id
first_name
last_name

DATES_WORKED_BY_EMPLOYEES
date

LOG
date (references dates_worked(date))
employee (references employee_info(id))
period
machine
position
product
time
ppm


Now, I think this could likely be normalized further. Specifically, I think you mean "part" when you say "product", as in part put onto machine. As such, there would be multiple lines that contain identical information, where the only change is the product. This would be moved to another database (machine?) and linked as another FK in the log database.

Can you further clarify, or will that suit your needs well enough?

 

[mikeblas]

each employee has many production dates, each production date has many production periods.

for instance, at 7:00am I get into work and from 7:00am to 9:30am I work on one machine, then I goto break. at 9:45pm to lunch I work on another machine, and so on and so forth for the day.

Your example isn't clear. Presumably ,the employee is you. In your example, what is the production date? What are the production periods?

I want to be able to track my production from work period to work period for different products on a daily basis for many employees

What is a product? You haven't mentioned this entity before. How does the Products entity relate to the other entities you've told us about?

(Primary keys are in bold)

EMPLOYEES
Emp_Num, Emp_F_Name, EMP_L_Name.

PRODUCTION DATES
Production Date, Emp_Num.

PERIODS
Production Date, Period, Machine, Position, Product, Time on Machine, Parts Per Minute.

Why is the primary key in PRODUCTIONDATES not inclusive of the Emp_Num? The way it is modeled now, you wouldn't have two different employees on the same date. I can't tell if that's right or wrong because you haven't defined what a production date is, but it seems suspect.

Of what data type is Period in the PERIODS table?

Anyway, you'll need to explain the relationships you want for us to provide concrete help.

 

[Fistandantilis]

sorry for bein so vague, here is what I got so far,

I have some tables set up as look up tables they are:

Machine
Product
Time period
Position

These do not have PK's, I made them to hold data for the main tables. for instance, when the manager is entering data for that days production rather than him type in a machine name he could just use the data in the lookup table in an effort to thwart data entry mistakes.

the main tables look like this:

EMPLOYEES
Employee Number, Emp First Name, Emp Last Name.

PRODUCTION
Production Dates, Employee Number.

PRODUCTION DETAIL
Production Date, Period, Machine, Position, Product, Time on Machine, Parts Per Minute.

each production date will have many production periods, a period is the time between breaks, during each period an employee will work on a machine and each machine has many positions, and all positions on a given machine will be working on the same type of products.
What I am hopeing to do is show that for each EMPLOYEE there will be many PRODUCTION DATES each of those production dates will have many periods showing an employees performance.

I know this may seem confusing to read, I just quit smoking 2 days ago and I am all sweaty and figity and I really cant think strait so I am doing my best to convey my thoughts to you guys, thanks for the help.

Jawsh

 

[mikeblas]

A lookup table without a PK doesn't make any sense.

sorry for bein so vague,

No worries. Let me know when you've got an explanation for the relationships. You'll find practically answers your own question, anyway.

 

[msny]

Your basically looking at creating a Time n' Attendance system for production data.

I've created and worked on a number of these over several years.
Here is my advice. Start at the beginning, remember that data is just data
until you can make it into some kind of logical organized information.
It has to make sense to both computer processing and people alike.

Forget about creating tables and such right now, concentrate on what the information might look like at the gradular level , then work up. Gather up requirements first. Here are few questions that may get you started, you think up the rest and fill in the blanks.

Does this company already have a payroll system where they keep track of employee data?
If yes, see if you can get access to any pre-existing data. Hourly and salaried people get paid differently, determine if this an issue

How does the system work now?
If there using a paper system, try to mimic that process for gathering requirements. Users like paper trails beleive me, try to do what they do....

For instance what are the time periods worked?
7 to 3, 3 to 11 ect.
In shift work, take into account regular time, time and 1/2 ect.and the rates that apply. Some places may pay more for nights or weekends. You will need to be flexible here.
Find out who detemines standards and what are they such as machine rates ect.

What kind of machine(s)?
Define it with a description and maybe plant location.

How many people does it take to run it?
1,2 10 ect. full time, part time ect...again you need standards

Do they have a products database already?
Again collect any paper forms they may currently have.

ect.ect.

Your a long way from designing anything worthwhile. I suggest you interview the people
concered starting with the plant manager, formans, line operators ect....

Once you know all the details post again and we can help you better design a DB that works.

 

[Tawnos]

etc = et cetera (and all the rest).
ect = ectoplasm?

Let us show the world geeks know how to communicate!

On topic:
Mike, when he says "lookup tables" he may be referring to join tables that use foreign keys instead of primary keys. However, given the way he referred to them, I'm guessing you are right in that he needs primary keys.

Fistandantilis (hereafter referred to in the second person):
Your data tables need primary keys that are unique and can be referenced from whatever table calls them as a foreign key. I'm partial to keys with no data association (essentially, unique IDs that are simply auto incremented numbers).

MACHINE
uid
type

PRODUCT
uid
type

Et cetera for the other data tables.

Your "production detail" table will consist of joins of this information. It says how the data in the other tables is related. There will be no primary key, only a number of foreign keys referencing the data tables' primary keys. The lookup table may also contain information unique to the join of those other tables, such as a "parts per minute" row.

As Mike said, definition of what your real data is makes this a whole lot easier. Decide what needs to be stored, and figure out what variations/repetitions might come up. Report back, and, chances are, you'll have answered your own question.