Impossible Table Design?

[mmiers]

Hi all!

I'm having a problem finding a way to design my database. I'm making a database for the quality assurance dept. at a local packaging plant. They want a database to store all of their test data.

As of right now, there are approximately 36 different tests and there may be an unknown number of additional tables in the future. Each test requires multiple rows. All of these tests obviously relate to a single "job" table that holds general information about the testing session.

Another big issue is that each test requires some static information about that particular test. Such as required specifications, test method and frequency of testing. I should note that about 90% of these info tables are exactly the same. I decided to split all these tables because I want the option of adding static fields to these tests. Although if there is an alternate way that's better, I'm all ears.

Before the jobs table is the usual cutomers, suppliers and products table. Here is my current basic relationship design.

Customers,Suppliers,Products 1-M> Jobs 1-1> TestInfo 1-M> Tests

Now then, I was wondering if someone could tell me how I can efficiently design the tables. There are about 3 different reports that call different tests for different customers and all that stuff. Obviously, there may be more reports in the future.

I have been toying with the design for almost a month now and I'm getting desperate. I thought my current system would work but queries are slow (i attribute this to a text primary key instead of an autonumber I was using which I will be changing soon but i'm not 100%) and I ran into the jobs table only being able to have 32 indexes (due to all the related tables). Obviously, I need all the tables below jobs to cascade delete.


Phew. Sorry about the length but I wanted to be as detailed as possible to avoid confusion. Please, any advice would be very helpful. I know there has to be a more efficient way to do this. This isn't a ridiculous database concept so there must be a feature in Access I'm missing or something.

Thanks in advance!
- Mark

 

[llkhoutx]

Your start appears to be in the right direction with one kind of information of in a table. I suspect you tables are not related and indexed correctly.

Each table should have a PrimaryKey, almost always a autonumber. Tables are linked by the primary key. Tables aalso have foreigh keys to link to other tables, also indexed.

Be sure that each linking field is indexed, that may be causing your queries to slow down.

In the attached illustration, all tables have an autonumber ID and and JobSuppliers have a junction table, because a Job can have multiple suppliers. Similarly a Job can have multiple Tests, hence a JunctionJobTest table.

Look at the relationships window of the attached 97 data base and the design veiw of the tables, especially the indexing.

 

[mmiers]

Wow, thanks for the detailed reply.

I have a bit of experience in access and I'm pretty sure that the problem can't be seen in the relationships window.

I posted a modified 2000 version of your relationships that is very similar to my current setup. Basically, just imagine about 35 more tests and testinfo tables.

Thanks again for the help.
- Mark

 

[mmiers]

Oops, here's the file.

 

[llkhoutx]

Your relationships look great.

Sometimes when you have a complex query that runs slow, breaking it into multiple queries dramatically improves performance. Many simple queries usually run faster than one complex one.

 

[mmiers]

Okay. Right now I need my query to calculate the avg, min, max and range for each test required for that particular report. How about having 1 query perform all necessary aggregate functions, then the main query pull all the right data? Will that increase performance or should I break it down further?

- Mark

 

[mmiers]

One other thing. I've read some other articles and forum posts that implied that a good table design should not have a table max out the number of indexes by having 32+ relationships. Is there something else I should be doing or is this a special case?

As of right now I would need to have tables between tblJobs and tblTestInfo that splits up the number of relationships.

- Mark

 

[llkhoutx]

I think those function calls are killing you.

Separating them will help. If that doesn't make increase the speed significantly, I'd try a separate query for each function call. Then join then in one query for later use.

I have a form that I run 79 queries on each form requery. The hourglass stays on only a second or two.

 

[Pat Hartman]

Sorry but your design is incorrect. You are creating a maintenance nightmare by having separate tables for each different type of test. Relational databases are very different from spreadsheets. Do some reading on relational design.

You do not need multiple testInfo and test tables. You need one of each with one additional field, test type. I modified your structure, corrected your column names, and removed extraneous indexes and columns.

 

[mmiers]

Ah yes, I thought this might happen. I made a mistake on my sample database and forgot to mention it.

Only about 30 out of 35 of my testinfo tables have the same fields and only 10 out of 35 of my test tables have the same fields.

I've been designing in access for nearly 5 years now and although this particular design dilemma has never come up, I do feel I have enough knowledge of relational databases. This is not my first attempt with this databases, I have tried many different ways including having all the testinfo data in one giant jobs table.

The main issue with this database is that once it's done it's not finished. Meaning new tests and changed fields can come up on a weekly basis. Although many of the tests and testinfo tables are similar, it is much quicker to add new tests and much less hassle than most alternatives.

Your redesigned database would be great if every test and testinfo table were the same.

Thanks for the help though!
- Mark

 

[Pat Hartman]

You don't say what you need to do with the data once it is stored. If you need to create reports that draw from multiple test types, you are creating a maintenance nightmare since every time you add a new test you'll need to change relevant queries/forms/reports.

It is possible to make a table that stores its fields as rows rather than columns. This is not recommended since it can be difficult to work with but if the alternative is constant changes to the db then perhaps you should consider it. The table would look like:

TestID (autonumber pk)
TestInfoID (foreign key to TestInfo table)
FieldID (foreign key to FieldNames table)
FieldValue (numeric if all your values will be numeric, otherwise text)

You can then select the rows for any TestInfoID and make a crosstab that produces a recordset that looks like what your current tables look like.

 

[mmiers]

Very nice idea!

I did however fix my problem. llkhoutx's suggestion of splitting the queries did the trick. My large database and several complex formulas are retrieved and claculated in under 1 second after changing the primary keys around.

I actually feel my current table design works the best for maintenance. Having the multiple tables lets me immediately be where I need to be and is much easier to understand. It's nice knowing I have full control over every detail, yet it is still fairly simple.

Besides, I get paid for any maintenance I have to do anyway.

Thanks again for all the help!
- Mark

 

[llkhoutx]

Glad my suggestion worked for you.