CODEWITHSUNDEEP
databasepostgresqlindexingtypesdatabase-design
ChrisR
ChrisR

Reputation: 14447

Storing varying data types

We are currently building a PostgreSQL database that allows us to create profiles for hardware. Each profile exists of a few different properties which are all dynamically added to the profile through the UI. Each property can be of a different type (string, integer, text, ...)

How would I most efficiently store these properties in a relational database?

I've read Storing diverse value types in a database and I'm charmed by the single answer there but I'm not at all confident that this is the ideal solution. Are there any pros/cons using the separate-table-per-type approach over the other solutions? Anyone has real world experience with this and minds shedding some light on the best approach?

Upvotes: 0

Views: 3914

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

Use a column of data type text.

Every data type in PostgreSQL has text representation - or else a plain pg_dump would fail. Also, how would the user type in the value in the first place?

I have had several use cases like that for a couple of years now. No problems. You can store the actual data type in addition and cast the text representation if needed.

One disadvantage might be that the on-disc space for text representation will be higher for some types. But the overhead for multiple columns / tables may easily make up for that.

Additional answer to question in comment

PostgreSQL is extremely versatile with indexing. I would solve a case like you mention in the comment with a partial index on an expression:

CREATE INDEX tbl_col_propx_idx ON tbl (cast(col AS int))
WHERE  prop_type_id = 23;  -- 23 signifying "propertyX"

And prop_type_id would be a foreign key to a look-up table (foreign-key referenced):

CREATE TABLE prop_type(
   prop_type_id int PRIMARY KEY,
   prop_type text NOT NULL);

prop_type_id could also be an enum type. The handling of enums has been much improved in the latest version.

Then a query like this would be lightening fast:

SELECT *
FROM   tbl
WHERE  prop_type_id = 23   -- 23 signifying "propertyX"
AND    col::int BETWEEN 50 AND 100

In addition, such an index would automatically validate the data type and throw an error if you'd try to enter string that doesn't match the type.

Alternatively, consider a document type like hstore or json / josnb (typically superior in modern Postgres).

Upvotes: 4

forker
forker

Reputation: 2679

My preferred way is single table with both key and value being varchar and/or text.

The only big disadvantage of table-per-type in that comment I can think of is that it's hard to constraint the uniqueness of the key in pair with the entity this key-value entry belongs to.

Table inheritance (but still with table-per-type) would be another way to go and IMHO it's brutally type-safe and compliant with postgres and ORM concepts:

CREATE TABLE key_values(user_id integer references users(id), key varchar(255), type varchar(255), CONSTRAINT UNIQUE(user_id, key));
CREATE TABLE key_values_int(value integer) INHERITS (key_values);
CREATE TABLE key_values_string(value varchar(255)) INHERITS (key_values);
and so on...

This perfectly maps to JPA.

Upvotes: 1

Related Questions