Help with structure of first database design

[ej256]

I’m trying to create a database where the operator (me in this case) receives a log and then assigns multiple people to review this log based on the various subsystems they represent. So for example, a test is run called x. Once x is run, I receive the log of x and assign five people representing different subsystems (HW2, MIS, OP, SW2, SE, HW, SW) to review it. What I’m trying to do is manage all the reviews and see if any reviews are outstanding. The structure I was hoping for, and what I have implemented so far consists of the following: I have two forms in which to enter data, one for the run information that I enter, and one for the reviews that the other people enter. The problem I’m running into is how I’m able to view who has done each review.

I originally was thinking about creating a query where I would be able to search by last name and show who had done what review based on whether or not they had checked the Done Review box. The problem with this was that unless a person created a review then a review wouldn’t exist at all so I wouldn’t be able to query whether or not they had completed it based on the check box. The other Idea that I had was to add a check box for each sub system in the run information table and then map the check box in the review form to that check box in the run information table based on the RUN ID and Domain info they entered into the review form. I haven’t been successful in that either as I haven’t found a way to dynamically update the control source of the check box in the form. Anybody have any ideas of how’d they’d implement this or something I’m missing? This is my first dealing with database development and I’m at a loss right now. Thanks.

attached is the relationship of my tables

 

[plog]

The first step in every database is setting up the tables properly. That process is called normalization (https://en.wikipedia.org/wiki/Database_normalization). Read up on that, google a few tutorials then give it a shot with your data.

Forms have nothing to do with this process at this point, so put those out of your mind. You need to correctly set up the tables to accomodate your data.

 

[ej256]

The first step in every database is setting up the tables properly. That process is called normalization. Read up on that, google a few tutorials then give it a shot with your data.

Forms have nothing to do with this process at this point, so put those out of your mind. You need to correctly set up the tables to accomodate your data.

I've done a decent amount of reading having worked through nearly an entire access textbook. I've already setup my tables and tried to include a picture with my post showing my relationships, but I can't since I haven't posted 10 times. My table structure is as follows: I have one table called run information and another called review. I have a one to many relationship from run information to review linked by my run ID parameter. I also have a couple other lookup tables that include peoples names, subdomains, etc to enable a lookup dropdown combo box for data entry in my forms.

edit: I figured out I'm able to attach things so please see attached for my relationships.

 

[MarkK]

You don't need the word "information" in the name of a table.

There are things, or 'objects' or 'entities,' in your verbal description that don't appear in your tables, like people, and sub-systems. I would expect there to be tables for that data too.

Also, is there is a fixed list of tests? If so then you need two tables for tests, one being an abstract list like a catalog, and the second being the actual tests that occur in time at an actual location with actual results. See how the catalog of tests, and the actual tests you run, are different types of things, described by different data?

The problem is more complex than you have designed for, IMO.

 

[ej256]

You don't need the word "information" in the name of a table.

There are things, or 'objects' or 'entities,' in your verbal description that don't appear in your tables, like people, and sub-systems. I would expect there to be tables for that data too.

Also, is there is a fixed list of tests? If so then you need two tables for tests, one being an abstract list like a catalog, and the second being the actual tests that occur in time at an actual location with actual results. See how the catalog of tests, and the actual tests you run, are different types of things, described by different data?

The problem is more complex than you have designed for, IMO.

Here are all my relationships. I didn't include them originally because I thought it'd be easier to just see the two that are giving me issues. Please see attached. And there are not a "fixed" set of tests, but I believe I have what you describe in terms of my run information table being a "catalog" and my review table being the results. Also, the acronyms in my run information table are the domains I was speaking about.

 

[MarkK]

I receive the log of x and assign five people representing different subsystems (HW2, MIS, OP, SW2, SE, HW, SW) to review it.

This describes a one-to-many relationship, where one row in a master table is related to many rows in a child table. This relationship is not implemented in the table design you are showing us. It breaks normalization to have each of those sub-systems (child objects) in the same table, and in the same row, as the parent object.

Also, I don't see the point of linking to a table with only one field. The link itself IS the foreign key value, and since there are no other fields in the table, what value is added for all the overhead of creating the link? What is the point?

How is LastName a property of a Review? LastName is a property of a person.

Hope this helps,