Need help developing a small relational db

June 6, 2007 9:52 AM

Help me properly set up a very small relational db. This is a teensy bit over my head, so mainly I want to establish that I'm not going terribly wrong before I get too far into this.

The data is organized as follows:

One location -> many goals
One goal - > many projects
Each project -> many criteria (the criteria are the same across all projects)

Different locations can have the same goals, and I guess this is where I'm getting stuck. I have a LocationID table, and a GoalID table. Do I add fields for multiple goals in the location table? Or do I add fields for multiple locations in the goal table? Also, various locations can have the same type of project, so I have the same dilemma with the ProjectID table.

I have Access 2003. I cannot buy or learn a new DB program right now.

Thanks MeFi!

posted by desjardins to Computers & Internet (5 answers total)

If I'm reading you correctly, you want to add a field for LocationID in your Goals table. This is how you key goals to locations. Likewise, you want a field for GoalID in your projects table.

Any time you see your self adding columns like "Goal1", "Goal2", "Goal3"... you've likely made a miscalculation. For further research, google "primary key" and "foreign key".
posted by Nahum Tate at 9:56 AM on June 6, 2007

Response by poster: here's the current relationship diagram for you visual thinkers.
posted by desjardins at 9:59 AM on June 6, 2007

Ok so, one location has many goals, and many locations can have the same goal.

This is a "many to many" relationship and the way you solve this is to create a third table. In this case, I would create a table called something like "Location_Goals".

LocationId and GoalId together make up the primary key for the table and you probably won't need any other fields here. In your diagram, this table would go between the location and goal tables and each field is a foreign key to the respective table.

This way, you can select all the goals for a location or all the locations that a specific goal is assigned to.
posted by utsutsu at 10:05 AM on June 6, 2007

Having designed a bunch of DBs, all I will say is this: most smallish databases don't need to be relational at all. Flat file will do. In fact, spreadsheets (which are the same thing) will do for the most part. Simpler is better.
posted by MarshallPoe at 10:17 AM on June 6, 2007

Oops. Your diagram (and re-reading your description) make it clear that utsutsu is correct. What you want is sometimes called a 'junction table', and its purpose is to turn a many-to-many relationship into two one-to-many relationships.

I'd chop the tbl_GoalID and tbl_goals as they currently exist. Then make a new tbl_goals with just goalID and goal. Then make a tbl_location_goals with goalID and LocationID columns.

It looks like your measures tables are all suffering the same malady, and could use the same remedy.
posted by Nahum Tate at 10:26 AM on June 6, 2007