# Is there a better way to store this data? **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [April 21, 2010, 1:18pm UTC](https://forums.percona.com/t/is-there-a-better-way-to-store-this-data/1385 "2010-04-21T13:18:33Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![richrumble](https://avatars.discourse-cdn.com/v4/letter/r/46a35a/32.png) [@richrumble](https://forums.percona.com/u/richrumble)\ **Post date:** [April 21, 2010, 1:18pm UTC](https://forums.percona.com/t/is-there-a-better-way-to-store-this-data/1385/1 "2010-04-21T13:18:33Z")
I’m beginning a Proof of Concept that will store a checksum of each and every file of each and every server on my lan. I want to keep repeat data to a minimum, and still be able to “reconstruct” and search for files based on file size, names, locations and even the checksum’s (hashes) themselves. I’ve attached a CSV that should give you a indication on how I envisioned the DB to be laid out. What I am curious about is if can be improved, if I can make any other optimizations to keep the DB as “light” as possible. The reason it’s designed the way it is should be obvious, however the “path-to-hash” section still troubles me that there will be a lot of repeated data over and over in all the fields. I have hundreds of workstations and servers, so keeping repeat data and overall DB size to a minimum is an obvious design goal. As such, Filenames are independent of file hash(checksum), and ID numbers (in base36) are used instead. Exact copies of files will hash the same, even if the name is changed, so again hash’s are independent of name. Root’s, like partition’s and drive letters are also independent of the rest of the path. Paths are also reduced to base36 number ID’s. All of that seems well and good, but when cataloging which file names, their hash’s, paths and on which computer they are on, it seems like I could probably reduce this further, but I’m wondering if I’ve already crossed a point of diminishing returns? Suggestions, questions and people telling me I’m crazy are welcome. ---
**Author:** ![richrumble](https://avatars.discourse-cdn.com/v4/letter/r/46a35a/32.png) [@richrumble](https://forums.percona.com/u/richrumble)\ **Post date:** [April 22, 2010, 8:16am UTC](https://forums.percona.com/t/is-there-a-better-way-to-store-this-data/1385/2 "2010-04-22T08:16:28Z")
Any suggestions? Anyone think I’m crazy… ) (The CSV in excel or calc (oo) looks much better than this) Filename-to-hash \_\_file\_to\_hash\_\_\_\_file\_ID\_\_\_hash\_ID \_\_908\_\_\_\_\_\_\_\_\_\_\_144\_\_\_\_\_543 \_\_909\_\_\_\_\_\_\_\_\_\_\_145\_\_\_\_\_543 Filenames file\_ID\_\_\_\_\_file\_name \_\_144\_\_\_\_\_Calc.exe \_\_145\_\_\_\_\_Copy of Calc.exe hashes \_\_hash\_ID\_\_md5\_\_\_\_\_Sha-1\_\_\_Sha-256 \_\_543\_\_\_\_\_8144813\_\_F0483F\_\_etc… machine \_\_mach\_ID\_\_mach\_serial\_\_mach\_name\_\_\_mach\_UUID \_\_99\_\_\_\_\_\_\_7xkfs0\_\_\_\_\_\_\_Laptop-007\_\_\_4C4C4544… \_\_100\_\_\_\_\_\_5k1p13\_\_\_\_\_\_Dev-31\_\_\_\_\_\_\_FABC73F0… Path-to-hash \_\_machine\_ID\_\_root\_ID\_\_path\_ID\_\_file\_ID \_\_99\_\_\_\_\_\_\_\_\_\_3\_\_\_\_\_\_\_12\_\_\_\_\_\_144 \_\_100\_\_\_\_\_\_\_\_\_3\_\_\_\_\_\_\_12\_\_\_\_\_\_144 \_\_99\_\_\_\_\_\_\_\_\_\_3\_\_\_\_\_\_\_12\_\_\_\_\_\_145 paths \_\_path\_ID\_\_path\_name \_\_12\_\_\_\_\_\_windows\system32 root **root\_ID\_\_root\_name _1\_\_\_\_\_\_\_a: 2\_\_\_\_\_\_\_b: 3\_\_\_\_\_\_\_c: 4/root 5_**\_\_/boot ---
**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\ **Post date:** [April 22, 2010, 10:58am UTC](https://forums.percona.com/t/is-there-a-better-way-to-store-this-data/1385/3 "2010-04-22T10:58:50Z")
Your example is too complex for me to take the time to understand fully, but I think you are asking the wrong question. Start with a fully normalized design, and if you need to for performance, then denormalize it – but not until you prove the need. ---
**Author:** ![Troy](https://avatars.discourse-cdn.com/v4/letter/t/85f322/32.png) [@Troy](https://forums.percona.com/u/Troy)\ **Post date:** [April 27, 2010, 10:01am UTC](https://forums.percona.com/t/is-there-a-better-way-to-store-this-data/1385/4 "2010-04-27T10:01:59Z")
I agree with xaprb. With decent/proper indexes I’m not sure you’re going to run into problems. Perhaps it’s as simple as 1 table: file\_hash, file\_name, file\_path (machine, root, full path) Obviously pulling the machine info and file hash out to new tables would be a smart move since multiple columns define each. However, the duplicated paths would probably bug me too - way too much redundant data. I guess it depends on how you’re using the data. If you always search by file name or file hash the path is just a look up value. If, on the other hand, you want to see what all is in a directory on a specific machine or to compare the same directory on multiple machines then your version seems like it would work better. I don’t think I understand the “Filename-to-hash” table. I think some table would contain the ID from “Path-to-hash” (where you define properties of each file on each system) and “hashes” (where you define a unique file across the network). Honestly, you could add the hash ID column to “Path-to-hash” which would give you the entire path to each file and the hash in one spot. The “Filename-to-hash” table would go away, queries could be on any key, look ups would be simple. Troy