The current US financial situation has claimed a victim in the Web 2.0 world — Uber. I’m not sure if this is the first significant name, but it will not be the last site running MySQL where investors will be quick to cut losses and move on.
The current US financial situation has claimed a victim in the Web 2.0 world — Uber. I’m not sure if this is the first significant name, but it will not be the last site running MySQL where investors will be quick to cut losses and move on.
Every website page content uses two basic elements, HyperText Markup Language (HTML) and Cascading Style Sheets (CSS). Each of these has various standards, HTML has versions such as 3.2, 4.0, 4.01, and the new XHTML 1.0,1.1, 2.0 along with various version flavors know as strict, transitional & frameset. CSS also has various versions including 1, 2 and 3.
Each browser renders your combined HTML & CSS differently. The look and feel can vary between FireFox, Safari, Chrome, Internet Explorer and the more less common browsers. Indeed each version of a product also renders different. With IE 8 just being released, it’s common versions now are 5.0, 5.5, 6.0 and 7.0. This product alone now has 5 versions that UI designers must test and verify.
To minimize presentation and rendering problems, adhering to the standards can only assist, and greatly benefit the majority of entrepreneurs, designers and developers that are not dedicated resources. There are two excellent online tools from the standards body, the World Wide Web Consortium (W3C) that an easily assist you.
You can also link directly to these sites, so it’s easier to validate your HTML and CSS directly from your relevant webpage. For example, HTML Validation and CSS.
It’s not always possible to meet the standards, and when you are not the full-time developer of your site, it can be time consuming if you don’t check early and regularly.
Indexes are a great way to improve performed in a MySQL database, when used appropriately.
When used in-appropriately the impact can be a degradation of performance.
The following example from Movable Type shows how when reviewing the slow query log I found numerous occurrences of Inserts take 3 or more seconds, with no reported lock contention time for this insert.
# Query_time: 3 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 SET insert_id=6281; INSERT INTO mt_comment (comment_author, comment_blog_id, comment_commenter_id, comment_created_by, comment_created_on, comment_email, comment_entry_id, comment_ip, comment_junk_log, comment_junk_score, comment_junk_status, comment_last_moved_on, comment_modified_by, comment_modified_on, comment_parent_id, comment_text, comment_url, comment_visible) VALUES (...)
The impact here, is that SELECT statements to the mt_comment table are also blocked because this table is in MyISAM. It was reviewing slow running SELECT statements that the cause of the slow inserts was easily determined.
mysql> explain SELECT comment_id -> FROM mt_comment -> WHERE (comment_visible = '1') AND (comment_blog_id = '3') AND (comment_entry_id = '276') -> ORDER BY comment_created_on DESC; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: mt_comment type: ref possible_keys: mt_comment_visible,mt_comment_entry_id,mt_comment_blog_id,mt_comment_blog_stat,mt_comment_visible_date,mt_comment_entry_visible,mt_comment_blog_visible,mt_comment_blog_ip_date,mt_comment_blog_url key: mt_comment_entry_visible key_len: 6 ref: const,const rows: 99 Extra: Using where 1 row in set (0.00 sec) CREATE TABLE `mt_comment` ( `comment_id` int(11) NOT NULL auto_increment, `comment_author` varchar(100) default NULL, `comment_blog_id` int(11) NOT NULL default '0', `comment_commenter_id` int(11) default NULL, `comment_created_by` int(11) default NULL, `comment_created_on` datetime default NULL, `comment_email` varchar(75) default NULL, `comment_entry_id` int(11) NOT NULL default '0', `comment_ip` varchar(16) default NULL, `comment_junk_log` mediumtext, `comment_junk_score` float default NULL, `comment_junk_status` smallint(6) default '0', `comment_last_moved_on` datetime NOT NULL default '2000-01-01 00:00:00', `comment_modified_by` int(11) default NULL, `comment_modified_on` datetime default NULL, `comment_parent_id` int(11) default NULL, `comment_text` mediumtext, `comment_url` varchar(255) default NULL, `comment_visible` tinyint(4) default NULL, PRIMARY KEY (`comment_id`), KEY `mt_comment_commenter_id` (`comment_commenter_id`), KEY `mt_comment_visible` (`comment_visible`), KEY `mt_comment_junk_score` (`comment_junk_score`), KEY `mt_comment_ip` (`comment_ip`), KEY `mt_comment_parent_id` (`comment_parent_id`), KEY `mt_comment_entry_id` (`comment_entry_id`), KEY `mt_comment_email` (`comment_email`), KEY `mt_comment_last_moved_on` (`comment_last_moved_on`), KEY `mt_comment_created_on` (`comment_created_on`), KEY `mt_comment_junk_status` (`comment_junk_status`), KEY `mt_comment_blog_id` (`comment_blog_id`), KEY `mt_comment_blog_stat` (`comment_blog_id`,`comment_junk_status`,`comment_created_on`), KEY `mt_comment_visible_date` (`comment_visible`,`comment_created_on`), KEY `mt_comment_entry_visible` (`comment_entry_id`,`comment_visible`,`comment_created_on`), KEY `mt_comment_blog_visible` (`comment_blog_id`,`comment_visible`,`comment_created_on`,`comment_id`), KEY `mt_comment_blog_ip_date` (`comment_blog_id`,`comment_ip`,`comment_created_on`), KEY `mt_comment_junk_date` (`comment_junk_status`,`comment_created_on`), KEY `mt_comment_blog_url` (`comment_blog_id`,`comment_visible`,`comment_url`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1
As you can see, the table has 18 indexes. This means that for every row inserted, 18 separate index inserts are required.
When adding an Index to a table, first determine the usage patterns that will use the index, consolidating indexes when possible and removing obvious duplicates (in the above example, the single column comment_blog_id is a classic duplicate index).
Adding an index will generally help SELECT performance, depending on cardinality, but will always impact INSERT,UPDATE and DELETE performance.
Another down side of too many indexes is the MySQL optimizer has much more work to do to eliminate beneficial indexes for every Query Execution Plan (QEP) that is undertaken.
Indeed I have seen worse, in one case a table with ~120 columns, move then 20 single column indexes AND a 3 part primary key summing 40 bytes in InnoDB. The impact was terrible for performance, with the Index size being 3x times the data size.
Ronald Bradford, Principal of 42SQL provides Consulting and Advisory Services in Data Architecture, Performance and Scalability for MySQL Solutions. An IT industry professional for two decades with extensive database experience in MySQL, Oracle and Ingres his expertise covers data architecture, software development, migration, performance analysis and production system implementations. His knowledge from 10 years of specialized consulting across many industry sectors, technologies and countries has provided unique insight into being able to provide solutions to problems. For more information Contact Ronald.
Choosing a domain name for your brand identity is the start. Protecting your domain name by registering for example .net, .org, and the many more extensions is one step in brand identity.
However a recent very unpleasant experience in New York, resulted in realizing some companies also register undesirable domain names. I was one of many unhappy people, mainly tourists as I was showing an Australian friend the sights of New York. We had chosen to use the City Sights NY bus line, but we caught with some 100+ people in a clear “screw the paying customers” experience.
I was really annoyed that my friend, only in New York for 2 days both had to experience this, and missed out on a night tour. I commented, I’m going to register citysightsnysucks.com, and share the full story of our experience, directing people to use Gray Line New York, which clearly by observation were providing the service we clearly did not get.
To my surprise, the domain name was already taken. To my utter surprise, the owner of the domain is the same as citysightsny.com. Did they do this by choice, or did another unhappy person (at least in 2006) register this, only to be perhaps threatened legally to give up the domain.
I would generally recommend in brand identity this approach, especially when select common misspellings and hyphenated versions if applicable can easily lead to a lot of domain names for your brand identity.
$ whois citysightsny.com Whois Server Version 2.0 Domain names in the .com and .net domains can now be registered with many different competing registrars. Go to http://www.internic.net for detailed information. Domain Name: CITYSIGHTSNY.COM Registrar: INTERCOSMOS MEDIA GROUP, INC. D/B/A DIRECTNIC.COM Whois Server: whois.directnic.com Referral URL: http://www.directnic.com Name Server: NS0.DIRECTNIC.COM Name Server: NS1.DIRECTNIC.COM Status: clientDeleteProhibited Status: clientTransferProhibited Status: clientUpdateProhibited Updated Date: 31-dec-2006 Creation Date: 28-nov-2004 Expiration Date: 28-nov-2011 Registrant: CitySights New York LLC 15 Second Ave Brooklyn, NY 11215 US 718-875-8200x103 Fax:718-875-7056 Domain Name: CITYSIGHTSNY.COM
$ whois citysightsnysucks.com Whois Server Version 2.0 Domain names in the .com and .net domains can now be registered with many different competing registrars. Go to http://www.internic.net for detailed information. Domain Name: CITYSIGHTSNYSUCKS.COM Registrar: INTERCOSMOS MEDIA GROUP, INC. D/B/A DIRECTNIC.COM Whois Server: whois.directnic.com Referral URL: http://www.directnic.com Name Server: NS.PUSHONLINE.NET Name Server: NS2.PUSHONLINE.NET Status: clientDeleteProhibited Status: clientTransferProhibited Status: clientUpdateProhibited Updated Date: 26-jun-2007 Creation Date: 11-aug-2006 Expiration Date: 11-aug- Registrant: CitySights New York LLC 15 Second Ave Brooklyn, NY 11215 US 718-875-8200x103 Fax:718-875-7056 Domain Name: CITYSIGHTSNYSUCKS.COM
Why you do not use GRANT ALL ON *.*?
I was with a client today, and after rebooting a MySQL 5.0.22 instance cleanly with /etc/init.d/mysqld service, I observed the following error, because you always check the log file after starting MySQL.
080923 16:16:24 InnoDB: Started; log sequence number 0 406173600 080923 16:16:24 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.22-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 080923 16:16:24 [ERROR] /usr/libexec/mysqld: Table './schema_name/table_name' is marked as crashed and should be repaired 080923 16:16:24 [Warning] Checking table: './schema_name/table_name'
Now, I’d just added to the /etc/my.cnf a number of settings including:
which explains the last line of the log file. When attempting to connect to the server via the mysql client I got the error:
“To many connections”
So now, I’m in a world of hurt, I can’t connect to the database as the ‘root’ user to observe what’s going on. I know that table it’s decided to repair is 1.4G in size, and the server is madly reading from disk. Shutting down the apache server that was connecting to the database is not expected to solve the problem, and does not, because connections must wait to timeout.
MySQL reserves a single super privileged connection, i.e. ‘root’ to the mysql server specifically for this reason, unless all the connections have this privilege. The problem, as often experienced with clients, is the permissions of the application user is simply unwarranted.
mysql> select host,user,password from mysql.user; +-----------+-------------+------------------+ | host | user | password | +-----------+-------------+------------------+ | localhost | root | 76bec9cc7dd32bc0 | | xxxxxx | root | | | xxxxxx | | | | localhost | | | | % | xxxxxxxxxxx | 0716d6776318d605 | | localhost | xxxxxxxxxxx | 0716d6776318d605 | | localhost | xxxxxxx | 6885269c4a550a03 | +-----------+-------------+------------------+ 7 rows in set (0.00 sec) mysql> show grants for [email protected]; +---------------------------------------------------------------------------------------+ | Grants for [email protected] | +---------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO xxxxxxx'@'localhost' IDENTIFIED BY PASSWORD '6885269c4a550a03' | | GRANT ALL PRIVILEGES ON `xxxxxxx`.* TO 'xxxxxxx'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
So the problem is ALL PRIVILEGES is granted to an application user. Never do this!
The solution is to remove all unused users, anonymous users, and create the application user with just the privileges needed.
DROP USER [email protected]; DROP USER [email protected]'%'; DELETE FROM mysql.user WHERE user=''; FLUSH PRIVILEGES; DROP USER [email protected]; CREATE USER [email protected] IDENTIFIED BY 'xxxxxxx'; GRANT SELECT,INSERT,UPDATE,DELETE ON xxxxxxx.* [email protected];
Domain names historically have been www.example.com, written also with the protocol prefix http://www.example.com, but in reality www. is optional, only example.com is actually needed.
www. is technically a sub-domain and sub-domains incur a small penalty in search engine optimization.
There is no right or wrong. What is important is that you choose one, and the other needs to be a 301 Permanent Redirect to the one you have chosen.
You also need to know that creating a server alias in your web server configuration, for example Apache or Tomcat is not a permanent redirect, in-fact it is technically duplicate content, with two web sites the same also incurring a penalty for search engine rating.
So what do the big players do. Here are a few.
Do not use www
Show duplicate content
Curiously youtube.com uses a 303 redirect, microsoft.com, stumbleupon.com and craigslist.org a 302 redirect.
How do you check? Use a CLI tool such as wget.
$ wget www.google.com --2008-09-22 19:56:48-- http://www.google.com/ Resolving www.google.com... 184.108.40.206, 220.127.116.11, 18.104.22.168, ... Connecting to www.google.com|22.214.171.124|:80... connected. HTTP request sent, awaiting response... 200 OK $ wget google.com --2008-09-22 19:57:56-- http://google.com/ Resolving google.com... 126.96.36.199, 188.8.131.52, 184.108.40.206 Connecting to google.com|220.127.116.11|:80... connected. HTTP request sent, awaiting response... 301 Moved Permanently Location: http://www.google.com/ [following] $ wget www.facebook.com --2008-09-22 20:07:59-- http://www.facebook.com/ Resolving www.facebook.com... 18.104.22.168 Connecting to www.facebook.com|22.214.171.124|:80... connected. HTTP request sent, awaiting response... 200 OK Length: unspecified [text/html] $ wget facebook.com --2008-09-22 19:59:43-- http://facebook.com/ Resolving facebook.com... 126.96.36.199, 188.8.131.52 Connecting to facebook.com|184.108.40.206|:80... connected. HTTP request sent, awaiting response... 301 Moved Permanently Location: http://www.facebook.com/ [following] $ wget digg.com --2008-09-22 20:10:47-- http://digg.com/ Resolving digg.com... 220.127.116.11 Connecting to digg.com|18.104.22.168|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 15322 (15K) [text/html] $ wget www.digg.com --2008-09-22 20:14:06-- http://www.digg.com/ Resolving www.digg.com... 22.214.171.124 Connecting to www.digg.com|126.96.36.199|:80... connected. HTTP request sent, awaiting response... 301 Moved Permanently Location: http://digg.com/ [following] $ wget twitter.com --2008-09-22 20:26:18-- http://twitter.com/ Resolving twitter.com... 188.8.131.52 Connecting to twitter.com|184.108.40.206|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 2655 (2.6K) [text/html] $ wget www.twitter.com --2008-09-22 20:26:41-- http://www.twitter.com/ Resolving www.twitter.com... 220.127.116.11 Connecting to www.twitter.com|18.104.22.168|:80... connected. HTTP request sent, awaiting response... 301 Moved Permanently Location: http://twitter.com/ [following]
Professionally, I prefer shorter and simpler without www.
Gary Vaynerchuk spoke next at Web 2.0 NY on Building Personal Brand Within the Social Media Landscape.
He was hilarious. His video presentation is available online, to share with others. He is inspirational for new young entrepreneur and I’d love to see him talk at Ultra Light Startups
His talk was simply “Patience and Passion”.
Words of wisdom included.
In closing, you have to do what you love.
Next on the Web 2.0 keynote speaker list was Maria Thomas of www.etsy.com with her talk The DIY Guide to Growing a Company.
If never heard of Etsy before – Your place to buy & sell all things handmade, interesting site. Most companies start small, and stay small. Only 0.1 of 1% grow to any size (e.g. > $250 Million) Esty, this year has 100M in revenue, Amazon is about $2 billion.
The opening lines included the message “Don’t loose the essence of who you are, and what you want to achieve.” and the term Filotimo.
It was an interesting point about qualifications “I got my Internet Degree at Amazon.com, then building digital media business at NPR – National Public Radio.”
Some more quotes from this discussion.
Some points of reference from the next Web 2.0 keynote by Jason Fried of 37 Signals
The DNA of your company has to able to say no.
I needed a rest from my opening keynote review NY Tech 1995-2008. Opening Web 2.0 Expo NY Keynote but a few siginificant points from The Death of the Grand Gesture by Deb Schultz.
Web 2.0 Expo NY keynotes are happening today. Technology in use included CrowdVine which I’d not heard of, and plenty of Twitter feeds such as w2e_NY08.
The opening keynote was Fred Wilson from Union Square Ventures with his presentation New York’s Web Industry From 1995 to 2008: From Nascent to Ascendent .
Some stats, Seed and early stage deals.
Fred first asked “New York is not an alley. Call it Broadway, or just New York.”
Here is a summary of his history of New York Web Industry.
New York is now 1/3 of Silicon valley, compared to 1/8 of funded Internet companies.
One thing mentioned is a documentary called “We live in Public”. Some of the footage from 1999, is so early Big Brother.
I will be attending next week’s Web 2.0 Expo 2008 in New York.
Garys Guide has a schedule of the key events and off site associated event parties.
It will be a bit of a change from the typical MySQL Conferences and recent OSCON Conference I have attended this year.
The Keynote titles gives you an indication of the variety of talks expected.
While working for a client, I had need to produce canned results of certain different criteria, recording the result in a table for later usage, and keep the position within each result.
Knowing no way to do this via a single INSERT INTO … SELECT statement, I reverted to using a MySQL Stored Procedure. For example, using a sample I_S query and the following snippet:
... DECLARE list CURSOR FOR SELECT select table_name from information_schema.tables where table_schema='INFORMATION_SCHEMA'; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=TRUE; OPEN list; SET result_position = 1; SET done = FALSE; lab: LOOP FETCH list INTO table_name; IF done THEN CLOSE list; LEAVE lab; END IF; INSERT INTO summary_table(val,pos) VALUES (table_name,result_position); SET result_position = result_position + 1; END LOOP;
However, in reviewing with another colleague after writing some 10+ different queries and SP loops, I realized that it is possible to record the position of each row in a result set using session variables, negating the need for all that code.
SET @rowcount = 0; SELECT table_name, @rowcount := @rowcount + 1 FROM information_schema.tables WHERE table_schema = 'INFORMATION_SCHEMA'; +---------------------------------------+----------------------------+ | table_name | @rowcount := @rowcount + 1 | +---------------------------------------+----------------------------+ | CHARACTER_SETS | 1 | | COLLATIONS | 2 | | COLLATION_CHARACTER_SET_APPLICABILITY | 3 | | COLUMNS | 4 | | COLUMN_PRIVILEGES | 5 | | ENGINES | 6 | | EVENTS | 7 | | FILES | 8 | | GLOBAL_STATUS | 9 | | GLOBAL_VARIABLES | 10 | | KEY_COLUMN_USAGE | 11 | | PARTITIONS | 12 | | PLUGINS | 13 | | PROCESSLIST | 14 | | PROFILING | 15 | | REFERENTIAL_CONSTRAINTS | 16 | | ROUTINES | 17 | | SCHEMATA | 18 | | SCHEMA_PRIVILEGES | 19 | | SESSION_STATUS | 20 | | SESSION_VARIABLES | 21 | | STATISTICS | 22 | | TABLES | 23 | | TABLE_CONSTRAINTS | 24 | | TABLE_PRIVILEGES | 25 | | TRIGGERS | 26 | | USER_PRIVILEGES | 27 | | VIEWS | 28 | +---------------------------------------+----------------------------+ 28 rows in set (0.01 sec)
Of course you need the all important SET before each query, if not specified however, the subsequent query does not result in an error, just NULL.
So all I needed was:
INSERT INTO summary_table(val,pos) SELECT table_name, @rowcount := @rowcount + 1 FROM information_schema.tables WHERE table_schema = 'INFORMATION_SCHEMA';
A simple and trivial solution.
How this performs under load, and how it is supported in different and future versions of MySQL is not determined.
Do you have a full time System Administrator? Do you have only a part-time SA, or none at all?
Packet General’s Data Security and PCI Compliance solutions run on a dedicated appliance, based on a “Just Enough Operating System” (JeOS) to minimize exposure.
This appliance actually improves not just the security of your data, but ensures your Operating System is secure and up to date. With only 4 services and a footprint < 600MB this is an ideal solution for running even a normal MySQL installation. Security upgrades can also be provided as an automated feature, eliminating the need for this management internally.
Tomorrow in the MySQL Webinar How to secure MySQL data and achieve PCI compliance which is being held Thursday, September 11, 2008, 10:00 am PST, 1:00 pm EST, 18:00 GMT we will be discussing this in more detail.
It started with del.icio.us/ (which now ironically redirects to http://delicious.com), and now it’s becoming more the trend to create a domain name with the extension included for effect.
With unique .com domains harder to come by, and dropping vowels like flickr.com so last generation, some countries must be trying to cash in on the success such as Tuvalu which has something like 10% of GDP from domain name sales of .tv.
Some recent names I’ve noticed are http://identi.ca, http://chi.mp and http://cyclo.ps.
I have even considered some recent projects using this new trend, but the combination of either the 2 letter extension not existing (For example .ld) or it’s not possible to get domains from a registrar (For example .er) it will take some time.
This week I will be the moderator for a MySQL Webinar How to secure MySQL data and achieve PCI compliance being held Thursday, September 11, 2008, 10:00 am PST, 1:00 pm EST, 18:00 GMT.
Recently I wrote about Do you store credit cards in your MySQL Database?. If you do, then PCI Compliance is not something you can ignore.
This webinar will not only be discussing PCI Compliance, but also MySQL data security. Our panel includes Didier Godart from MasterCard Worldwide, one of three members who drafted the Payment Card Industry Data Security Standard 1.0.
For more information on the various PCI Compliance and Encryption options for MySQL , check out the Packet General website.
Tonight over discussion was Android and what is Google’s ultimate direction. Have they lost their way, or are they just planning to explode with so many new things that will revolutionize what and how we do things. With $475,000 first price for Android, they certainly have the money available to invest in new directions.
I arrive home, and find email discussion on The Google Browser – Chrome.
Inquisitive, I take a look, to find the great teaser, nothing by a comic, come back tomorrow for the download link. Is that clever to leak information, have everybody write about it and check back tomorrow?
It’s important that for any software application good standards exist. Standards ensure a number of key considerations. Standards are necessary to enforce and provide reproducible software and to provide a level of quality in a team environment, ease of readability and consistency.
If you were going to create a MySQL Naming Standard you have to make a number of key decisions. Generally there is no true right or wrong, however my goals tend towards readability and simplicity. In 2 decades of database design I’ve actually changed my preference between some of these points.
All database objects are defined in the logical form, that being singular.
For example: box, customer, person, category, user, order, order_line product, post, post_category
For database tables & views, objects are defined in plural. For columns, objects are defined singular.
For example: boxes, customers, people, categories, users, orders, order_lines, products, posts, post_categories
Inconsistency between table name and column name, when using plural. Column names simply are not plural.
When the plural of the name is a completely different spelled word. For example a table of People, and a primary key of PersonId.
When the plural rule is not adding ‘s’, for example replacing ‘y’ with ‘ies’ as with Category.
Strict rule necessary for relationship and intersection tables. Generally, only the last portion is plural.
What about other objects, such as stored procedures for example.
All database objects should be specified as lowercase only. Words are separated with ‘underscores’.
For example: customer, customer_history, order, order_line, product, product_price, product_price_history
All database objects use CamelCase. Words are separated via Case.
For example: Customer, CustomerHistory, Order, OrderLine, Product, ProductPrice, ProductPriceHistory
Some database products have restrictions here, Oracle for example, UPPERCASES all objects. MySQL allows for both, except for some Operating Systems that does not support Mixed Case properly (e.g. Microsoft)
In a related post I will be discussing natural and surrogate keys. For this purpose, we will assume you are using surrogate keys.
All keys will have a standard name. For Example: id, key, sgn (system global number)
When referencing primary keys and foreign keys, what standard is used?
The primary key is the same name across all tables, making it easy to know the primary key of a table.
For foreign keys, the name is prefixed with the table name or appropriate table alias.
For Example: id. The foreign key would be customer_id, order_id, product_id
The primary key is defined as unique across the system, and as such the foreign key is the same as the primary key.
For Example: customer_id, order_id, product_id
When self referencing columns to a table, having a standard is also appropriate, for example parent_product_id.
For the given type of object, have a standard prefix or suffix.
For Example, all tables are prefixed with tbl, all views are suffixed with _view, all columns for a given table are prefixed with table alias.
Don’t prefix or suffix an object with it’s object type.
Don’t use them. When a word is reserved, find a more description name.
E.g. system_user, order_date,
For Example: user, date, group, order
A number of database systems do not allow the use of reserved words, or historically have not. Sum such as MySQL for example, allow reserved words, but only when additional quoting is used.
This indeed could be a entire topic it’s self. In simplicity, do you use abbreviations other then the most common and everybody knows abbreviations (to the point you don’t know what the abbreviation actually is in real life type abbreviations), or do you not.
I use the example of ‘url’. How many people actually know what url stands for. This is a common abbreviation.
For objects, use abbreviations when possible. Don’t use it for key tables, but for child and intersection tables.
For Example: invoice, inv_detail, inv_id,
Avoid abbreviations at all costs.
invoice, invoice_detail, invoice_id,
Unless you have a large schema (e.g. > 500 tables) the use of abbreviations should not be needed given the relative sizes of objects in modern databases.
There are a lot more considerations then these few examples for naming standards, however as with every design, it’s important to make a start and work towards continual improvement.
At the end of the day, I will work with what standard is in place. What I won’t work with is, when there is no documented, accountable standard.