How does Master/Detail work?

Question

I have found some tutorials, but they still leave me with questions.

Let's take a classic example of 2 tables, one for customer details and one for order details.

The customers table in the database has:

• an autoincrementing integer customer_id as primary key
• a text field for customer name
• a text field for contact details

And the orders table has:

• an integer customer_id which is a foreign key referencing the customers table
• some other stuff, such a reference to a bunch of item numbers
• an integer order_value to store the cash value of the order

I need two dataset components, two queries and a connection.

So far, so good? Or did I miss something already?

Now, the tutorials say that I have to set the MasterSource of the of the datasource which coresponds to the DB grid showing the orders table to be the datasource which corresponds to the customers table and the MasterFields, in this case, to customer_id.

Anything else? Should I for instance set the Detailfields of the query of the datasource which corresponds to the customers table to customer_id?

Should I use the properties, or a paramaterized query?

Ok, at this point, we have followed the classic tutorials and can scroll through the customers DB grid and see all orders for the current customer shown in the orders DB grid. When the user clicks the customers DB grid I have to Close(); then Open(); the orders query to refresh its corresponding DB grid.

However, those tutorials always seem to posit a static database with existing contents which never change.

When I asked anothter question, I gave an example where I was using a Command to INSERT INTO orders... and was told that that is A Bad Thing` and I should:

• OrdersQuery.Append();
• OrdersQuery.FieldByName('customer_id') := [some value]';
• OrdersQuery.FieldByName('item_numbers') := [some value]';
• OrdersQuery.FieldByName('order_value') := [some value]';
• OrdersQuery.Post();

Is that correct?

I ask because it seems to me that a Command puts data in and a query should only take it out, but I can see that a command doesn't have linkage to the DB grid via a datasource's query.

Is this a matter of choice, or must the query be used?

If so, it seems that I can't use even simple SQL functions such as SUM, MIN< AVG, MAX in the query and have to move those into my code.

If I must use the query, how do I implement SQL UPDATE and DROP?

And, finally, can I have a Master/Detail/Detail relationship?

Let's say I want a 3rd DB grid, which shows the total and average of all orders for a customer. It gets its data from the orders table (but can't use SUM and AVG) which is updated each time the user selects a different customer, thus giving a Master/Detail/Detail relationship. DO I just set that up as two Master/Detail relationships? I.E, the DB grid, datasource, query for the total and average orders refers only to orders and has no reference to customers, even if it does use customer_id?

Thanks in advance for any help and clarification. I hope that this question will become a reference for others in the future (so, feel free to edit it).

Answer 1

TLDR: In the SQL world, Master/Detail is an archaism.

When some people say "Master Detail" they aren't going to go all the way down the rabbit hole. Your question suggests you do want to. I'd like to share a few things that I think are helpful, but I don't see that anyone can really answer your questions completely.

1. A minimal implementation of master detail, for any two datasets, for some people's purposes, is nothing more than an event handler firing when the currently selected row in the master table changes. This row is then used to filter the rows in the detail table dataset, so that only the rows that match the primary key of the master row are visible. This is done for you, if you configure it properly, in most of the TTable-like objects in Delphi's VCL, but even Datasets that do not explicitly support master/detail configurations can be made to function this way, if you are willing to write a few event handlers, and filter data.

2. At one of my former employers, a person had invented a Master Detail controller component, which along with a little known variant of ADO-components for Delphi known as Kamiak, and it had some properties which people who are only familiar with the BDE-TTable era concept of master detail would not have expected. It was a very clever bit of work, it had the following features:

   • You could create an ADO recordset and hold it in memory, and then as a batch, write a series of detail rows, all at once, if and only if the master row was to be stored to the disk.
   • You could nest these master-detail relationships to almost arbitrary depths, so you could have master, detail and sub-detail records. Batch updates were used for UPDATES, to answer that part of your question. To handle updates you need to either roll your own ORM or Recordset layer, or use a pre-built caching/recordset layer. There are many options, from ADO, to the various ORM-like components for Delphi, or even something involving client-datasets or a briefcase model with data pumps.
   • You could modify and post data into an in-memory staging area, and flush all the master and detail rows at once, or abandon them. This allowed a nearly object-relational level of persistence management.

As lovely as the roll-your-own-ORM approach seems above, it was not without it's dark side. Strange bugs in the system lead me to never want to ever use such an approach again. I do not wish to overstate things, but can I humbly suggest that there is such a thing as going too far down the master-detail rabbit-hole? Don't go there. or if you do, realize that you're really building a mini ORM, and be prepared to do the work, which should include a pretty solid set of unit tests and integration tests. Even then, be aware that you might discover some pretty strange corner cases, and might find that a few really wicked bugs are lurking in your beautiful ORM/MasterDetail thing.

As far as inserts go, that of course depends on whether you are a builder, or a user. A person who is content to build atop whatever Table classes are in the VCL and who never wants to dirty their hands with SQL is going to think your approach is wrong-headed if you are not afraid of SQL. I wonder how that person is going to deal with auto-assigned identity primary keys, though. I store a person record in a table, and immediately I need to fetch back that person's newly assigned ID, which is an integer, and I am going to use that integer primary key now, to associate my detail rows with the master row, and the detail rows, therefore refer to the master row's ID integer, as a foreign key, because my SQL database is nicely constructed, with referential integrity constraints, and because I've thought about all this in advance and don't want to do this over and over again repeatedly, I eventually get from here, to building an object-relational-mapping framework. I hope you can see how your many questions have many possible answers, answers which have lead to hundreds or millions of possible approaches, and there is no one right one. I happen to be a disbeliever in ORMs, and I think the safe place to get off this crazy train is before you get on it. I hand code my SQL, and I hand code my business objects, and I don't use any fancy Master Detail or ORM stuff. You, however, may choose to do as you like.

What I would have implemented as "master detail" in the BDE/dBase/flat-file era, I now simply implement as a query for master rows, and a second query for detail rows, and when the master row changes, I refresh the detail rows queries, and I do not use the MasterSource or related Master/Detail properties in the the TTable-objects at all.