How many tables? 2

[bernie10]

Hello all,
Some database design help please...

I'm creating an application to track problem products which are found. Here is the basic structure. A person finds a problem and fills out info on the problem into a table. At a later point the department which handles problems will fill out additional information on the problem. Finally after being reviewed by the department the orginal person who found the problem will enter some final information.

So basically my question is should I use three tables related together or just one big table? If three, then all tables would have the same number of rows with one, and only one, row in each column corresponding to one row in the other two columns. This is why I was wondering if it should all be done in one table. Still, it seems more intuitive to do it in three tables since data is entered in three stages.

Any thoughts?

Thanks,
Collen

 

[lespaul]

Entering and displaying data is completely separate from designing table structure. If your tables are structured correctly then the rest is easy enough to do.

I would first recommend that you read The Fundamentals of Relational Database Design

I would start off answering some of the following questions:

What kind of information do you track about the problem? Who reported it? Are there standard defects or problems that need to be identified? Do you need to track who resolves the problem? Could there be many people working on the same problem? Where does the information come from about those people? Do you need to be able to track the resolution of the problem? Are there standard resolutions that need to be identified?

Once you have those answered and have read the document, you should have a better idea of what kind of tables you need and how they should be structured.

HTH



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[bernie10]

Leslie,
Thanks for the response and for the reference to the document. I have read that document before and it was very helpful.

I suppose I wasn't clear on my question. Basically all I'm wondering is, in a case where data in entered in three stages (perhaps weeks or months apart), is it appropriate to use a single table (which would mean that perhaps half the fields for many records would be null since a problem may only have completed stage 1 or 2) or should several be used, one for each stage? I know in general that entering and displaying data is a seperate issue from table structure design, but I didn't know if this might be an exception since data is entered in stages. I wasn't sure if it was okay to leave required fields blank for a period of time while the problem goes through all three stages.

I understand that there are additional considerations as you mentioned and that I will have to account for them, but I'm just asking very generally if it is (ever) appropriate to use a single table for data which is entered in stages.

So for example suppose this is the data collected:

ProblemNumber (primary key)

Stage 1:
ProductNum
Comments

Stage 2:
Evaluator
Recommendation

Stage 3:
Conclusion

Have I made my question any more clear? Thanks so much for the help.

Collen

 

[LittleSmudge]

The TIME that the data is loaded into the database is totally irrelevant to the database structure.

If there will only EVER be one evaluation per identified problem then they belong in the same table.

If there will only EVER be one conclusion record per problem/evaluation then that too belongs in the same table.

The reason for having multiple table is to accommodate the situation where a single problem could give rise to [ none, one or many ] evaluations or to [ none, one or many conclusions ] etc.


'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[bernie10]

That was exactly what I needed to know... thanks for the help.

Collen