What's a good way to handle cross-database keys in PostgreSQL?

Question

I'm writing an accounting app that uses MySQL, but I'm thinking about switching to PostgreSQL. My app has a meta database that stores users, document types, a list of companies, etc. Each company (a book-keeping entity) has its own database. Each company's documents table might look like this, along with other columns, of course:

documentID	createdByUserID	documentTypeID
1	1	1
2	1	1
3	2	5
4	1	3

createdByUserID and documentTypeID have foreign keys to meta.users.userID and meta.documentTypes.documentTypeID, respectively. MySQL doesn't mind foreign keys going to other databases. I want to have one users table for my app (along with documentTypes and other things), and I want to have each company's data in separate databases.

I see here that PostgresSQL doesn't handle cross-database foreign keys. How could I implement this sort of functionality in PostgreSQL? How is this sort of situation typically handled?

Answer 1

Use schemas, not databases, to separate your tables.

In MySQL, "schema" and "database" are synonyms. They refer to the same thing, which is one level of namespacing. You can query tables in separate schemas, for example with joins or making foreign keys. Since database and schema are the same thing in MySQL, you can do cross-database queries and references just as easily. A schema is like a subdirectory. You can USE <schemaname> to change the default schema, just like you can use cd in a Bash shell to change directory, and then use qualified names to access things in the other directory.

In PostgreSQL, "schema" and "database" are not the same thing. Schema is pretty much like it is in MySQL, a namespacing for tables. But a database is a bit more like setroot in a UNIX shell. When a client connects to a database, that client can't see schemas that live in other databases. The tables in one database can't reference tables in another database.

You really shouldn't keep tables in different databases if they are supposed to reference each other, or if you might query both tables in the same session. Databases are for separating collections of schemas or tables for different applications. If they are related, they should be in the same database (though they may be organized into schemas within that database).