CODEWITHSUNDEEP
databasepostgresqldatabase-designforeign-key-relationship
ChrisR
ChrisR

Reputation: 14457

Grouping postgresql tables in schemas

I'm currently building an app that contains around 60 or so tables, some with meta information, some with actual data and a couple of views on top of them.

To keep things organized I'm prefixing all table name with meta_ or view_ respectively, but might it be worth it to just put them in different schemas inside the same database?

  1. It this common practice?
  2. Any reasons to not do this?
  3. Can I create FK-constraints over different schemas?

PS: There seems to be no performance penalty judging from this answer: PostgreSQL: Performance penalty for joining two tables in separate schemas

Upvotes: 4

Views: 1110

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657912

You can use schemas in any way you like. No limitation in principal. They are especially useful if you need to GRANT certain rights to groups of objects, for instance to separate users inside a database. You can largely treat them like directories in a file system (the analogy has its limits, though).

I use v_ prefix for views and f_ prefix for functions. But that is basically just notational convenience to spot those objects quickly in a text search - if I hack the dump for instance. Make such prefixes short, you will have to type them for the rest of your database life. Multiple prefixes from various semantic layers could have to apply to a single object.

I would not mix functional prefixes (v_ or view_) with semantic prefixes (meta_) on the same level. Rather create a separate schema for meta-objects and use prefixes to denote object types throughout all your schemata.

Whichever system you chose, stay consistent! Or it will do more harm than good.

Upvotes: 3

Related Questions