Re: Table Design Issue & PGSQL Performance

On Thu, 28 Jul 2005 10:33:09 -0700 (PDT), operationsengineer1 wrote
> i have a notes table that records notes for different
> areas. since i didn't plan on different area notes
> initial, each area has its own note table.
>
> i know, bad, bad boy! i have three data entry pages
> instead of a single one and i'm already tired of
> maintaining multiple pages when it isn't necessary!
>
> anyway, i want to correct this situation so that i
> have a single table and add an area column to
> differentiate the area to which the note belongs.
>
> will pgsql insert and query faster if i use int2
> compared to char(2)? the reason i'm thinking of using
> char(2) is b/c it makes the table self documenting.
> for example, "pn" would obviously mean "production"
> and "qa" would obviously meany "quality" in the
> table's context. having a 1 and a 2 represent
> production and quality, respectively, is a lot less
> self documenting.
>
> i would appreciate a few people with experience
> chiming and providing their opinion on this.
>
> as always, thanks for sharing - it is much appreciated.

I went through similar contortions when designing some new tables in our
database. Basically I was considering emulating an enum type. Then I thought
why bother? I calculated the space requirements for each type. int, char(1)
and char(20). Then I looked at these in the context of the total record size.
Since I was dealing with a 512 character description the size of the
identifier was almost irrelevant. As you point out the self documenting issue
warrents consideration. If you use an int then somewhere you will have to
create and maintain some translatation code be it a SQL CASE or a switch or
whatever. Not only that but I would suggest you consider that what is obvious
to you may not be three generations of developers removed. Is the space
difference between 'pn' and 'production' significant in the context of the
record size.

HTH

Kind Regards,
Keith