hyperboreean
hyperboreean

Reputation: 8343

table design for storing large number of rows

I am trying to store in a postgresql database some unique identifiers along with the site they have been seen on. I can't really decide which of the following 3 option to choose in order to be faster and easy maintainable. The table would have to provide the following information:

The amount of data that would have to hold is rather large: there are around 22 millions unique identifiers that I know of.

So I thought about the following designs of the table:

This approach would require around 22 mil multiplied by the number of sites.

Hopefully the number of sites won't go past 10. This would require only the number of unique identifiers that I know of, that is around 20 millions, but it would make it hard to work with it from an ORM perspective.

id - integer

unique_identifier - text,

one table that would store only sites, like in:

id - integer

site - text

and one many to many relation, like:

id - integer,

unique_id - integer (fk to the table storing identifiers)

site_id - integer (fk to sites table)

So, which one seems like a better approach to take on the long run?

Upvotes: 1

Views: 623

Answers (3)

Brian Hooper
Brian Hooper

Reputation: 22084

I'd definitely avoid the ten column boolean horror if I were you, as there will always be more sites later. I'd agree with Romain Hippeau, with the added suggestion that you might want an index on sites to answer questions like 'Who has visited site x?'.

Upvotes: 0

Matthew Wood
Matthew Wood

Reputation: 16427

If you already have a natural text-based unique identifier for site (url, perhaps?), then the only thing you need is ONE table with two fields:

CREATE TABLE (
    unique_identifier TEXT NOT NULL,
    site_identifier TEXT NOT NULL,
    PRIMARY KEY (unique_identifier, site_identifier)
);

You could then also add a UNIQUE INDEX on (site_identifier, unique_identifier) to facilitate lookups by site.

You may end up using a little extra space for the main table this way, but it is dead simple to query, update, and maintain.

Upvotes: 1

Romain Hippeau
Romain Hippeau

Reputation: 24375

Have two tables.
Table 1 Site ID,Site Name, Site Description
Site ID -> Primary Key
Site Name -> Index

Table 2 would be the one you are talking about.
Row ID, Site ID, Whatever Info.
Row ID -> Primary Key
Site ID -> Foreign Key into table 1
Index (Row Id, Site ID)

Upvotes: 1

Related Questions