Thread: Table and Field namestyle best practices?
I'm new to pgsql, mostly coming from MSSQL. It sounds like it's simplest to keep field names lowercase with pgsql, so I will have to change some habits I've developed over the years. I would like to glean whatever collective wisdom I can here from experienced pgsql devs. I've been using namestyles with mixed case like OrgID. That is much more readable than orgid. Probably a good convention to adopt would be to use namestyles like org_id. That change I can live with. But another issue is the way that I've been naming foreign key references. I tend to prefix field names with a table based acronym. So names like OrgName, OrgCity. Foreign key fields incorporate the ref'd pk. So, if I have a table tblEmployee, fk to the org table would be like EmpOrgID. I know many would simply use OrgID as the fk in tblEmployee, but I have liked compounding the names because is results in completely unambiguous field names throughout the db. If I'm giving up the mixed case naming, I could use emp_fname, emp_lname, emp_org_id, but I'm not sure if that's best. I am very curious to hear the various conventions folks here have arrived at. I don't expect there to be consensus, but the various rationales might help me arrive at an approach that works well for me. -- View this message in context: http://www.nabble.com/Table-and-Field-namestyle-best-practices--tf2596761.html#a7243332 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> I've been using namestyles with mixed case like OrgID. That is much more > readable than orgid. Probably a good convention to adopt would be to use > namestyles like org_id. That change I can live with. According to recommended practice you have a choice to make. Here is what is described from the following link: http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.) Make note of the last sentence. Regards, Richard Broersma Jr.
Yes, I've already pretty much decided to use lowercase for all namestyles, I mentioned that in the first post. Using lowercase invokes a set of other issues, which I'm asking for options on...namely, conventions like org_id, and emp_org_id, or simply using org_id as the fk pointer. Richard Broersma Jr wrote: > >> I've been using namestyles with mixed case like OrgID. That is much more >> readable than orgid. Probably a good convention to adopt would be to use >> namestyles like org_id. That change I can live with. > > According to recommended practice you have a choice to make. Here is what > is described from the following link: > http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS > > Quoting an identifier also makes it case-sensitive, whereas unquoted names > are always folded to > lower case. For example, the identifiers FOO, foo, and "foo" are > considered the same by > PostgreSQL, but "Foo" and "FOO" are different from these three and each > other. (The folding of > unquoted names to lower case in PostgreSQL is incompatible with the SQL > standard, which says that > unquoted names should be folded to upper case. Thus, foo should be > equivalent to "FOO" not "foo" > according to the standard. If you want to write portable applications you > are advised to always > quote a particular name or never quote it.) > > Make note of the last sentence. > > Regards, > > Richard Broersma Jr. > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > > -- View this message in context: http://www.nabble.com/Table-and-Field-namestyle-best-practices--tf2596761.html#a7244110 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Nov 8, 2006, at 18:49 , novnov wrote: > I've been using namestyles with mixed case like OrgID. That is much > more > readable than orgid. Probably a good convention to adopt would be > to use > namestyles like org_id. That change I can live with. Both are perfectly acceptable, though the mixed-case version has drawbacks. ANSI SQL's case-insensitivity makes mixed-case identifiers something of a second-rate citizen; "orgid" and "OrgID" are both going to be resolved to the same object, unless you explicitly double- quote it. Ambiguity is rarely a problem, but because there are no checks for consistency, inconsistencies tend to sneak in, especially in team projects; some people might type "OrgID", some "OrgId", and so on. Note that lower-case, underscore-delimited variable identifiers are consistent with mainstream coding conventions for C, C++, Ruby, Python and most languages in the Unix world (Java and JavaScript being notable exceptions). After several years of trying to go against the grain and be consistent across the board, I ended up giving in and always using whatever is appropriate in the language/ environment I work in. > But another issue is the way that I've been naming foreign key > references. I > tend to prefix field names with a table based acronym. So names like > OrgName, OrgCity. Foreign key fields incorporate the ref'd pk. So, > if I have > a table tblEmployee, fk to the org table would be like EmpOrgID. I > know many > would simply use OrgID as the fk in tblEmployee, but I have liked > compounding the names because is results in completely unambiguous > field > names throughout the db. If I'm giving up the mixed case naming, I > could use > emp_fname, emp_lname, emp_org_id, but I'm not sure if that's best. For column names, I recommend using whatever is natural in the decribing a field, irrespective of what the field is actually pointing towards. For example, a table representing an object with a creator attribute pointing to a user would have a column "creator_id" referencing "users (id)" -- not, say, "creator_user_id", which is superfluous. The id suffix is there to tell me it's an identifier, not the creator proper. In your case, in the table "organizations" the column names would be "id", "name", "city" and so on, and a table "employees" would have a column "organization_id" with a foreign-key reference. This simple convention translates more easily to one's mental model of a query such as "select all employees where the organization name is 'foo'", which becomes: select * from employees join organizations on id = organization_id where organizations.name = 'foo' as opposed to select * from tblEmployees join Org on OrgId = EmpOrgId where Org.OrgName = 'foo' or something. I am curious as to why you need to prefix table names with "tbl" in the first place. Alexander.
On Wednesday November 8 2006 11:31 am, novnov wrote: > Yes, I've already pretty much decided to use lowercase for all > namestyles, I mentioned that in the first post. Using > lowercase invokes a set of other issues, which I'm asking for > options on...namely, conventions like org_id, and emp_org_id, > or simply using org_id as the fk pointer. Not sure there is a "best" way, but I prefer org_id due to brevity and no concern for quoting. Your emp_org_id is nice for views because you don't have to disambiguate columns.
On 11/8/06, novnov <novnovice@gmail.com> wrote: > > I'm new to pgsql, mostly coming from MSSQL. It sounds like it's simplest to > keep field names lowercase with pgsql, so I will have to change some habits > I've developed over the years. I would like to glean whatever collective > wisdom I can here from experienced pgsql devs. > > I've been using namestyles with mixed case like OrgID. That is much more > readable than orgid. Probably a good convention to adopt would be to use > namestyles like org_id. That change I can live with. of course, it's all in the eye of the beholder, but i think org_id looks much better than OrgID. I suggest not prefixing tables with 'tbl', but idx_ for indexes and fk_ for foreign keys is ok. I also think its ok to do on_xyx_delete for a delete trigger on table xyz. some people like to name id columns 'id' and refer to that column as xyz_id for foreign key reference, but I prefer to write out xyz_id in all tables...it helps with natural joins and searching. this is basically your argument as well. also, and this is getting into flamewar territory, but i prefer to not pluralize tables (create table order) for conceptual reasons. no big deal though really. my tables do not automatically get an 'id' column although they often do...this is barely on topic for your post so ill leave it there ;-) one last thing (also not really on topic), and this is very much against the grain, but I do not do upper/lower case mixture that I see in 95% of the sql on the web: SELECT * FROM order WHERE it reminds me too much of cobol, er COBOL and it makes my brain hurt. i also passively agressively affirm my minority stand on this issue by deliberately having lousy capitalization on the various lists. regards, merlin
Merlin Moncure wrote: > looks much better than OrgID. I suggest not prefixing tables with > 'tbl', but idx_ for indexes and fk_ for foreign keys is ok. I've recently gotten into the habit of naming my indexes after exactly what they index. For example: create index "foo(x,y,z)" on foo(x,y,z); and CREATE INDEX "tbl using gist(text_search_vec)" on tbl using gist(text_search_vec); It's just as obvious as prefixing them with "idx_" and makes EXPLAIN output a bit quicker for me to understand.
Thanks that some good real world input. Not sure what it'll add up to for me yet but good reference points. In the db centric world I've been inhabiting for these years there are many conventions re namestyles, they extend to table names, query names, field names, variables, everything. I started out disliking the schemes but over time saw the sense and adopted or munged for my own taste. Unless some kind of prefixing is incorporated into naming conventions, a name like employee could be a table, a query, an 'object', a field, etc. All of my prev work is very easy to read because all names are clearly pegged. tblOrganization is a regular data table, tlkpCity is a lookup table. qryOrganization is a select query (usually by convention tblOrganization.*, ie all records), qappEmployeeSalary is an append query, qdelEmployeeSalary a delete query (usually with params). Also, when refactoring is needed, and table/field etc needs to be renamed, having unique names is pretty cool...and the patterns I've built up name items in families (like the Org field name prefixes) so sometimes one can swat a bunch of name changes at once...carefully. These conventions have helped me navigate databases when they start getting a signficant number of objects in them. I know many do without, but they've been very good for me. I don't need to repro the naming conventions I've built up over the years, but would like to keep the utility they offer somehow. Alexander Staubo wrote: > > On Nov 8, 2006, at 18:49 , novnov wrote: > >> I've been using namestyles with mixed case like OrgID. That is much >> more >> readable than orgid. Probably a good convention to adopt would be >> to use >> namestyles like org_id. That change I can live with. > > Both are perfectly acceptable, though the mixed-case version has > drawbacks. ANSI SQL's case-insensitivity makes mixed-case identifiers > something of a second-rate citizen; "orgid" and "OrgID" are both > going to be resolved to the same object, unless you explicitly double- > quote it. Ambiguity is rarely a problem, but because there are no > checks for consistency, inconsistencies tend to sneak in, especially > in team projects; some people might type "OrgID", some "OrgId", and > so on. > > Note that lower-case, underscore-delimited variable identifiers are > consistent with mainstream coding conventions for C, C++, Ruby, > Python and most languages in the Unix world (Java and JavaScript > being notable exceptions). After several years of trying to go > against the grain and be consistent across the board, I ended up > giving in and always using whatever is appropriate in the language/ > environment I work in. > >> But another issue is the way that I've been naming foreign key >> references. I >> tend to prefix field names with a table based acronym. So names like >> OrgName, OrgCity. Foreign key fields incorporate the ref'd pk. So, >> if I have >> a table tblEmployee, fk to the org table would be like EmpOrgID. I >> know many >> would simply use OrgID as the fk in tblEmployee, but I have liked >> compounding the names because is results in completely unambiguous >> field >> names throughout the db. If I'm giving up the mixed case naming, I >> could use >> emp_fname, emp_lname, emp_org_id, but I'm not sure if that's best. > > For column names, I recommend using whatever is natural in the > decribing a field, irrespective of what the field is actually > pointing towards. For example, a table representing an object with a > creator attribute pointing to a user would have a column "creator_id" > referencing "users (id)" -- not, say, "creator_user_id", which is > superfluous. The id suffix is there to tell me it's an identifier, > not the creator proper. > > In your case, in the table "organizations" the column names would be > "id", "name", "city" and so on, and a table "employees" would have a > column "organization_id" with a foreign-key reference. This simple > convention translates more easily to one's mental model of a query > such as "select all employees where the organization name is 'foo'", > which becomes: > > select * from employees > join organizations on id = organization_id > where organizations.name = 'foo' > > as opposed to > > select * from tblEmployees > join Org on OrgId = EmpOrgId > where Org.OrgName = 'foo' > > or something. > > I am curious as to why you need to prefix table names with "tbl" in > the first place. > > Alexander. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > -- View this message in context: http://www.nabble.com/Table-and-Field-namestyle-best-practices--tf2596761.html#a7245644 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re this part of what you wrote: "For column names, I recommend using whatever is natural in the decribing a field, irrespective of what the field is actually pointing towards. For example, a table representing an object with a creator attribute pointing to a user would have a column "creator_id" referencing "users (id)" -- not, say, "creator_user_id", which is superfluous. The id suffix is there to tell me it's an identifier, not the creator proper." Rereading it I'd like to clarify my current approach. The reason a fully qualified pointer (ala "creator_user_id") is cool is that it's obvious what it's about in comparison with another field "modified_user_id". One points to the creator and the other to the most recent modifier of the record. Following your path to this kind of clarity one could name the fields "creator_id" and "modifier_id". Those are ok but in more complex schemas (esp ones that are not 'common' objects like "user") it might not be so obvious that the 'creator' part is an alias for 'user'. The approach I've taken so far makes it obvious but is more verbose of course. Not saying my legacy approach is better than your suggestion, but I did want to note the rationale for the pattern I arrived at ages ago. A decision to simplify in pgsql by using all lowercase forces me to rethink my entire namestyle practice...probably only seasoned devs can understand how weird that is to consider. Alexander Staubo wrote: > > On Nov 8, 2006, at 18:49 , novnov wrote: > >> I've been using namestyles with mixed case like OrgID. That is much >> more >> readable than orgid. Probably a good convention to adopt would be >> to use >> namestyles like org_id. That change I can live with. > > Both are perfectly acceptable, though the mixed-case version has > drawbacks. ANSI SQL's case-insensitivity makes mixed-case identifiers > something of a second-rate citizen; "orgid" and "OrgID" are both > going to be resolved to the same object, unless you explicitly double- > quote it. Ambiguity is rarely a problem, but because there are no > checks for consistency, inconsistencies tend to sneak in, especially > in team projects; some people might type "OrgID", some "OrgId", and > so on. > > Note that lower-case, underscore-delimited variable identifiers are > consistent with mainstream coding conventions for C, C++, Ruby, > Python and most languages in the Unix world (Java and JavaScript > being notable exceptions). After several years of trying to go > against the grain and be consistent across the board, I ended up > giving in and always using whatever is appropriate in the language/ > environment I work in. > >> But another issue is the way that I've been naming foreign key >> references. I >> tend to prefix field names with a table based acronym. So names like >> OrgName, OrgCity. Foreign key fields incorporate the ref'd pk. So, >> if I have >> a table tblEmployee, fk to the org table would be like EmpOrgID. I >> know many >> would simply use OrgID as the fk in tblEmployee, but I have liked >> compounding the names because is results in completely unambiguous >> field >> names throughout the db. If I'm giving up the mixed case naming, I >> could use >> emp_fname, emp_lname, emp_org_id, but I'm not sure if that's best. > > For column names, I recommend using whatever is natural in the > decribing a field, irrespective of what the field is actually > pointing towards. For example, a table representing an object with a > creator attribute pointing to a user would have a column "creator_id" > referencing "users (id)" -- not, say, "creator_user_id", which is > superfluous. The id suffix is there to tell me it's an identifier, > not the creator proper. > > In your case, in the table "organizations" the column names would be > "id", "name", "city" and so on, and a table "employees" would have a > column "organization_id" with a foreign-key reference. This simple > convention translates more easily to one's mental model of a query > such as "select all employees where the organization name is 'foo'", > which becomes: > > select * from employees > join organizations on id = organization_id > where organizations.name = 'foo' > > as opposed to > > select * from tblEmployees > join Org on OrgId = EmpOrgId > where Org.OrgName = 'foo' > > or something. > > I am curious as to why you need to prefix table names with "tbl" in > the first place. > > Alexander. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > -- View this message in context: http://www.nabble.com/Table-and-Field-namestyle-best-practices--tf2596761.html#a7299349 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 11/8/06, novnov <novnovice@gmail.com> wrote:
> I am very curious to hear the various conventions folks here have arrived
> at. I don't expect there to be consensus, but the various rationales might
> help me arrive at an approach that works well for me.
Personally I use all lower caps names a typical table might look:
CREATE TABLE names (
name_id serial PRIMARY KEY,
name varchar(100) UNIQUE NOT NULL,
birth date
);
CREATE INDEX names_birth_index ON names (birth)
CREATE INDEX names_name_lower_index ON names (lower(name));
CREATE TABLE winners (
winner_id serial PRIMARY KEY,
name_id integer REFERENCES names
);
CREATE VIEW winner_names_view AS
SELECT * FROM winners JOIN names USING (name_id);
...generally I don't like naming columns like 'id' -- if I put
full names, like name_id then JOIN ... USING(col_id) or
NATURAL JOINs are easy and straightforward.
Sometimes I put a trailing "_view" to mark that given table
is really a view. My index names are composed of
table_col1_col2_index or table_col1_function_index
(like the above lower() case). If index is unique,
I use "_key" as a suffix instead of "_index".
I know couple of people who name their tables like
T_Name, T_Winner etc. (and V_For_Views), but I consider
it a bit superfluous for my tastes. And if I have whole a lot
tables, I like to keep them organized into schemas, which
are powerful beings in PostgreSQL.
Regards,
Dawid
OK, thanks. I'm having a major internal debate about how I'm going to adjust
my habits to pgsql's 'lowercase is simplest' reality, all of this is
helpful.
One thing I've not been able to determine is if there are any characters
besides the standard alphanumeric ones and "_" that do not invoke the double
quoting of names. Are there any? If there were others, it'd be nice to know,
for possible use as namespace markers ("_" is most commonly used for this,
maybe it's the only real candidate).
Dawid Kuroczko wrote:
>
> On 11/8/06, novnov <novnovice@gmail.com> wrote:
>> I am very curious to hear the various conventions folks here have arrived
>> at. I don't expect there to be consensus, but the various rationales
>> might
>> help me arrive at an approach that works well for me.
>
> Personally I use all lower caps names a typical table might look:
>
> CREATE TABLE names (
> name_id serial PRIMARY KEY,
> name varchar(100) UNIQUE NOT NULL,
> birth date
> );
> CREATE INDEX names_birth_index ON names (birth)
> CREATE INDEX names_name_lower_index ON names (lower(name));
> CREATE TABLE winners (
> winner_id serial PRIMARY KEY,
> name_id integer REFERENCES names
> );
> CREATE VIEW winner_names_view AS
> SELECT * FROM winners JOIN names USING (name_id);
>
> ...generally I don't like naming columns like 'id' -- if I put
> full names, like name_id then JOIN ... USING(col_id) or
> NATURAL JOINs are easy and straightforward.
>
> Sometimes I put a trailing "_view" to mark that given table
> is really a view. My index names are composed of
> table_col1_col2_index or table_col1_function_index
> (like the above lower() case). If index is unique,
> I use "_key" as a suffix instead of "_index".
>
> I know couple of people who name their tables like
> T_Name, T_Winner etc. (and V_For_Views), but I consider
> it a bit superfluous for my tastes. And if I have whole a lot
> tables, I like to keep them organized into schemas, which
> are powerful beings in PostgreSQL.
>
> Regards,
> Dawid
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
--
View this message in context: http://www.nabble.com/Table-and-Field-namestyle-best-practices--tf2596761.html#a7308343
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Sun, Nov 12, 2006 at 02:21:10PM -0800, novnov wrote: > > OK, thanks. I'm having a major internal debate about how I'm going to adjust > my habits to pgsql's 'lowercase is simplest' reality, all of this is > helpful. Well, it's more like "no quoting is simplest", then all the identifiers are case-insensetive and you don't have to worry much about upper and lower case. You will read them back in lowercase though. But to answer your question, unquoted identifiers can contain letters, numbers and the underscore. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
> I'm having a major internal debate about how I'm going to adjust > my habits to pgsql's 'lowercase is simplest' reality.. FYI, I adapted by going to "quote everything". It takes getting used to, but now I never have to worry about any keyword conflicts, ever, past present or future. And I don't even think it's much more typing on the whole, because of how many _ characters I dropped. I now just use _ as "namespace separation", nothing else. Although the C++ side of me is tempted to use "::", just because I can ;-) -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice