4 ms·

Some times you know a value won't change that frequently. We use them for things like AWS regions we support. Sure AWS may add a few new regions a year, even if

Some times you know a value won't change that frequently. We use them for things like AWS regions we support. Sure AWS may add a few new regions a year, even if we supported all regions though we'd only be at 18. The validation on the application side assumes you never go around that validation, and the reality of data is that it's very possible to do that without a constraint on the database. Of course you can go too far with them when they don't make sense, but they're at least worth considering.

ainar-g 8y ago
Not saying that you are doing something wrong, but the AWS case kinda sounds like it will eventually grow to be a table of its own to me. Tomorrow the manager/architect/client comes and tells you that you need to associate some data with the region, and boom, you have a new table and a new foreign key constraint. Again, not saying you're doing something wrong, just sharing a thought.
- setr 8y ago
  I don't have much experience using them but it seems to me like migrating from an enum to a one-column table would be generally trivial: the constraints are effectively the same between an FK and the enum, so there can't really be any data to be cleaned up, and you'd still only use it for data validation, so there's no queries that need rewriting afaict. If its becoming a new table because you now want to associate metadata with it, then you'll have to rewrite queries in either case: previously it was just for data validation (so only relevant on insert, and never really referenced); now information is required so queries will have to join to it where they weren't before. And if the enum existed in multiple places, and now you have to go around hunting them down to construct the FKs.. then it probably should have just been a table in the first place (otherwise any change to the enum requires finding and syncing each instance of it) If it'll eventually want to become its own table, then at least while its still small we can claim the benefit of not having to name the thing. And that alone seems sufficiently beneficial to accept having to convert to a table later (unless, ofc, you expect to have to convert by like, tomorrow)

ainar-g 8y ago

Not saying that you are doing something wrong, but the AWS case kinda sounds like it will eventually grow to be a table of its own to me. Tomorrow the manager/architect/client comes and tells you that you need to associate some data with the region, and boom, you have a new table and a new foreign key constraint. Again, not saying you're doing something wrong, just sharing a thought.

setr 8y ago
I don't have much experience using them but it seems to me like migrating from an enum to a one-column table would be generally trivial: the constraints are effectively the same between an FK and the enum, so there can't really be any data to be cleaned up, and you'd still only use it for data validation, so there's no queries that need rewriting afaict. If its becoming a new table because you now want to associate metadata with it, then you'll have to rewrite queries in either case: previously it was just for data validation (so only relevant on insert, and never really referenced); now information is required so queries will have to join to it where they weren't before. And if the enum existed in multiple places, and now you have to go around hunting them down to construct the FKs.. then it probably should have just been a table in the first place (otherwise any change to the enum requires finding and syncing each instance of it) If it'll eventually want to become its own table, then at least while its still small we can claim the benefit of not having to name the thing. And that alone seems sufficiently beneficial to accept having to convert to a table later (unless, ofc, you expect to have to convert by like, tomorrow)