Database Design. Table containing units conversion
I have to defend a point of view about this issue (or accept my mistake). More than an answer I would like to find an authoritative answer, some article / book that could settle the issue. My database design contains several measures, like 1 - the patient height and weight in the Patients table. 2 - The administration units in the Medications table (in reality, a one to many relation, one medication, one or more units) Another developer in the team tries to convince the group that the Units table should not exist, that the UI has to hardcode the conversions. If the height can be exposed in meters or feet, a new client that wants the height in centimeters implies a change in the UI program. Can anybody help me to find the source to settle this discussion?
I don't think there's an obviously right answer here. Will these conversions also need to be done in reports, Analysis Services or in a data warehouse? It's highly likely they will. If so, the conversion factors should be stored in the database so that the same answers can be calculated in the app and in the database. But I wouldn't get too 'religious' about always doing the conversion in the database. One thing strikes me as odd though. This developer is wanting to do something most developers try to avoid: change existing code. They would much rather write new code. I think they must have some strong motivation for this. Try to get them to explain what problem they are trying to solve with this. Maybe you can see a fix that would make you both happy.
The table contains the height in cm and the weight in kg. The internal part of the system that does some calculations based on height and weight knows this. When a user changes one of the units to see the values, the user is marked as "using pounds and feet/inches". The question here is if the conversion has to be in the UI or in the database. The current way, my opinion, is that the database is taking care. The other person wants (and probably will get his way) that the lookup is not done and the UI would do the conversion. I was actually looking more for a source than an opinion.
I think he means is it okay to have a column with different units, or should they all be the same. i.e id | Height |UnitCode ---------- 1 | 155 | 1 2 | 6.5 | 2 where 1 = cm and 2 = feet Personally I don't like this table because 6.5 < 155 but 6.5 feet is taller than 155 cm. Also If I just have cm then I can probably get away with having a data type integer, if I have feet and cm I need to have a decimal. There is that story, don't know if true or not, about USA and UK building a rocket and one was working in imperial and the other in metric (I find it hard to believe this is true, but I was told it a lot by my maths teacher as a kid so I would show my units or risk a rocket falling from space)