The Virtues of SQL.

ron

ron

Member
Hi ...

After 15 years working with Progress DBs - I'm doing a course about relational databases and SQL. (Yep, I guess I have a masochistic streak in me.) Prior to this my sum total knowledge of SQL was that SELECT COUNT(*) FROM blablabla gave me a count of the records in a table!

The course has a lot to say about ER diagrams (although I failed to see all that much value in them). It also discusses creating tables, etc. But what quite surprised me was that the "rule" was that every relationship requires the existence of a table. That quite astonished me. Of course where there is a many:many relationship there must be an intermediate table of some kind. But also for 1:1 and 1:many?

For those who have - or do - work with Progress 4GL and also SQL - my question is: do you find that DB design is different when working with SQL vs Progress? I'm trying to understand why it seems unnecessarily complicated with SQL.

Cheers,
Ron.
 
Everything you have mentioned is basic DB design, irrespective of the language. This is not to say that there aren't a bunch of ABL applications out there that look like the authors never heard about good DB design, but the principles apply none the less.

The only real difference between ABL and SQL in this regard is that ABL is record oriented (mostly) while SQL is set oriented, although ABL has some more set oriented operations like PRESELECT and OPEN QUERY.
 
Maybe I didn't explain sufficiently ...

In the common situation of a customer order table (a header - say 'order') - and order line items (say 'lines') - I'm accustomed to there being just two tables. But the course in SQL I'm doing says there must be three tables ... with a 'has' table in-between. (It's reminiscent of CODASYL databases of the 1970s.) In other words not only does every entity in an ER diagram become a table - but so does every relationship. As I said previously - any many:many situation must have an intermediate table - but I (as yet) don't see why one is needed in the case of 1:1 or 1:many relationships.

Ron.
 
That kind of association table is only required when you have a many to many relationship. In a one to many relationship like order and order line, no such table is needed. That is just as true in SQL as in ABL or OpenEdge.
 
I think you are effectively confirming my own experience about the matter. But I assure you the (university) course I'm doing absolutely requires an intermediate table in-between every pair of entities that have a relationship. This extra table usually contains no data, as such, just the keys of the records in the tables of the connected entity-tables. The intermediate table does not seem to be at all nececcary for database navigation purposes - just to absolutely enforce referential integrity.

I'll put this question to the tutor and see what he has to say about the matter.
 
You must log in or register to reply here.
Back
Top