Thread: Domain vs table
Hi, We have table 'user' and one column define status of user, currently there are 2 valuse 'A' acitve and 'D' deleted. Currently we define column as domain type ( status_domain with two possible values) but I'm not sure is it good solution, maybe it is better create separate table e.g account_stats and use foreign key in account table? In our databases we prefer 'domain' solution for column with low cardinality and when we do not need extra fields related to values (e.g description). I think such solution should give us better performance when rows are updated/inserted but I've never make real comparision to separate table. Havy you made such comparision? Regards Michal Szymanski http://blog.szymanskich.net http://techblog.szymanskich.net
I think I've found answer to my question http://www.commandprompt.com/blogs/joshua_drake/2009/01/fk_check_enum_or_domain_that_is_the_question/ Michal Szymanski
On Sun, Oct 11, 2009 at 11:31 AM, Michal Szymanski <mich20061@gmail.com> wrote: > I think I've found answer to my question > http://www.commandprompt.com/blogs/joshua_drake/2009/01/fk_check_enum_or_domain_that_is_the_question/ > I mostly agree with the comments on the blog but let me throw a couple more points out there: *) It is possible (although not necessarily advised) to manipulate enums via direct manipulation of pg_enum *) enums are the best solution if you need natural ordering properties for indexing purposes *) domains can't be used in arrays *) foreign key is obviously preferred if you need store more related properties than the value itself *) if the constraint is complicated (not just a list of values), maybe domain/check constraint is preferred, possibly hooked to immutable function merlin
On Oct 20, 2009, at 6:55 AM, Merlin Moncure wrote: > On Sun, Oct 11, 2009 at 11:31 AM, Michal Szymanski > <mich20061@gmail.com> wrote: >> I think I've found answer to my question >> http://www.commandprompt.com/blogs/joshua_drake/2009/01/ >> fk_check_enum_or_domain_that_is_the_question/ >> > > I mostly agree with the comments on the blog but let me throw a couple > more points out there: > > *) It is possible (although not necessarily advised) to manipulate > enums via direct manipulation of pg_enum > *) enums are the best solution if you need natural ordering properties > for indexing purposes > *) domains can't be used in arrays > *) foreign key is obviously preferred if you need store more related > properties than the value itself > *) if the constraint is complicated (not just a list of values), maybe > domain/check constraint is preferred, possibly hooked to immutable > function Also, if the base table will have a very large number of rows (probably at least 10M), the overhead of a text datatype over a smallint or int/oid gets to be very large. -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828