Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm looking for a SQL architecture solution for the following problem. The idea is that I have a validation form that contains the client's data. Each client can log in to his account and edit his data. After that I have to save it to the db. After validation of the content of the data, I have to update the original fields.

Actually each client has a photovoltaic installation. Each installation contains one or more inverters and at each one there is N solar pannels that are attached. All of the inverters are attached to an electrical meter.

I choose to save only the difference between the data stored in the db and the modified data. e.g: if the client changes only the brand of his inverter I will not save all the data, but only the modified inverter brand. I then have to validate the brand and to update it in the table Inverters.

My first idea to solve that issue is to work with 2 tables: validation_data and validation_fields. The table validation_fields contains all the fields that can be updated (vaf_field). The table validation_data is a temporary table. It contains the id of the field to update (vad_vaf_id, who is also the foreign key to the table validation_fields) and the value (vad_value). vad_met_id is the foreign key to link to the meter. Nothing special :) Once the data is verified, vad_is_treated will have the value 1. The default is 0.

+-----------------+   +-------------------+
| validation_data |   | validation_fields |
+-----------------+   +-------------------+
| vad_id          |   | vaf_id            |
| vad_met_id      |   | vaf_field         |
| vad_vaf_id      |   +-------------------+
| vad_value       |
| vad_is_treated  |
+-----------------+

So that logic is good, or at least it works fine for me, but only if the client possesses only one inverter. As I said I save only the difference between the existing and modified data. So if the client changes the brand of two of his inverters, in the validation_data table I will have:

+--------+------------+------------+--------------+
| vad_id | vad_met_id | vad_vaf_id |   vad_value  |
+--------+------------+----------- +--------------+
|      1 |     1234   |          2 |        Peter |
|      2 |     1234   |          3 |    SMA 1400S |
|      3 |     1234   |          3 |    SMA 1400S |
+--------+------------+------------+--------------+

Consider that if vad_vaf_id is 2, that is the firstname field, and 3 - the inverter's brand.

So okay, for the first line all is good, the cliend changed his firstname. But for the second and the third line there is a problem. The client changed the brand of his inverters but for which one? The first and the second inverter, or the first and the third, or the second and the third?

Do you have any idea how I can deal with that?

Thanks in advance

share|improve this question
1  
I really would not call a table design an architecture. –  TomTom Oct 20 '13 at 8:39

1 Answer 1

If your vad_vaf_id does not sufficiently designate the target value, perhaps you should add a new column in your temporary table to provide that information? Something like vad_target?

+-----------------+   +-------------------+
| validation_data |   | validation_fields |
+-----------------+   +-------------------+
| vad_id          |   | vaf_id            |
| vad_met_id      |   | vaf_field         |
| vad_vaf_id      |   +-------------------+
| vad_target      |
| vad_value       |
| vad_is_treated  |
+-----------------+

When user changes his firstname, leave the field NULL. When there are multiple possible targets (i.e. with inverters), use inverter's id.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.