database programming/design question

[winterbadger]

Anyone familiar with database applications, particularly MS Access, please read on; others pass by .

I'm working on a database that has Table A, a list of ponds, and Table B, a list of types of frogs. I need to set it up so that users can associate different types of frogs with each pond that they catalogue.

Now, to me, that tells me that I have to know how many types of frogs could be associated with any one pond (call that number N), so that I can set up data fields Frog Type 1 through Frog Type N in the Pond Table.

My boss says, "No, no, just set up a 'link table' that records however many associations the user wants to make. You shouldn't have to set up all those data fields in the Pond Table--that's messy."

I've never encountered such a thing, which either means (a) it doesn't exist or (b) seeing as I've only been doing this for a few years, it's just something I've not yet learned about.

Does anyone (who's made it this far, which may be no one) know how one would create such a table, especially in MS Access?

Comments

[janewilliams20.livejournal.com]

Yes, most examples I know of have next to nothing in the link table: in fact, just frog_id and pond_id, no link_id, would be acceptable. You then use the two together as your primary key.

This was a nice example, I felt, simply because it shows how you can add extra useful information into your linking record. Without that, it can look a bit useless.

And in fact I didn't explain how to do it in Access: I can't, because like you, I don't use it. The SQL queries I've written should work in just about any standard database, though, and I believe Access, like SQL Server, will accept them into its editor and then draw the pretty diagrams based on that. Unless, of course, it decides they're too complicated for its tiny mind....