Here's a hypothetical (simplified) database structure:
Members (Table)
MemberID - Primary Key, type GUID
MemberName - type Varchar(128)
Age - type Integer
Status - type SmallInt (Single, Married, Widow, etc)
Hobbies (Table)
HobbyID - Primary Key, type Integer
HobbyName - type Varchar(256)
HobbyType - type SmallInt (Indoors, Outdoors)
MembersHobbies (Table)
MemberID - Foreign Key, type GUID
HobbyID - Foreign Key, type Integer
Now, each Member can have from 0 up to 3 hobbies in the MembersHobbies table.
1. Sounds simple enough, but here's the first obstacle... the resulting query (will be later displayed in a ListView) needs to be in this format:
MemberID MemberName Age Status Hobby1 Hobby2 Hobby3
--------------------------
----------
----------
----------
----------
----------
-
if the member has 1 hobby, Hobby2 and 3 will have the value "-". If the member has 0 hobby, all the 3 Hobby columns will be a dash "-".
Well, since there is a max. limit of 3 columns, I was thinking of making use of Calculated fields, so that I will do an external query for each of the Member row.
2. However, here comes the 2nd obstacle...
Calculated fields is fine, as long as my searching part of the programme allows users to only search on criterias such as Age and Status. However, part of the search criteria would also involve filtering by Hobby.
e.g. the user can ask for something like "Show me records of everyone aged between 20 and 25 with 1 or more hobby that is of type Outdoor"... or ... "Show me records of all <30 year old singles with Fishing as one of their hobbies".
The result will still need to be in the above format, and all (up to 3) hobbies of members who match the given criteria needs to be displayed, e.g. for the above criteria, the result might be
SomeGUID Alex 20 Fishing Reading -
SomeGUID Meikl 22 Programming Fishing Jogging
Do hope someone can shed some light on how to go about tackling this without compromising on the performance (calculated fields can be very very tedious and time-consuming, on large databases). And if it's of any use, I'm using FireBird 1.5 with the IBObjects 4.x components.
DragonSlayer.
