Separate tables or one loooong table?

[vjacobs]

Good afternoon all! I have a problem and believe I am over thinking it. :banghead:
I have five separate people I need updates from about a project, and they all need to pull specific information from the same table. Since each will be updating me on different aspects of the project, I was either thinking of putting everything in one long table and runny queries, or designing 6 different tables - which I can certainly do but am nervous about linking them together? Thank you so much for your help, I appreciate it.
Vivi

 

[spikepl]

This is a riddle, right? Without any specifics as to what data is required, hard to tell what the best structure is.

In any case - who is asking/updating info has seldom much influence on how the datastructure should be designed. In theory, at least.

 

[plog]

The correct answer is probably seperate tables, but not as many as you've proposed. If you want to post some sample data we can help you work through it to get the proper structure, but my hunch is that neither of your methods is correct.

 

[CraigDolphin]

More information would certainly help but there is some basic information in the post that can be used for speculation.

I see tables for:
People
PeopleID (auto, pk)
FirstName
LastName
etc

Projects
ProjectID (auto, pk)
ProjectName

Aspects
AspectID (auto, pk)
AspectName (Text)

I see a need for a junction table between Projects and Aspects that stores the resulting value/result(s):
Updates
UpdateID (auto, pk)
ProjectID (fk)
AspectID (fk)
UpdateContent (text/memo ?)

And there might also be a junction table to store information about which aspects certain people are allowed to populate
PeopleAspects
PeopleAspectID (auto, pk)
PeopleID (fk)
AspectID (fk)

[Or, alternatively, if a person might be allowed to enter different aspects on different projects, you might need to replace the PeopleAspects Table with two tables: one being a junction table between people and projects (PeopleProjects), and a second being a child table of the PeopleProjects table (called PeopleProjectAspects, for example).]

Whether these table names are the most appropriate, whether additional tables would be required, and what additional attribute fields would be needed in these tables is going to depend on additional information.

 

[vjacobs]

The correct answer is probably seperate tables, but not as many as you've proposed. If you want to post some sample data we can help you work through it to get the proper structure, but my hunch is that neither of your methods is correct.

Thank you for your response. Let me explain a little about what I am looking for.
I work at a factory type place and we have 5 different stages of a project. I would like to be able to pull up a particular project and see what it's process is at any given time. The only information they would all have in common is information that is pulled from an excel file like the name of the company, order number, etc.
I am used to simple databases and reports, and think I am over my head on this one, but I have purchased the access book and am willing to do whatever it takes to learn this one. Thank you for your help!
Vivi

 

[spikepl]

Without anything specific it is difficult to provide any specific advice.

The question is what information do you intend to record for each stage. Is the type of information the same or does it differ. With type I mean things like "Begin time", "End time", "Who is working on it", perhaps "Location" ... etc ... all common elements of all stages of a workflow. What data is held for one stage but not for other stages? What differs? If nothing differs then there is no point in a separate table for each stage.

 

[vjacobs]

The same questions will be asked of each person - The date, status, planned completion date, actual completion date, any problems they are having, and person reporting the information. Thanks! Vivi

 

[spikepl]

Well then. That is same type of information, each record just tagged with its provenance/stage. One table. But of course, for normalization, "persons" would probably be one table, "status" another, perhaps "stages" yet another, and only the keys would be stored in the main table.

Google database normalization.

 

[vjacobs]

Just to correct myself - that same information will be asked by all, but then each may be asked 2 or 3 different questions depending on their place in the process. I will google it now, thanks.
Vivi