2. PHPRunner General questions
  3. master/detail relationship in 1 table
This topic is locked

master/detail relationship in 1 table

 		10/10/2008 9:41:53 PM
PHPRunner General questions
P
 promanron author

Hi All,

I am evaluating PHPRunner, and so far, it seems to be a great product. Having worked thru many of the simpler tests, I now come upon a more difficult (at least for me) topic, which is working with master detail records. Let me explain...
I am working with the latest version of PHPRunner, and mySQL 4.1
I have a single table that contains order records, with the following (abbreviated) structure...
id, orderNbr, firstName, lastName, lineNbr, partNbr, desc, qty
an order can contain multiple line items. if there are 3 line items, there will be 3 records in the table.
The orderNbr, firstName and lastName will repeat in each record, for each line item on the order.
The lineNbr, partNbr, desc and qty are unique to each line item.
Here is an example...
123 Fred Flintstone 1 ABC123 Big Part 23

124 Fred Flintstone 2 DEF321 Engine 1

125 Fred Flintstone 3 ZZZZZ9 Wheel 8

126 Barney Rubble 1 ABC123 Big Part 4

127 Barney Rubble 2 YYYYY8 Rock Hammer 2
I would like to develop a page (or maybe pages), where the user is presented with the 'header' information for an order, then can edit the individual line items, all displayed in a grid. I want to allow the user to edit the header information, with the final result that all of the associated detail records get updated, too. The addition of a detail record should result in the new detail record being updated with the header information, too. Deleting records should result in all associated order records being deleted.
I really like the demo where there was a pop-up window where the users could see detail records associated with the header record being viewed.
I am open to any suggestions, ideas or comments!!
thanx,

ron
Sergey Kornilov admin 10/11/2008

Ron,
what you looking for is basically master-details relationship between Orders and OrderDetails tables. You can an example at http://livedemo.asprunner.net/phprunner/li...asterkey1=10249
Once you enable Inline Add/Edit functions on OrderDetails table you can add/edit individual lines.
I would not recommend to repeat orderNbr, firstName and lastName in each order line. orderNbr is quite enough to link order header and order lines while you can display firstName and lastName using JOIN SQL query. Good database design guidelines say you don't want to duplicate info.
P
 		promanron author 10/11/2008

Ron,

what you looking for is basically master-details relationship between Orders and OrderDetails tables. You can an example at http://livedemo.asprunner.net/phprunner/li...asterkey1=10249
Once you enable Inline Add/Edit functions on OrderDetails table you can add/edit individual lines.
I would not recommend to repeat orderNbr, firstName and lastName in each order line. orderNbr is quite enough to link order header and order lines while you can display firstName and lastName using JOIN SQL query. Good database design guidelines say you don't want to duplicate info.


Hi Sergey,

Thanx for the quick reply.
Being a life-long database programmer, I understand the problems with duplicate data, and don't like this structure, either. Unfortunately, this database has been constructed to support the importation of orders from a wide selection of sources, including emails, web pages, other databases, flat files, etc. It has to support the lowest common denominator of the capabilities of these sources. Thus, the replication of header data on each line item. Therefore, I gotta work with what I got!
I will look at the example you provided. One of the approaches I was thinking of was to do the initial selection of records using either a DISTINCT or GROUP BY selection in the sql. Then, display only the common header fields in the grid. Then, add code so that selecting the 'edit' button, or creating a 'detail' link, would select and display the associated line item records. Of course, I would also have to add code to handle the updating of the common fields between all line items. In any event, I am stuck using this database structure, with all data in 1 table, and all common data repeated for each line item.
thanx for your help!!

ron
P
 		promanron author 10/11/2008

Hi All,

I have come up with some answers to my original question...which was, having both header and detail info in same table, same record...how to show/edit header and detail records. (Please see previous post)
I created a project.

I added the original table (which we can call header_table). I added a custom view of the same table (and called it the detail_table).
For the header table, I modified the sql to do a GROUP BY orderNbr, ORDER BY orderNbr, lineNbr... which resulted in records selected but only shows the first record in the order.
I modified the fields for the header_table to only show the header fields. I modified the detail_table to only show the detail fields.
I dragged the orderNbr field from the header_table to the detail_table, making a master/detail relationship.
Final result...the first record of each order appears in the header_table view. If I click the detail link, the header fields appear on the detail page, then the individual records (line items) for the entire order appear in the detail_table view.
I still have a lot of work to do...make sure that changes to header record get populated to all of the detail records. Make sure that additions of new detail records require a new, unique and sequential line nbr. Apply business rules to other fields. Control cleanup if header record is deleted, etc.
Maybe this solution will help someone else.
ron