When should tables become a field? (1 Viewer)

[sueviolet]

Hello there,


Need some advice, before I change things around


I'm not really happy with the current design of my database. I have two separate tables - one called "stream site data" and one called "reach data."

Reach data refers to data about a homogenous segment of a stream, stream site data refers to data about a particular point on a stream.

These two table both link to several other tables. (ie: both stream site and reach tables link to riparian and physical data tables)

The thing is, the fields in the reach and stream site tables aren't that shockingly different from each other.


My idea: to make one table (combine the fields from the stream and reach table) and in this new table create a new field called "type" in which users could specify if they are recording data about a reach or a stream.


Is this a bad or good idea ? your comments would be greatly appreciated.

 

[glynch]

That could work, but one important question to ask is if you want to relate a stream site to a stream reach. I would guess that one reach contains many stream sites. If you want to track which stream reach contains which stream sites then you will need to have a field to hold that relationship.

Another question might be whether one stream reach can contain several smaller stream reaches which may each contain several stream sites. Then you are getting into a component hierarchy, which gets a little complicated.

 

[sueviolet]

Thanks for your reply


Could you elaborate more on your first paragraph ("will need to have a field to hold that relationship") Could you explain how this could be set up? Could you perhaps explain the rational behind creating a field instead of a table?

No, one stream reach cannot contain several smaller stream reaches.


In theory, one reach can contains many stream sites.


Thanks for your help

 

[Pathetique]

you could also keep the two tables that you have and create a query that gets all the records from both tables

path

 

[Pathetique]

on second thought, I think that you should just have one table. Once you make sure that both tables have the same fields you can copy and paste records. Then, you can manipulate the data the way you want. You could then have a field for the type of data shown as a combo box with a value list containing the two choices you want.

path

 

[glynch]

You could create what is called a recursive relationship within one table. It duplicates the one-to-many relationship that is common across two tables.

In your case it would work like this:

1. Combine your reach and site tables
2. If you don't already have a numeric primary key field (LocationID), add one
3. Add a ParentReachID field (long integer)
4. Any stream site that is contained within a reach would have the LocationID of the reach where that site is located as the ParentReachID for that site record.
5. You need to set up some rules in the application to enforce a relationship and avoid creating orphans, but that is true of any one-to-many relationship.

I hope this explanation was clear enough.