Unanswered: When to use a field instead of a table?
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)
at the moment the fields in the reach and stream site tables are different, but the fields in the stream site table could easily apply to reach data and field in the reach table could apply to data in the stream site table.
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 ? is possible, could you give an example of when you could use a field instead of creating a separate table?
Going on your description I believe you have hit the nail on the head !
If the tables are essentially the same then yes they should be 1 table as the data can be easily seperated using a query based on that Varying field.