Thread: relationship

Ok,

Going nuts and confusing myself trying to figure out how to structure my tables and relationships. Here is a summary if anyone can follow and advise:

I have a database that tracks logs for a process. Each day an employee opens the database and fills out the form for that day. This includes his/her name, date, shift, notes. Then below that is a list of 24 tags that he will fill out 3 times per shift with 3 values per tag. The tag names don't change, just the 9 values per day per tag.

I am having trouble figuring out how to structure the tables or put everything in one table. Eventually I would like to report on how many logs each employee completed each month. Do I make a table for each tag name or include all of them on one?

Anyone grasp my idea enogh to advise?

Ok, im gonna post my sample to help clarify. i have combined everything into one table. this works, i just think there's a better way. ive only added one of the tags, there are 24 more.

I'm trying to think my way through this here. So you have a few columns with the employee info then you need some sort of way to link that record to something like 24(tags)*3(values)*X(# of employees) pieces of data. I don't think you want to put everything in one table. It will be a nightmare to query across.

If that is the case, you may want to, in your employee table get valueID column and create a serial number of some sort. Maybe the clockNumber and the date with a shift and a log instance and a value set. John Doe is #32520 and the date is 12/22/2011, this is shift 1, log 1, valueset 1
"11132520122211" - The first two numbers are the shift and log instance, value set 1. The next numbers are the employee clock #, then the date. This is how you your serial to link that date he logs his values into your system. (Does not have to be super complicated, just has to be done in a manner that will not duplicate values, but has enough horsepower to uniquely identify a record)

Using this system... when your employee enters values for the first time that shift your value ID# will be 11132520122211, the 2nd set of values 11232520122211 and the 3rd set 1133252012211. The 2nd time that shift, but the first set of values would be 12132520122211 and so on.

Now you can have this big "dumb" value table with a valueID# column and the 24 tags columns. (This table will be huge, recordwise). When you write values to this table you can also parse the that ID# out to speciffic columns. When you create the record you can use the right/left/len string functions to parse out the log instance and the value set so it's easier to query over. This way give you a primary key in all tables.

There is another way, where you give the employee a valueID# with just the clock and date. Then when writing to the values table it is specified in the column what instance and value set it is.

So Mr. Doe would have 32520122211 as his valueID for the day and in your values table you will have 9 records with that valueID but you will have another column that has log instance and another with value set.

table as follows.
Value ID logInstance valueSet TAG1
3252012211 1 1 500
3252012211 1 2 350
3252012211 1 3 400
3252012211 2 1 600

etc.
I hope this helps. If nothing else gets your brain thinking, or someone looks at my answer and gets so outraged by its incorrectness that they give you some good advice! It's a pretty tough dataset to organize!