Basic Table Design

[gw73]

Hi
I am still new to access and have some basic questions. I have a table that I can set up two or three ways and I don't know which is proper. The first field is plot number which is a simple 4 diget text string that identifies a plot, the second is project number which identifies the project that the plot is associated with and the third is simply the year. I did it this way so the same plot number can be used for different projects or with the same project in different years so I don't have to keep seperate tables for each one. This works as long as make all three primary keys to keep from having duplicates. Would it be better to have seperate tables for each one or put them in one field and number them like xxxx.ppp.yyyy and when I querry them sort them out. The relation is one project can have many plots which can have only one year. Any ideas would be appreciated.


Thanks
GW73
PS. I won't be able to check this until tomorrow

 

[jjonesal]

I think I would have separate tables for Plot and Project....there are probably other attributes you keep track of for each plot and for each project. You wouldn't want to duplicate this information (which you would be doing by combining into one table.) I'm really going out on a limb by making a design suggestion when I don't know all the details but, my instinct tells me:

Plot
====
PlotID
other plot info

Project
=======
ProductID
other project info

PlotProject
===========
PlotID
ProjectID
Year
Information specific to this year's Plot/Project J. Jones
jjones@cybrtyme.com

 

[gw73]

Thanks for the idea, I didn't use your idea exactly, but it gave me an idea that solved my problem. These forums are the greatest thing around.

GW