Thread: Relational table methods

Hi guys, just curious about how you relate tables in SQL. I have just upgraded a very flat DB design to be relational. I use 3 tables for a relation in this system.

Lets say we have a selection of shops and a selection of fruit, we want to link fruit to the shops you can buy the fruit, I would do something like:

Table 1
index = shop_id, int
col1 = shop_name, varchar(100)

Table 2
index = fruit_id, int
col1 = fruit_name, varchar(100)

Table 3
index = relation_id, int (optional)
col1 = shop_id, int
col2 = fruit_id, int

I would then query table 3 and link table 1 and 2 using the id's

That works great for me although if we start adding extra conditions and relations it does start to get a little messy (say varieties of apples, or isles in the shops).

The question is, do you use this method? Are there better methods out there? mainly a curiosity thing...

ahh sweet, see I never actually studied relational table I just adopted that method when presented with a problem and have never checked if it is the best way to do it.

I meant messy when trying to nut it out in your head, no matter how neat you write it anything with multiple joins makes for complicated logic, but thats what makes our jobs interesting right?

I lay out my SQL code very similar to you, its good to confirm that im doing things right.

I agree with the previous responders, but thought I would add the following, since you are asking a pretty general question.

Prior to about 1970, many programmers were asking questions about whether a particular "method" was a good one. Than along came Dr. E. F. Codd, a mathematician at IBM, who developed the mathematical set theory of relational databases. Since then, it really is not a matter of rating one method against another. There is one fundamental way to design relational databases so that they will be guaranteed to work with SQL. Now that I've stated that so categorically, it's only fair to admit that practical considerations often drive experienced developers to modify the "theoretical" design under certain conditions. But that should be done only after a fully normalized structure has been evaluated and understood.

The way to think of a new database is to determine the scope of the data model, then specify the entities that you will represent, then their relationships and their attributes. If you always approach database design in this way, your task will be much easier and you will make intelligent decisions. Defining scope and entities can be a little "fuzzy" at times, but it forces you to consider all the ill-defined issues at the beginning of your project, not after you've invested a lot of effort into a design that turns out to be weak or even completely unworkable.

I recommend that one of the best investments of your time would be to read some tutorials on database normalization. There are lots of them online and in books.