database design 3

[Xzibit7]

I have two questions

(1) I am trying to make a database that shows a list of unique devices. These devices will go through a process that entails:

-assigning the device to initial review
-initial review will then do one of two things, send it to a final verifier or send it to engineering review with a comment on what is wrong
-the verifier will have the task of assigning the device to initial review and verifying.
-Engineering review will do the above tasks as well but also perform the engineering review on the devices that need it and then verify.

My first question is that I have set up only two tables for this one that just has the personnel and there specific group, and one that has all the information on the devices such as where the device is in the process etc. Am I blatantly wrong in choosing to use only two tables?

(2) My second question is that there are individual devices which will have a primary key, however I would like to be able to assign the devices to a specific person in initial review based on groups or types of devices. I do not want this individual to be able to see the groups just the unique individual devices assigned to them. So for instance, I would like a form that lists all the groups and shows how many devices are in each one of these groups and then the assigner can assign all of those devices to one person on initial review with one click. Then another form for initial reviiew which shows all the individual devices assigned to that specific person. Is this possible?

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Xzibit7]

Okay bad question, sorry guys how about this I am trying to take a field with duplicate entries and only show that, the number of duplicate entries per field line (not sure how to count number of duplicate entries), and a dropdown to select who would get assigned that item on a form. Once this item is assigned another form would show the assignee all the unique (primary key) items that make up the item with duplicate entrees. Any help would be appreciated thanks.

 

[lespaul]

First, did you read the article PHV recommended? Do you have your tables all set up according to those rules? What table structure did you end up with to track all the different "steps" a device has to go through? Does a device ever go through the process from beginning to end twice? I personally wouldn't move into forms development until I was sure the table structure is correct. If you would like to post the structure, we'd be more than willing to help make sure it's correct.

You mention not knowing how to count duplicates, here's a little help on that:

SELECT Field1, Field2, Field3, count(*) From TableName GROUP BY Field1, Field2, Field3

where the fields are your "duplicate" information... this will tell you how many records have that "duplicate" combination of information (Field1, Field2, Field3)...

HTH

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Xzibit7]

The item will only go through the process once, right now my table structure goes like this a have a table (data) with the following fields:

ID(primary key); Lab#; Part# (exploited duplicate field); Initial Review (y/n); Initial Reviewer (Name); Verified (Y/N); Verifier (Name); Engineering Review (Y/N); Comments(Text)

and another table personnel with the names of employees for drop down menus.

I have read the site and am unsure how to use the information based on my situation. For instance my whole database only has one "thing", also if I had seperate table for each place in the queue I would have to take the items out of the previous table and each one would still contain the same information just a different check.

 

[fneily]

Using the infomation from PHV's reference, I'd have 6 tables. Of course, this is just normalizing tables.

tblDevice with fields DeviceID, other device info fields
tblPersonnel with fields PersonnelID, other personnel info
tblType with TypeID, TypeDescription
tblDevType with the primary keys DeviceID, TypeID
this table connects the devices to a "group". And it has a multi-field primary key of the two foreign keys.
tblAssign with fields PersonnelID, TypeID
this table connects a "group" to a person. Multi-field P.K.
tblVerify with fields DeviceID, I.R.D, V.Personnel, E.R.D., E.Personnel, Verified(Y/N), comments
Primary Key would be DeviceID
I.R.D. is Initial Review Date
E.R.D. is Engineering Review Date
V.Personnel is PersonnelID of Initial Verifier
E.Personnel is PersonnelID of Engineering Verifier

So you can easily assign a verifier a "group". A form can show all the devices in a type and they can all be assigned on one click. Just like you want.
tblVerify tracks the verification process.
Also using the dates in this table, you can also create a graph of the progress.

 

[lespaul]

I would probably have the Verify table structured more like (I would also name it DeviceReview):

DeviceID
ReviewType
ReviewDate
ReviewerID
Verified
VerifierID

Where reviewtype would be intial or engineering, the date of the review and the ID of the person doing the review, Verified (Y/N) and the ID of the person doing the verification of this review. Have to agree on all the other tables though.

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Xzibit7]

Thanks everyone, you have been a big help.

 

[Xzibit7]

I am a little unsure of the tblDevType though. Does having two primary keys allow you to have duplicates in one of the fields? Also I understand tblType but what is the benefit of having tblDevType when I could just add the type to the orignal tblDevice.

 

[Xzibit7]

Also tblAssign allows the connection of all personnel to the group, what if I just want the initial review able to be assigned a group

 

[PHV]

Did you read the doc I suggested ?
Another helpful doc:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

LesPaul - after I submitted, that structure occurred to me too. But, this is a good example of taking some time to create the tables. The first try may not always be the best, or correct.

Xzibit7 - BOTH fields are the primary key. So you can't think of it as "duplicates in one of the fields". Let's say you have two devices, radio and beeper. Both are electronic. So the primary key would be radioelectroic and beeperelectronic. Electronic is duplicated, but that's not the primary key.
Also, with tblDevType, I wasn't sure if a device could belong to more then one type.


tblType may or may not be used. I've had discussions with others in Tek-tips on this. I use it because descriptions may change. In the above paragraph the type is electronic. But maybe at a future date a V.P. wants it to say "runs on electrical current". You only have to change the description in one place - the table. In your way, you have to go and do a mass change and hope you don't miss anywhere.

"Also tblAssign allows the connection of all personnel to the group..". Oops. Primary key should just be TypeID. Now only a single person can belong to one type. Good eyes. I should do what I usually tell others - first design with a pencil and paper, then go to the computer.