Delphi 2009, MyDAC and relational database

Question

Hello everyone!

I have quite a problem concerning the use of relational database concepts in Delphi 2009 with MyDAC.

I have a database structure that looks somehow like the following:

---

Item

• id
• name

---

Storage

• id
• name

---

StorageItem

• id
• item_id
• storage_id
• place

---

Now when I have an active dataset from "Item" how can I display all associated Storages in for example a DBGrid?

By the way: Would it be better to not use "id" in every table but to alter it and use something like for example "id_item" or "id_storage"?

Thank you in advance :)

Answer 1

I'm not familiar with MyDAC personally, but most dataset components have some way to establish master-detail relationships. Check if there's a MasterSource property on your dataset, or some similar way to link a detail dataset to a master dataset. If not, you could use a TDatasetField to establish a link, and filter the nested dataset to only display the right records.

As for ID column names, it's a good idea to give a descriptive name to each field, so you can tell by looking at the code that you've got your links right. If you call your id column "id", that could be any id column, and that could get confusing if you start passing around references to datasets. But if it's called item_id every time, (not item_id sometimes and id_item sometimes) then you always know exactly what you're looking at. It makes it easier to know that your code is right, too. A filter that says "master.item_id = detail.item_id" is easier to read that "master.id = detail.item_id". That could be wrong and fail silently if master is assigned to the wrong dataset, for example.

Answer 2

• With StorageItem you created a
  many-to-many relationship. If you
  need just one-to-many (many storages are related to one item, but you
  don't need the vice versa), then you may just add another field to the Storage table (item_id) that would be a foreign key for Items table. Then you create an index on item_id in Storage table, and connect the two tables in
  master-detail relationship.
• If you do need many-to-many then you may add a query component with SQL
  (select * from StorageItem where
  item_id := :current_storage_id), and current_storage_id is your query's
  parameter.

Answer 3

Select a.ID, b.Name, a.Place
from StorageItem a
inner join Storage b
on (a.id = b.id)

the above query will return all the items in StorageItem table with it's name, now if you want to filter it to return only items for a specific item add where clause to be like

Select a.ID, b.Name, a.Place
from StorageItem a
inner join Storage b
on (a.id = b.id)
where a.item_id = 1 -- place the item id here

you can use where with parameters such as:

MyQuery.Sql.Text := ' Select a.ID, b.Name, a.Place from StorageItem a
+ ' inner join Storage b on (a.id = b.id) '
+ ' where a.item_id = :ItemNo ';
MyQuery.ParamByName('ItemNo').asInteger := 1;
MyQuery.Open;

and assign the query above to dbGrid

also you can use MasterSource property to make the relations without using the "where" part

Answer 4

Thank you all for your recommendations. Mohammed's hint helped me the most, so I marked his as "the answer".

I wasn't aware that I had been so close to the solution when I posted this question :)