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

[kmusser]

I know they exists, I'm pretty sure we even use some of them, I'd have to dig into documentation to tell you how to make them though.

[janewilliams20.livejournal.com]

I haven't used Access for years, but databases in general, yes, once or twice :)

Your boss is absolutely right. "Link table" isn't a phrase for it I've heard before, but it's a perfectly good description of what the table does. You don't want to have a limit on how many frogs per pond, or ponds per frog. You don't want to have to worry about removing frog 3 from pond 4 and shuffling frogs 4 and 5 up a space. You'll want to be able to look up where Frog Type 3 can be found. You want a nice third-normal-form database, you do.

I hope your tables have better names than this, but let's stick with what you've got, and add a few extra fields (I know nuffin about frogs and ponds, but I can invent stuff)

table_a
pond_ID (primary key)
pond_name
pond_depth

table_b
frog_id (primary key)
frog_name
frog_colour

link_table
frog_pond_id (PK)
pond_id
frog_id
spotted_by_user_id (same data type as the PK of your user list)
date_spotted
how_many_frogs (integer)
are_frogs_happy (boolean)

Now set up two relationships.
Link_table.pond_id is linked to table_a. pond_id
link_table.frog_id is linked to table_b.pond_id

Interfaces?
You need a screen to populate your list of ponds. Another one to populate your list of frogs. You've probably got both of those.

Now we need a new screen, called something like "I saw a frog today!"
Here, the user first picks a pond from a drop-down list.
Now they pick a frog from another drop-down list.
They fill in details about the date (default today), how many of this type of frog they saw, and whether or not they were happy. Press "save" to insert a record into your link table. Repeat for next frog.

So for Pond 3, what's been seen there?

select frog_name, frog_colour, date_spotted, how_many_frogs, are_frogs_happy
from table_a
inner join link_table on table_a.pond_id = link_table.pond_id
inner join table_b on link_table.frog_id = table_b.frog_id
where table_a.pond_id = 3
order by frog_name, date_spotted

Where do I go to find places where Frog 9 is happy?

select pond_name
from table_b
inner join link_table on link_table.frog_id = table_b.frog_id
inner join table_a on table_a.pond_id = link_table.pond_id
where table_b.frog_id = 9
and are_frogs_happy = "Y"
(I may have the syntax wrong here, it may be =1 or something)


Yes, I do rather enjoy database design. Why do you ask? :)

[winterbadger]

Thank you so much for the detailed reply! That's tremendously helpful, and once I'd read it through it made so much sense that I couldn't believe I was so dim as to not see it already.

I'm very grateful! :-)

[winterbadger]

Thanks for the comment, Karl! As soon I read through Jane's explanation, I realised I've seen a number of other examples, I just wasn't thinking of them/looking at them in the right way to make sens with what my boss was describing.

[janewilliams20.livejournal.com]

NP - it's another of those concepts that's simple and obvious once you already know it, and anything but beforehand :)

[kevinrtaylor.livejournal.com]

janewilliams20 is absolutely right.
I had to do something very similar to record contacts for morris dance groups.
Some have 1, some 2, some more than 2, and it's a waste of space having 3 or more different name/address/email fields in each group record.
My link table is much simpler, since all the detail is in the group/contact tables.
It just has 3 fields: LinkID, GroupID and ContactID.
However, I couldn't explain how to do it in Access because I work with mySQL.

tbl_transactions

[mikecosgrave.livejournal.com]

Obviously, I would call it a transactions table, since every time you see a frog in a pond it is a type of transaction, and you find transactions tables in every major database - every phone call you make or credit card payment uses one. I use the bill metaphor to explain master-client relationships and normalisation to my digital history class.

The query editor in Access does support SQL, and Access does allow queries to create tables so if you have good SQL out of MySQL, you can paste it into the SQL view in the Access query editor and execute it to create tables.

Mind you, the problem with Access databases is that a year down the road some fool moves them onto the server so several people can use them, and a year later some other fool scales it up from 5 users to 500 and then people begin to wonder why it takes 10 minutes to execute a query. I have seen middling size businesses run just fine on Access, but when it gets broke, it gets real broke. It is a handy desktop tool, and in some ways is it really handy for teaching but when students get out in the big bad world of MySQL or Oracle, they have to move up to using less user-friendly tools which many find hard to do.

Mike

Re: tbl_transactions

[winterbadger]

Yes, the resident information architecture person on our team is making sure that people for whom I produce the databases that I work on understand that if these are, effectively, just testbeds, and if they want a permanent, office-wide application, we have to build it (a) into the enterprise architecture and (b) using Oracle. I'm not up to that yet; it's been many years since I cut my programming teeth on Basic and Fortran, and I still need all of Access's little crutches for the moment.

[winterbadger]

Thanks!

"However, I couldn't explain how to do it in Access because I work with mySQL."

Indeed. I hope to learn some more advanced programming at some point, both for use in this job and because it will be handy downt he road.

Re: tbl_transactions

[janewilliams20.livejournal.com]

We tend to move up to SQL Server from Access: it's big enough for most purposes, and being an MS product, transfer is very easy.

But yes, the user departments write their cute little Access toys, no documentation, no design, no spec, maybe 15% of the code doesn't work but is left in anyway - and then they hand them to us to "fix". One of my jobs five or six years ago was taking something like this that had grown to have about half a million rows in each of the main tables and move it to Oracle: given the growth rate they expected, and the complication of the code that analysed the data, nothing less was going to do the job. As it was, their reports ran all night and then crashed. The spec was something like "make it work like this one would work, if it worked, which it doesn't".

Ah, well. If databases were easy, I'd be out of a job :)

[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....