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:
- 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. - 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.
References.id
an auto increment integer and addReferences.short varchar(64)
with an appropriate index. Then, have you considered storing the extra data as a JSON column? postgresql.org/docs/10/datatype-json.html