Need help understanding table

[sueviolet]

Hello all,

I have been given an empty database that is in the process of being populated from various sources. Eventually the db will be used in a GIS and mapped.

My question is - could you help me understand how the following 3 tables work together. I am confused (escpecially with table 3) as how the information in entered and retrived.

Alright:


Table 1: Barriers

Fields:
Barrier_ID
UTM_zone
easting
northing
barrier_type (input from variety of sources)
comments


Table 2: Barrier_Type_Link

Barrier_id
Barrier_type_id


Table 3:

Barrier_type_id
Description (this fields lists 15 possible barriers [to chose from?])
Acronym (acronyms for the barriers)


How does table 3 relate logically to table 1?


I think i am most confused as the purpose of and the way table 3 is supposed to work (did not create db). It appears that it is a sort of look up table for data entry - (no forms have been created yet for this db) since it lists a selection of barriers a user could pick from. I don't understand this table.

Please help!

If you want, I could send you the tables

 

[cogent1]

These three tables establish a many-to-many link between BARRIERS and BARRIER TYPES.

It infers that ONE BARRIER can have MANY BARRIER TYPES (?) and that ONE BARRIER TYPE can appear on the details of MANY BARRIERS .


I would have thought (I may be wrong) that a Barrier is of one specific type. This would render Table2 redundant . You will need to clarify this point.

If so, then you need to include Barrier Type ID as a foreign key in your BARRIER table . This would then make a simple One-to-Many relationship between BARRIER TYPES and BARRIERS. In other words ONE BARRIER TYPE appears in the details of MANY BARRIERS.

The Barrier Type information only has to appear in your tables once, in the Barrier Type table. The Barrier Type ID which appears in Table 1 enables you to extract all the information about the Barrier Type that exists in Table 3, by linking the two tables together in a query that has a join between the Barrier Type ID fields. This is the essence of a relational database.

If there actually is a Many to Many relationship, then it's a different scenario, but I await your comments before going further.

The Field called Barrier type in table 1: how does that differ from the field called Description in Table 3? would that field be more logically placed in Table3. Impossible to know without more detail.

 

[sueviolet]

Comments

Thanks for your help



Yes, i think you are right, a barrier can only have one type (ie: you can't have a barrier that is a dam and a culvert)

So it looks like i need a one to many relationship instead.


Table 3 (barrier type) is a look up table - the description fields lists a set of possible barriers one could have.


You asked how the field 'barrier type' in table 1 differed from the field called "description' in table 3.


The field 'description' in table 1 lists 15 barrier types. I believe this is a lookup table.

The field" barrier type' in table 3 will contain a record of a particular barrier found at that location.

Does this information change your answer?

Any additional comments?

Thanks again

 

[cogent1]

"barrier Type" isn't a good description then. THAT should be renamed BarrierDescription. As your primary key field in Table 3 is called Barrier Type ID, it makes more sense to call its associated descriptor "BarrierType" instead of Description.

 

[sueviolet]

mistake in reply

My mistake:

The field 'description' in table 3 (not 1) lists 15 barrier types. I believe this is a lookup table.

The field" barrier type' in table 1 will contain a record of a particular barrier found at a particular location

Does that make a difference to your replies?