4

what is a better design??

Model 1:

+------+          +--------+
| type |-|--+     | Data   |
+------+    |     +--------+
| id   |    |     | Mach ID|
| type |    |     | Time   |
| len  |    +----<| type ID|
+------+          | data   | (binary max 8)
                  +--------+

Model 2:

+---------+                 
| Data    |                 
+---------+                 
| Mach ID |                
| Time    |                 
| Type_1  |                 
| Type_2  |                
| ...     |                 
| Type_n  |                 
+---------+

(there are other tables too, like machine and client)

all types are previusly defined, ex:

  • id 08 is the temperature1 and its a float 4 bytes.
  • id 16 is a sensor and its an int 4 bytes.
  • id 125 is status 2 bytes.

there is only 22 types defined, but types can grow in the future. the problem is not all MachID have all the types. If i do the model 2 a lot of fields will have null.

All i have to do is store these data and display graphics per MachID. And to make graphs, i only need time vs type. so i can make sql selects to get tables like this

+------+--------+--------+-----+--------+
| Time | type_1 | type_2 | ... | type_n |
+------+--------+--------+-----+--------+
|      |        |        |     |        |

and graph, but getting this is more difficult with Model 1.

thanks for your help :)

ps: every month i get like 15000 records average per MachID. and sorry for my bad english

2
  • In the first model, you're storing an opaque binary and using an auxiliary table to interpret it correctly, while in the second model you're storing the value once in the correct column while leaving the other formatted columns empty. Is that right? Oct 1, 2014 at 18:31
  • @JonofAllTrades yes, in the second model the value is stored formatted, and int is stored instead of 4 bytes.
    – Vegam
    Oct 1, 2014 at 19:01

2 Answers 2

2

I would use your second model. The first might be more compact, but would require constantly CASTing your data; the second will have many NULLs, but they take up little space.

The second model looks denormalized at first, but if I understand your model right it's really not. You're not really packing 22 records into one, rather you're storing reports each of which may have up to 22 distinct strongly-types measurements. Some reports do not have every measurement, and that's fine.

2
  • I have to cast the data anyway when i get it from the socket. but, i have the doubt what will happen if types grow to 50. if you tell me its fine have nulls. i will do the second model. thank you :)
    – Vegam
    Oct 1, 2014 at 19:04
  • Casting it once to save it to the database is normal. Presumably you'll be reading your data multiple times, however, so it would be helpful to not have to CAST again every time you do so. 50 sparse columns is a lot, but NULLs take up very little space; MS SQL offers the SPARSE flag which reduces it to zero (stackoverflow.com/questions/1398453/…); what's your RDBMS? Oct 1, 2014 at 19:09
0

I see Model 1 as a better choice here:-

What when type will incease to 50 or 100 a table with maximumn column with null value, maintainance problem.

Database designs should offer flexibility to accomadate future requirement, not all but what we can think of for the time keeping requirement in mind at least those flexibility it must offer.

Choosing Model 1 will save space too, Type will some name(var(char)) and TypeId can be smallInt/Int (may not be needed).

In Model 2 adding new type Id needs to be done with ALTER TABLE and update indexes.

I see you need to write a complex query to get the data from tables in format you need it.

but the flexibility that if offers makes it a better choice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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