"Best practice" design to input data into detail table 1

[ReCycled]

I am working on a form where the users can input data into two related tables, basically a master table and a detail table, the second with several records that relate to the master. On a similar program I had the details input into a grid related to a detail table query, and had procedures to post, update or delete the details as the users wrote, corrected and finally confirmed the inputs. Although it is working fine, it was quite complicated for me to write, and is a bit slow to operate. –I`m no expert.

Faced with a similar need now, and looking for a better road, and I am having the user input the detail data into edits, values going into a set of variable arrays that are displayed in a list view. Only when the user has finished adding and correcting all the details related to the main form record, and so confirms, the program will post the arrays to the detail table and apply the updates. Possible complication for me will be in all the code needed to keep the garbage and “un-post-able” inputs out early in the procedure.

I suppose there are other solutions, and that this is a rather common thing. Here is the question: ¿Is there a “best practice” or “standard” solution for this? I am programming in Lazarus and using a Firebird database, although in the first mentioned program I had to use the tables in an existing Access program.

Thanks.

 

[Olaf Doschke]

I don't see whats exactly the hard part about it.

It boils down to having all detail records have the master record primary key as their foreign key. Besides that it's like adding N records to a table all at once or none (if there is a problem with some broken rule or something).

Maybe you can explain a bit more about the difficulty you have. Is it holding all the data in some array(s) before saving them? If that is Lazarus sepcifc and hos nothing to do with the database or database design, regadless of the implementation of the data entry form, the question also is a bit wrong here.

Bye, Olaf.

 

[ReCycled]

Hi Olaf,
Many thanks for your reply. After reading your post I realize:

1. Yes, this is the wrong forum. The question is one of programing procedure and not a database design issue. Sorry. Will see if there is a proper forum, and clear up the question.
2. The dificulty is mostly my lack of expertise, since I believe both paths I mentioned will work.

What I was wondering was if there was a standard way for this. It seems to me if I post to the detail table after the user inputs each record, the database will reject un-postable data, which is good but slow, as is deleting mistakes, etc. (this is how I wrote the first program). It seems that if I load all the records to an array, any changes and aditions while editing by the user will be a lot faster, but the number of detail lines will have to be a fixed number, which if large will be a waste of memory for unused variables, will not have the advanage of using the database to check the entries before the user finishes typing in all the lines, and maybe I will run into other dificulties as yet unknown to me.

So the question was, since surely experienced programers write procedures for this all the time ¿Is there consensus about a standard or best practice way? ¿one of this two, or a third way?

If you can point me to the proper forum, please do.
Thanks again, Maza

 

[Olaf Doschke]

I don't know what Lazarus offers, but different languages have different possibilities to bind a form to data. In most cases a collection would be a better thing than an array, but also an array can be dimensioned as needed in most cases and doesn't need to be fixed. In C# you have the in memory datasets you add to, befoe you submit to the central backend database. The rule check can be something you implement twice in database and application business rules layer. But database rules would perhaps be more concerned about data integrity alone, while the busisness rules may be much more complicated, considering data from other tables or even other databases or web services. So in general it's not a shame you implement simple data rules both locally in your application and in the database. It's often much easier to implement simple rules two times instead of handling errors coming back from database insert triggers or something like that.

Bye, Olaf.

 

[ReCycled]

Lazarus variable types include "Records" to handle a set of difrent type data, and "Arrays" for data of same type, so I will be using an array of records. There are also dbedits to acess table records directly. Not sure about colections, will look into it (seems to me a record is a type of collection) and also will see if I can get arround setting the array dimension as fixed when declaring it as a variable at the beginning of the procedure. Will have to write some code to pre-check variable values.

In any case I will input data from the users into variables, and do only one update to the database at the end of each array of records, related to one master record primary key. I still have a lot to learn..

Thanks Olaf for taking the time to help.
Maza