Re: [GENERAL] Table and Field namestyle best practices?
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. signature.asc Description: Digital signature
Re: [GENERAL] Table and Field namestyle best practices?
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 [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Table and Field namestyle best practices?
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Table and Field namestyle best practices?
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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Table and Field namestyle best practices?
On 11/8/06, novnov [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Table and Field namestyle best practices?
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
Re: [GENERAL] Table and Field namestyle best practices?
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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Table and Field namestyle best practices?
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
Re: [GENERAL] Table and Field namestyle best practices?
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. ---(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
Re: [GENERAL] Table and Field namestyle best practices?
On 11/8/06, novnov [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Table and Field namestyle best practices?
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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Table and Field namestyle best practices?
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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
