I have a problem I don't know how to handle.


I have a table called People

CREATE TABLE PEOPLE
(
People_ID INTEGER NOT NULL,
Fname VARCHAR( 24) COLLATE NONE,
LName VARCHAR( 24) COLLATE NONE,
}

and a table called PEOPLE_FLAGS

CREATE TABLE PEOPLE_FLAGS
(
PFlag_ID INTEGER NOT NULL,
Flag_Desc VARCHAR( 24) COLLATE NONE,
}


I'd like to associate various people_flag entries with each entry in
the people table. Usually, I'd create an intermediary table

CREATE TABLE PEOPLE_FLAGS_LINK
(
People_ID INTEGER NOT NULL,
Flag_ID INTEGER NOT NULL,
}

and just create entries in this table when the users associate
people_flags to each people entry.

However, the core of my application is a big list (a VCL TlistView)
which lists everyone from the PEOPLE table AND their PEOPLE_FLAGS.
How can I do this easily with the above tables?

Ideally, I'd like to do one simple select and retrieve one row for
each entry in the PEOPLE table, but I suspect that I'd need to do an
additional query for each person in order to get their PEOPLE_FLAG
entries. I want to avoid this for performance reasons.

I thought that I could do the following:

1. Do away with the PEOPLE_FLAGS_LINK table
2. Make the PEOPLE_FLAGS table contain a maximum of (eg) 30 entries
3. Add an integer array: PFlags INTEGER[30] to the PEOPLE table

With this, I could retrieve PEOPLE details and their associated flags
with a simple select.

Although this overcomes the problem, it doesn't seem right/correct/wise.

Can anyone offer any advice?

TIA