Workflow for normalized tables

[perci100]

Lets say I have 2 tables

Patient table

patientID
fname

lname

address table

addressID
address1
address2
city
state
zip

Ideally id like to be able to view and edit all of these fields together just as if it was the list view or edit view of a main table with the options dropdown etc.

Right now I have 200 fields in a table that desperately needs to be normalized.

I read something about creating a view joining the additional tables and then using SQL in a custom field to call that view? does that also work for edit?

[kirondedshem]

Right now I have 200 fields in a table that desperately needs to be normalized.

If you mean you have a table having 200 fields and yet you see some of those fields can have their own tables and you just store links to those tables in the main table. eg lets say the table is keeping employee information in one huge table including biodata, address information, employment information etc etc. Yet we can have a table for address information, a table for employment information etc etc and just keep the foreign key of the employee in each of them.
Solution RECOMENDED,phpmaker is much more usefull if used with a properly thoughout database so If you are up for it. This aprocah is more permanent as it solves the problem once and for all.
1.I suggest you look at the existing 200 fileds of you table and first recreate the table in a normalised way by looking at which of those fileds can be grouped together and come up with new several tables table linking to one main table.

1. Then create a php script to move all records from the old 200 filed table into the new normalised format. by inserting into the other tables and creating the links to the main table etc etc

I read something about creating a view joining the additional tables and then using SQL in a custom field to call that view? does that also work for edit?
Yes as long as the view contains a primary key field and all other fileds in it belong to the same table then it can be used in an edit and add form as well and it will still function the same as when you use its source table.

[mobhar]

perci100 wrote:
then using SQL in a custom field to call that view? does that also work for edit?

Please note that "A Custom Field is not an actual field in the database table/view so input value will NEVER be inserted/updated to the database.". You can read about this from "Custom Fields" topic from PHPMaker Help menu.

However, you can still join some tables by using a Database View, and then use "UpdateTable" property that belongs to the table/page class to define into which table you want to save the main data from that Database View. You need to exclude the fields that are not part of the main table by using unsert() PHP function. Those excluded fields can be managed by your PHP code to insert/update to each related table using ew_Execute() function. For this purpose, then you need to put that code in "Row_Updating" server event.

For your reference how to manage CRUD by using a Database View, then you may simply search on this forum by using "UpdateTable" keyword. You should find some useful information.