Storing repetetive file metadata in databse
I will be storing this kind of data for many different directory structures that very often have the same file metadata. I do not want to have many repeated entries in the database, but I also do not want to have a 5 column Primary key.


In order to normalize, you need to do is accommodate for LVM, and partition information. Then you should only have to keep inode, and meta data, (stat and lsattr if you want to be super anal). You may or may not want emulate hierarchy in path. We do in this example. The repetitive meta-data isn't an issue.

CREATE TABLE physical_volume (
  pv_id      int PRIMARY KEY,
  hw_uud     uuid
);

CREATE TABLE volume_group (
  vg_id int PRIMARY KEY
);

CREATE TABLE pv_vg (
  pv_id int REFERENCES physical_volume,
  vg_id int REFERENCES volume_group,
);

CREATE TABLE logical_volume (
  lv_id int PRIMARY KEY,
  name  text
);

CREATE TABLE partition (
  partition_id int PRIMARY KEY
  fs           text,
  mount_attr   jsonb,
);

CREATE TABLE files (
  file_id       int PRIMARY KEY
  partition_id  int REFERENCES partition
  inode         int,
  .. repetitive metadata
  UNIQUE (partition_id, inode)
);

CREATE TABLE path (
  path_id            int PRIMARY KEY,
  parent_path        int REFERENCES path
  file_id            int REFERENCES files
  name               text
);