tables set-up and relationships

[MJ]

help with this ??

I am not sure how i would set up the table structures and
confused about what kind of referential integrity would be
enforced.

i think i need four tables titled process, document,
people, year - with many-to-many relationship between all
of them

contents of the tables - each table would have 1 field:
process - process 1, process 2, process 3 etc.
document - document 11, document 11.45, document 14.51T
etc.
people- mary, jane, joe, howard, etc
year - 1999, 1997, 1951 and so on...

Such that process 1 could have 10 people, no document and
4 years affiliated to it for e.g.

Also if I were to type 1999 then all the processes, people
and documents in that year should come up. How would I
search?

Thank you so much

 

[Danny J. Lesandrini]

I might not have read your description close enough, but I stopped
when I got to the part where you think you want 4 tables with only
one field each. Doesn't sound right.

Why can't you have one table with 4 fields?

tbl
| process | document | person | year |
1 11.45 Dan 2003
1 14.51 Dan 2003
2 14.51 Dan 2004
1 14.51 Jill 2003
1 14.51 Jill 2004
1 14.51 Dan 2003


This is intuitive and easily searched.

 

[MJ]

Thanks for your response. I know what you mean but what I
want to do is:

I want a many to many relationship. I am not sure how to
do this.

 

[Tim Ferguson]

i think i need four tables titled process, document,
people, year - with many-to-many relationship between all
of them

contents of the tables - each table would have 1 field:

We seem to have gone straight from the problem (unexplained) to the
solution. Back to basics: what is a process, and what is a document and
what is a person and what is a year. What is the nature of the relationship
between (for example) processes and document -- does a document describe a
project (or group of projects) or is it a product of the project? Is one
person responsible for a document, or is there shared authorship -- or are
you more interested in the delivery and/ or receipt of the documents?

Such that process 1 could have 10 people, no document and
4 years affiliated to it for e.g.

But are those people "had" by more than one project -- and what does
"having" mean anyway (see above). Database design is all about semantics --
the successful designer is one who can tease the actual meaning of
relationships out of the real world and recreate it in the model.
"Affiliated to" means nothing to anybody except you at this stage.

HTH


Tim F