SQLTeam.com | Weblogs | Forums

Do AddNewRecord and EditRecord actions require different windows?

I have created the first half of a SqlServer database interaction project.
(I had already established the database structure and copied existing records into the tables.)

The "first half" was to allow the user to display an existing record and allow edit/save changes.

The "second half" is to allow the user to add a new record.

My thought is to use the same windows as in the "first half", hiding the buttons associated with editing existing records(if necessary) and showing buttons for creating a new record...but if I can simply use a button to allocate a new record(as empty), then use the same "edit/save existing record" buttons to fill in the field values for the new record, that could avoid duplication in my coding efforts.

Would it be considered "bad technique" to not have the two capabilities totally separate in my project?

(I have considerable experience with locally-hosted relational databases, but it was only recently that my employer began a relationship with a web-based host server provider for this database.)

TIA

I think you're better off having a separate INSERT/add function. The idea you proposed would lead to "blank" data being inserted into the database with the expectation of the user completing it. If they don't, or there's an error or broken connection of some kind, then you'll have orphaned rows in your table(s).

Generally speaking, it's better for the user interface (especially a web UI) to collect the data it needs before synchronizing anything with a database. The web was founded on an asynchronous/stateless model, in that you capture and maintain all state locally, and then send all the state you need to a web server to process. The web server then responds with whatever state you need to add or update in your browser.

I'd still have orphaned rows if the tables are automatically bound together by system-generated keys(primary keys and foreign keys)?

Yes, that's likely if you use auto-binding. I always struggled with that back in the day (20 years ago) and ended up abandoning it in favor of using stored procedures for that kind of logic. I found it easier to write a procedure to handle inserts/updates, especially for multiple tables with or without foreign keys. For me it was a lot more convenient, especially if I wanted to do multi-row operations, it still ended up being a single web request and single DB transaction, even for 100s of rows. Especially for stateless web requests.

If you have an often-changing data model, auto-binding might complicate certain things, depending on your data access layer. An ORM might help, but might also cause poor performance. Best way to check is to profile the SQL that's generated and review the query plans, if you do get performance issues. Stored procedures can help insulate against object-relational impedance mismatch and allow for more flexibility in the app layer.

I'm biased towards the cranky-DBA-20-years-experience-fixing-other-peoples-shit-get-off-my-lawn perspective, so keep that in mind. :slight_smile: