[SOLVED] Storing interests of multiple categories for multiple people
lazarus / 16-Databases

----------------------------------------------------------------------
Hi im Pascal · 2019-09-29 13:22

Hello,

Please, consider the following SQLite database scenario:

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Table 1:
Person(rowid) | first_name | etc... Table 2: Interest(rowid) |
person_id | category_id | description Table 3: Interest
Category(rowid) | description Note: The two "description" columns are
named poorly, they do not refer to the same thing.

The simplified goal is to store interests for various people. I tried
to design the tables according to what I remember reading/learning,
for example, in my mind I remembered storing just a "category_id" and
mapping that to the text-representation in another table would be
better practice than storing that same text directly inside the
interest table.

- Is this architecture any good for the task at hand, or could I
improve it to be more scalable/maintainable/... ?

I'm using a SQLite database inside Lazarus.

I hope my question is clear. Thank you in advance for any advice you
might be able to give me!

----------------------------------------------------------------------
lainz · 2019-09-29 15:08

Yes is good.

There is not a perfect model I think, it deppends on what you will do
with the data and how you need to retrieve it.

Your model will work for:
- Retrieving all the categories quickly
- Retrieving all interests by category
- Retrieving all interests by person

So is good if is that what you need.

----------------------------------------------------------------------
Hi im Pascal · 2019-09-29 17:15

Quote from: lainz on September 29, 2019, 05:08:44 pmYes is good.

There is not a perfect model I think, it deppends on what you will do
with the data and how you need to retrieve it.

Your model will work for:
- Retrieving all the categories quickly
- Retrieving all interests by category
- Retrieving all interests by person

So is good if is that what you need.

Thank you very much for your reply. In that case I have another open
question:

I have a ListBox with all persons and their respective rowid, now when
the users clicks the ListBox, I find the active item and want to
display all interests for the selected person (person_id). My current
approach is to first select all interests for a given person (I fill
the value with ParamByName()):

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT *
FROM interest WHERE person_id=:person_id;
Now I would like to display them inside a TMemo, maybe like this (the
exact format is not that important):
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Music:foobar------------------------------------------------
Sports:foobar------------------------------------------------
My problem: I need to map the interest_category (an integer) to the
actual description. Otherwise I would get something like this:
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 1:foobar
------------------------------------------------
2:foobar------------------------------------------------
The options I can currently think of:

- Somehow use another SQLQuery object and run another query while
iterating over the rows of the interest query (is this even possible?)
- Before querying the interest table, grab all categories and store
them in an array, then index that to find the category description
(problem: Can't assume rowid always will map directly to an index, e.
g. deleted categories would create gaps I think)
- Create a magical query which adds another column and automagically
adds the respective category text-representation (I have no idea if
this is possible...)

A more general question would be, given a design like I showed
(storing a numerical rowid for something and storing the text-
representation in a separate table) how does one retrieve this text-
representation when querying the table which only contains the
numerical rowid.

Here is my current code to better visualize the problem:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i if
LbPeopleForInterests.ItemIndex >= 0 then  begin    // This is how I
get the database row id for a person:    personId := PtrUInt(LbPeopleF
orInterests.Items.Objects[LbPeopleForInterests.ItemIndex]);     //
Query all interests for a specific person    SQLQuery1.SQL.Text :=
'SELECT * FROM interest WHERE person_id=:person_id;';   
SQLQuery1.ParamByName('person_id').AsInteger := personId;   
SQLQuery1.Open;    SQLQuery1.First;    MemoInterestDisplay.Clear;    
// Goal: Display all interests (Maybe I still need to ORDER BY
category_id):    while not SQLQuery1.EOF do      begin       
interestCategoryId := SQLQuery1['category_id'];         // >>>>> I
would prefer to have the text-representation at this point <<<<<     
 MemoInterestDisplay.Lines.Add(IntToStr(interestCategoryId));        
// Add the interest itself (not the category)       
MemoInterestDisplay.Lines.Add(SQLQuery1['description']);       
SQLQuery1.Next;      end;  SQLQuery1.Close;  SQLQuery1.Clear;end
Here is how I interface to the DB (in case this is relevant):
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SQLQuery1 
    : TSQLQueryDBConnection    : TSQLite3ConnectionSQLTransaction1 :
TSQLTransactionDataSource1     : TDataSource SQLQuery1.Database ->
DBConnectionSQLQuery1.Transaction ->
SQLTransaction1SQLTransaction1.Database ->
DBConnectionDataSource1.DataSet -> SQLQuery1DBConnection.Transaction
-> SQLTransaction1
This is a longer question, but I tried to make it as clear as
possible. Thanks in advance for any help!

----------------------------------------------------------------------
Zvoni · 2019-09-30 07:59

This is a "m:m"-scenario. You don'nt need the "Description"-Column in
Table Interest
What you need is a INNER JOIN
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
Description FROM Interest_Category AS ICINNER JOIN Interest As
IONIC.rowID=I.Category_IDWHERE I.person_id=:person_ID

----------------------------------------------------------------------
Hi im Pascal · 2019-09-30 11:13

QuoteThis is a "m:m"-scenario. What you need is a INNER JOINThank you!
That really helped me out, and I learned a lot from it!
QuoteYou don'nt need the "Description"-Column in Table InterestIn
hindsight, it was not a smart idea using "description" in two separate
tables, meaning two separate things (once, the interest "text" and
once the category "text"), but they are two separate things.

Based on your help, this is the query I ended up using:
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT I.*,
IC.description AS category FROM interest_category AS ICINNER JOIN
interest AS ION IC.rowid=I.category_idWHERE
I.person_id=:person_idORDER BY I.category_id;
And here is the full code for the ListBox MouseDown method:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.LbPeopleForInterestsMouseDown(Sender: TObject;Button:
TMouseButton; Shift: TShiftState; X, Y: Integer);var  personId:
Integer;  interestCategoryId: Integer;  currentCategoryId:
Integer;begin  personId := 0;  interestCategoryId := 0; 
currentCategoryId := 0;   if LbPeopleForInterests.ItemIndex <= 0 then 
  exit;   personId := PtrUInt(LbPeopleForInterests.Items.Objects[LbPeo
pleForInterests.ItemIndex]);  SQLQuery1.SQL.Text :=    'SELECT I.*,
IC.description AS category FROM interest_category AS IC ' +    'INNER
JOIN interest AS I ' +    'ON IC.rowid=I.category_id ' +    'WHERE
I.person_id=:person_id ' +    'ORDER BY I.category_id;'; 
SQLQuery1.ParamByName('person_id').AsInteger := personId; 
SQLQuery1.Open;  SQLQuery1.First;  MemoInterestDisplay.Clear;  while
not SQLQuery1.EOF do    begin      // Only output category once for
all interests in that category.      if SQLQuery1['category_id'] <>
currentCategoryId then        begin          if currentCategoryId <> 0
then // Don't add at the very beginning           
MemoInterestDisplay.Lines.Add('');         
MemoInterestDisplay.Lines.Add('=====  ' + SQLQuery1['category'] + '
 =====');          currentCategoryId := SQLQuery1['category_id'];    
  end;      MemoInterestDisplay.Lines.Add(SQLQuery1['description']); 
   SQLQuery1.Next;    end;  SQLQuery1.Close;  SQLQuery1.Clear;end;