How do I handle a single `reference` to multiple different types of data?

Question

I host a file sharing site. The idea is to take a short string (e.g. abcd) and do one of the following:

• serve an image
• display some text
• redirect to another url

Historically, I've hosted this site using non-relational databases, where I'd store it as something like:

{
  short: "abcd",
  type: "url", // or image or text
  // type specific data, e.g. for a url
  url: "https://google.com"
  // or for a file
  store: "/cool_store",
  filesize: 10,
  etc.
}

I've redesigned the platform and decided to use SQLite / Postgres instead. I'm not that used to schema design and my first design of (not actual code, heavily simplified):

CREATE TABLE Items (
    short varchar(64),
    store varchar(255),
    filesize int
);

Which worked great for the image hosting side of things. Now I want to expand to the other types and can't decide the best approach. My two main ideas are:

1. Replace the singular table with one 'reference' table and separate 'item' tables for each different type, e.g. References { id: 'abcd', type: 'url', ref: 1 } (where the ref represents the primary key of a different table). Items { id: 1, store: "/cool_store", filesize: 10 }. Urls { id: 2, url: "https://google.com" }, etc.
2. Store all the properties required on a single table with a large proportion of the columns being null. In total we require ~50 columns, with ~30 being null at any point in time.

The database is exceptionally read-heavy, maybe 99.99% reads.

Answer 1

You should group the fields closest related to each other in the same Table and create additional Tables for the remaining fields as they relate to each other as well. This is called database normalization and is effective in reducing data redundancy, improving maintainability, and helps optimize performance. Your additional Tables (kind of reference Tables such as your first option describes) will store the primary key(s) of the Table(s) they relate to. This is known as a foreign key relationship. Your second design option is called denormalization and is not recommended in most cases (there are some specific cases where some denormalization is helpful).

I don't know your data so I'll use an example for a Car Dealer to convey the idea of normalization. Image a Car Dealer business who sells different brands of Cars across multiple Locations (notice how my objects are already taking shape of the Tables I need). So I'd expect their to be a Locations table, with columns regarding each Location, such as its Address, PhoneNumber and maybe ManagerName, in addition to its primary key column LocationId. I'd also expect a Cars table with columns like Manufacturer, Model, ManufacturedYear, with a primary key column of CarId.

Cool, so far so good. But what if we wanted to know more information about the Managers of each Location and more information about the Manufacturers of each Car. To store that in the database, if we added those fields to the Locations and Cars tables respectively would be bad design because that's denormalized. Information about a Manager object doesn't make a lot of sense in a Locations table, same for information about a Manufacturer in the Car Dealership's Cars table. So we should create two more tables, Managers and Manufacturers.

Managers would now store the ManagerName field instead of the Locations table. (Realistically you'd like store ManagerName in two fields, one for FirstName and one for LastName but this is just a basic example, so bear with me :). Managers would also store things like the Manager's Address and PhoneNunber, and maybe even their YearlySalary, all things that wouldn't make sense in a Locations table. Finally, the primary key would be ManagerId which is what the Locations table would store as a foreign key reference to the Managers table, and this is how you relate the two tables.

Same design as above for the Manufacturers table. It would have columns like ManufacturerName, Address, PhoneNumber, maybe a field called NumberOfCarModels etc, and finally a primary key of ManufacturerId which would be a foreign key in the Cars table.

This is loosely a first level of normalization and hopefully conveys how to structure things. Essentially every main object gets it's own table. You can actually further normalize this (though not always necessary) by having more generic tables for your objects. For example a Contacts table could store all the Addresses and PhoneNumbers of the Locations, Managers, and Manufacturers tables in one place (with a column to denote the ContactType) instead of on each individual table. And the Managers table would likely be an Employees table with a field IsManager to denote which ones are Managers. This next level of normalization reduces redundancy and lightens the other tables even more so (but again it isn't a necessity and just depends on your situation).

Ideally, the performance benefits of normalization come from the fact that when you query a table, you're not getting back 30 extra columns of data you don't need and take longer to load off disk, etc. By designing your tables centralized around your primary objects, and storing only what's truly relevant to those objects in those tables, breaking out the additional fields into their own object tables, you will maximize the performance of querying those tables.

Answer 2

Here is an approach expanding upon your NoSQL (or document or JSON) data idea:

{
  short: "abcd",
  type: "url", // or image or text
  // type specific data, e.g. for a url
  url: "https://google.com"
  // or for a file
  store: "/cool_store",
  filesize: 10,
  etc.
}

You can use PostgreSQL JSON type. The table can have a structure like this, with item_details as the JSONB type.

CREATE TABLE items (
    short VARCHAR(64) NOT NULL PRIMARY KEY,
    item_details JSONB
)

The structure for item_details:

[
  { "key": "<type_name>", "val": "<type_value>" },
  { "key": "<type_specific_attribute-1>", val: "<type_specific_attribute_value-1>" },
  { "key": "<type_specific_attribute-n>", val: "<type_specific_attribute_value-n>" },
  ...
]

Here the "<type_name>" can be "url", "file", "text".

This allows querying on the array field by "key" or "val" or both keys. So, the WHERE clause condition can be for example (as pseudo-code), key = "url", and this will fetch all the table rows of type "url". Another example, key = "url" and val = "https://google.com" and this fetches the specific table row where the url value is specified.

Using this structure will allow many types, in addition to the "url", "file", and "text". You will be querying on only two JSON fields "key" and "val", irrespective of number of types.

Example data and queries:

INSERT INTO items VALUES ('abcd', '[ 
    { "key": "type_name", "val": "type_value" } 
  ]' );
SELECT * FROM items WHERE item_details @> '[{ "key": "type_name" }]';
SELECT * FROM items WHERE item_details @> '[{ "key": "type_name", "val": "type_value" }]';

You can also create an index (a GIN index) on the JSONB data:

CREATE INDEX items_idx ON items USING GIN (item_details);

-- More data:

INSERT INTO items VALUES 
  ('mnop', 
   '[
      { "key": "text", "val": "This is my text..." }
   ]'
  ),
  ('ijkl', 
   '[
      { "key": "url", "val": "https://google.com" }
   ]'
  ),
  ('wxyz', 
   '[
      { "key": "file", "val": "/cool_store" },
      { "key": "file_size", "val": 10 }
   ]'
  );

SELECT short, item_details FROM items WHERE item_details @> '[{ "key": "url" }]';
SELECT short, item_details FROM items WHERE item_details @> '[{ "key": "url", "val": "https://google.com" }]';

Further, you can use JSON Functions and Operators to extract JSON or process it.

This approach can also be used with Document Store data (JSON) of MySQL database or with other NoSQL databases which support JSON or Document stores.