Subject Re: [IBO] info on database design
Author Nando Dessena
sorry for not replying earlier.

> > You still have to resort to joins and subselects to get descriptions for
> > the codes. You can of course use lookup fields tied to different
> > datasets (tables), it just seems to me a strange approach to development
> > with a relational database when it's me (the client) who has to build
> > the relations. ;-).
> This is just a part of developing a user interface. You have to have a table
> of the lookup values with the keys if the user is going to look through the
> meaningful values and the key is what is going to be plugged in as a result
> of their choice. I fail to see where an actual JOIN is useful in this
> process other than at the reporting side of things, but that isn't what I am
> talking about here. I'm talking about interactive GUI apps for data entry
> and such.

I think we basically agree but we are simply different ideas about the
user interface.
I am used to have browsing forms which are what users work with most of
their time. These only need joins and subselect, not the overhead of
opening additional lookup datasets. Data entry is done in separate forms
which do have lookups but they are usually filtered. Incidentally, the
sample app. I have sent you for other reasons, which has a double
KeyLinks/MasterLinks relation, is an example of what I mean. This
accounts for selection (limiting the number of rows to show in a lookup
list); I may need projection as well. My lookup tables usually have a
code and a description, but more often than not they have also
additional fields which are not needed for a lookup list (reporting
sequence numbers, business flags and settings, username and date/time of
last modification being examples of this additional data).
That's why I never even think of using a table component; I can see the
usefulness of a table component, mostly for prototypes and simple data
entry forms, but since a query does whatever a table can do and then
some, I just forget the latter. My mistake is perhaps that I suggest
others to do the same. :-)

> I don't see your point of view here. I am not suggesting anyone do anything
> that is a classic relational "no-no". Relational database means we store
> things in relations. This is one of the basic building blocks of any good
> relational system. There is nothing wrong with an application being designed
> to directly use relations at their basic level. Perhaps you can elaborate on
> this if you find a spare minute or two.

Perhaps I have stressed thing too much.
I meant something on the lines of: a good thing in the relational world
is applying projection and selection techniques to limit the resultsets
as much as it's possible. While a table component, if smart enough like
TIBOTable or even, in some cases, the good ol' BDE TTable, can help me a
lot with selection (open additional cursors when I scroll to the end,
and such), it can't with projection. I have learned years ago that
selecting all columns of an entity and no columns from joined entities
must be regarded as suspicious. I may well be out of time - tools were
less advanced just a few years ago.

I hope I have made myself a bit more clear now; I don't think we
disagree on any fundamental aspects. Perhaps on the finer details (like
the use of data modules :-)).

> We are saying the same thing. Just because someone is putting a LookupField
> on a table so they get a nice pick list in their DBGrid doesn't mean they
> are or aren't putting business rules on the server or the client.

OK. I just think that picklist is generally best served by a query
component than by a table. But again I don't think it's fundamental.

> Thanks for your insights. I think this little bouncing back and fourth is a
> good way to open people's views to some of the questions they may be having
> about how to approach the design of their applications.

Thatps one of the things I like of this list (the others being prompt
replies and competent people - one I sometime dislike is the tendency to
slip OT from time to time, BTW, but when I get that ADSL connection I
think I won't pay attention to that. :-)).

Thanks for your post.