Trouble setting up relationships

[laruajo]

I am having trouble setting up the relationships in my database correctly and looking for any help that anyone can provide. Here are my tables (some tables have many fields so I will only list the primary key which is the only similary field in the tables, if you need more info just ask)

Table A
SO Num (Primary Key)

Table B
SO Num (Primary Key)

Table C
SO Num
IRR Num
SOIRR(primary key, automatically fills in with SO and IRR Num values)

Table D
SO Num
IRR Num
SOIRR(primary key, automatically fills in with SO and IRR Num values)

Table E
SO Num
IRR Num
SOIRR(primary key, automatically fills in with SO and IRR Num values)

Table F
SO Num
IRR Num
SOIRR(primary key, automatically fills in with SO and IRR Num values)

Table G
SO Num (Primary key)

The relationships need to be set up (referential integrity enforced?) so that a record with a SO Num of 123 cannot be entered in Table B before it is entered in table A and so on. Each table has a form based off of it. Each form will be filled out by a different department within the company and must be done in order. Would it be better to get rid of the SOIRR field and just make SO Num and IRR Num a combined primary key? Any help would be appreciated. Thank you.

 

[vb6novice]

laruajo,

Without knowing what other fields are in each table, it is hard to know how the relationships should be established. Do tables A, B and G have unrelated data? Can there be duplicates of the primary key in any or all of the tables?

The rules of normalization would dictate that all the fields that would relate to a unique primary key (SO Num) would be in one table, and all the records with a unique combination of keys (SO NUm and IRR) in another table. The exception to that rule is usually for security purposes (preventing users from seeing data that they have no need to see).

Your explanation, however, leads me to beleive you have many tables with the same key(s) solely for the purpose of tying them each to a different form. If that is the case, then you are going to a lot of extra trouble, because you could write a query for each of the forms to provide specific information to it, or, even easier, base all the forms on a single table and only provide controls to view what is needed for that form. Then you would not need to be concerned about referential integrity except in the case of between the one table and the many table.

Please provide more information.

 

[laruajo]

The setup of tables was not done by me, I was given the specifications to do it this way. The forms need to be filled out in a certain order, so if all the info was in two tables (one with the primary key SO Num and one with the primary key SO Num/IRR Num) how could I control what order the info was filled in? Each table that I have now has between 5 to 15 other fields, except for a couple fields that are duplicated in two tables, all the fields are different in each table. I hope this explains where I am coming from better.

 

[vb6novice]

laruajo,

Your explanation is helpful. It also leads me to more questions.
1) What causes a new record to be created in Table B when the data in the form for Table A is complete enough to allow moving to the form for Table B (likewise for the other tables)?
2) How do you currently control what order the forms are filled in?
3) Do different users complete different forms (Jill does form 1, Bob does form 2) or are all forms completed by a single individual?
4) If each form has a different table as its record source, and each table has different fields, does every field in the table have a control on its form?
5) Do ALL or only some of the fields for each form have to be filled in to allow going to the next form?
6) Are users prevented from deleting a record in any table?

You asked how you would control what order the info was filled in? How do you do it now? With only 2 tables, each form would show only the fields that get filled in for that form and only when all required fields are complete would the user(s) be allowed to move to the next form. You could have a NEXT button on each form which would cause the required fields to be checked for existence of entries, perform validation on those entries, and if everything was acceptable, close the form and open the next one. The next form would have different fields displayed (except for the main fields, like SO num and IRR num, or some other info that would idenfity the SO).

I doesn't have to be that way, but having fewer tables minimizes the problem of synchonizing records, referential integrity, update anomalies, etc.

 

[laruajo]

Let's see if I can answer all of your questions.

I haven't been working on this database very long so as of now there is no way to determine what order the forms are filled in (a record can be wet up in form B even if it doesn't exist in form A). Each form has some required fields, but no all are required. Once the form is open, all required fields must be filled in. Once this is done then the info for that record can be entered in the next form.

There is not one specific user that fills one form and another for another form. There is however, different departments that will fill out each form. The database is intended to keep track of info on parts that are beind manufactured in the company. This is why the record must be filled out in the forms in order. For example, inspection should not be filling out its info for the part before manufacturing does. Do you see what I mean? The part must be manufactured first with the info filled in, then it goes to inspection and they fill their info in.

Lastly, users most likely will not have access to the tables.

I hope this helps.

 

[vb6novice]

Laruajo,

Your original question about how to set up the relationships among the various tables will depend on whether you decide to keep the existing table structure (not ideal but workable) or another table structure. The only real difference will be how the processing goes. Ether way, you need to have a process flow that prevents the situation you mentioned of being able to set up a record I table B without it existing in Table A. You must control that situation through how you move from form to form.

For the rest of this discussion, I'll assume you stick with the 7 tables. Assume Table A displays only on Form A and is only used by Department A, likewise table B on Form B by Department B, an so on. Say that Dept A gets an order for a part. Dept A should be able to create a new record in Table A for the part. So Dept A user creates a record for a new part order and gives the order an SO number (or maybe the system assigns an SO number) and fills in some other info about the part. When all necessary info is filled in, Dept A user clicks DONE (or something like that) which checks that the required fields are complete and that the entries make sense (like the part number of the part to be manufactured actually exists). If all is NOT OK, user gets a message saying what is missing and what is invalid. If all IS OK, the system marks the record as department complete (not the same as order complete, as described later), indicates it as such on Form A, and writes a new record in Table B with the same SO number. Dept A user then must NOT be allowed to click DONE again for that part order by, so the DONE button must be disabled for that order. Table A user moves on to another part order (which enables the DONE button if the order is not yet department complete).

Table B user now opens up Form B, which displays the record for any part order that does not have complete Table B information (this would include new records). Dept B user fills in data and when he/she thinks its done, clicks the DONE button on Form B. Now, just like for Form A, system checks completeness and correctness of data, and either informs user of problems or marks the record as department complete and disables the DONE button.

Processing continues from Form to Form, Department to Department in that same manner until Form G is clicked as DONE. When the part order has finished all the departments, the system must go back and mark all the records for that order in all the tables as complete (not the same complete as for a single table) so that it no longer appears on any of the Forms (unless the user has ability to look up information about completed part orders (although this information must be kept from being changed, except by authorized personnel).


You also must control which forms users are allowed to see and which data they are allowed to change at the department level. Is there any reason Dept C needs to see information put in by Dept F? Probably not. In my opinion, Users should never has access to the tables. The developer should let them see only what they need to see, and the database administrator can go into the tables if needed.


Are the tables created yet? Does their data structure have enough control fields (such as the Department Complete and Order Complete fields mentioned above) to provide the protection between departments and to control process flow? Also, are you experienced enough with Access and VB to create all the necessary forms, macros, VB code to accomplish this. If you think you need more help with that, please contact me outside of this forum at bkoran@newbreed.com .