Naming Convention for Databases

Essential Guidelines for Database Naming Conventions

Naming Convention

1. use lowercase characters
• eliminates question of proper case as well as errors related to case-sensitivity
• speeds typing rate and accuracy
• differentiates table and column names from uppercase SQL keywords

Note:- (Case Sensitivity:-The case sensitivity of the database depends on the operating system being installed. In Unix filesystems, filenames are usually case-sensitive. Old Windows filesystems (VFAT, FAT32) are not case-sensitive (there cannot be a readme.txt and a Readme.txt in the same folder) but are case-preserving, i.e. remembering the case of the letters. The original FAT12 filesystem was case-insensitive.Current Windows file systems, like NTFS, are case-sensitive, that is you can have a readme.txt and a Readme.txt in the same folder, however, Windows disallows you to create a second file differing only in case.)

2. separate words and prefixes with underlines, never use spaces
• promotes readability (e.g. book_name vs. bookname)
• avoid having to bracket names (e.g. [book name] or `book name` )(Yes [ ] and white space is a valid character on mySQL and Oracle, independent of the OS. but these are avoided because uniformity is not maintained)
• offers greater platform independence
3. avoid using numbers
• may be a sign of poor normalization, hinting at the need for a many-to-many relationship
4. Do not use dashes in database names.
5. Do not start object names with a letter(Donot start object name with letter  i.e. with numeric  or underscore or punctuators)
6. Keep names short but meaningful.  For example "SlQ" is too short. and "SalesFiguresForCompanyByFiscalQuarter" is too long.
7. The name of the object should make it pretty obvious what type of data it contains, and if for some reason it does not, then there is always the metadata tables and/or the documentation you should have written when designing the system. Using datatype-style prefixes for columns like IContactID (integer) and VEmail (varchar) not only make the column names harder to read, they also make them less flexible.

For Example

• Name of the project. Eg. nexty

• Postfix date fields with '_on'. Eg. added_on
• Primary key should be always 'id' (the primary key should always have postfix as '_id' eg. table_id, book_id)
• Reference table must be named in the format <First table><Second table> - Eg. PageTag
• Foreign key must be in the format '<table>_<primary key>' - all lower case - Eg. user_id

Table names

1. choose short, unambiguous names, using minimum number of words
• distinguish tables easily
• facilitates the naming of unique column names as well as lookup and linking tables
2. give tables singular names, never plural
• promotes consistency with naming of primary key columns and lookup tables
• ensures alphabetical ordering of a table before its lookup or linking tables
• avoid confusion of english pluralization rules to make database programming easier (e.g. activity becomes activities, box becomes boxes, person becomes people, data remains data, etc.)
• SQL statements read better, e.g. SELECT activity.name vs. SELECT activities.name
3. avoid abbreviated, concatenated, or acronym-based names
• promotes self-documenting design
• easier for developer and non-developer to read and understand
4. prefix lookup tables with the name of the table they relate to
• groups related tables together (e.g. activity_status, activity_type, etc.)
• prevents naming conflicts between generic lookup tables for different entities
5. for a linking (or junction) table, concatenate the names of the two tables being linked in alphabetical order
• orders linking table with a related entity table
• expresses composite purpose of the table
• this should be waived if the linking table has a natural, standard, or obvious name (e.g. “item” in: [order] 1 to M [item] M to 1 [product])
• this must be waived if there are multiple linking tables between the same two tables, (e.g. “student” and “instructor” between the tables “person” and “class”)

Column names

1. the primary key should be the singular form of the table name suffixed with “_id”
• allows primary key to be deduced/recalled from the table name alone (e.g. primary key of the book table would be book_id)
• consistent with the name of the foreign key
• prevents having to alias primary keys in programming
2. prefix the name of every column with the table name, excluding foreign keys
• prevents using “name”, “order”, “percent”, etc. as column names and clashing with SQL/RDBMS reserved words
• creates near unique column names (e.g. book_name, book_code, book_description, etc., often simplifying query design and SQL coding
• makes the column names consistent with the primary key
• differentiates foreign key columns from columns native to the table
• maintains semantic transparency of column names when using table aliases (e.g. SELECT a.activity_name FROM activity a)
• prevents naming a column the same name as the table
• (this point would be useful for more clarification. eg the field name 'code' for table names 'book' and 'author' can be differentiated using this point as book_code and author_code. )

3. foreign key columns should have the same name as the primary key to which they refer
• makes the table to which they refer completely obvious
• if there are multiple foreign keys referencing same table, prefix the foreign key column name with an appropriately descriptive adjective (e.g. lead_person_id, technical_person_id, etc. which transparently reference person_id in the person table)
4. suffix date-type columns with “_on”, suffix datetime-type columns with “_at”, and prefix boolean-type columns with “is_” or “has_” (to differentiate these specific kinds of data from other types)
• prevents confusing with more common text/number data types

Comparison between SQL , Mysql and Postgresql

Installing Postgresql On Ubuntu 12.04

Following are the variation for Ubuntu 12.04  from the book beginning PostgreSQL by Neil Mathew

adduser postgres

Creating the Database Directory
Next, you must create, as root, the directory PostgreSQL is going to use for its databases and
change its owner to be postgres:

mkdir /usr/lib/postgresql/9.1/data

chown postgres /usr/lib/postgresql/9.1/data

Here, we are using the default location for the database. You might choose to store the data
in a different location, as we discussed earlier, in the “Anatomy of a PostgreSQL Installation”
section.

Initializing the Database

You initialize the PostgreSQL database by using the initdb utility, specifying where in your file
system you want the database files to reside. This will do several things, including creating the
data structures PostgreSQL needs to run and creating an initial working database, template1.

You need to assume the identity of the postgres user to run the initdb utility. To do this,
the most reliable way is to change your identity in two steps, first becoming root with su and
then becoming postgres as follows. (As a normal user, you may not have permission to assume
another user’s identity, so you must become the superuser first.)
$ su
# su - postgres
pg$
Now the programs you run will assume the rights of the postgres user and will be able to
access the PostgreSQL database files. For clarity, we have shown the shell prompt for commands
executed as the postgres user as pg$.
■Caution Do not be tempted to shortcut the process of using the postgres user and run these programsas root. For security reasons, running server processes as root can be dangerous. If there were a problemwith the process, it could result in an outsider gaining access to your system via the network. For this reason,postmaster will refuse to run as root.
Initialize the database with initdb:

/usr/lib/postgresql/9.1/bin/initdb -D /usr/lib/postgresql/9.1/data

After Success. You can now start the database server using:
/usr/lib/postgresql/9.1/bin/postgres -D /usr/lib/postgresql/9.1/data (didnt worked)
or
/usr/lib/postgresql/9.1/bin/pg_ctl -D /usr/llib/postgresql/9.1/data -l logfile start (worked)

Starting the database server: As root. (from most to least favorite method)
$ service postgresql start
(If the database has not already been initialized with initdb, this will be performed by the command)  (Ref http://www.yolinux.com/TUTORIALS/LinuxTutorialPostgreSQL.html)
Important help worked for me
(PostgreSQL is a powerful, open source relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL92 and SQL99 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others.

pgAdmin III is the most popular and feature rich Open Sourceadministration and development platform for PostgreSQL, the most advanced Open Source database in the world. The application may be used on Linux, FreeBSD, OpenSUSE, Solaris, Mac OSX and Windows platforms to manage PostgreSQL 7.3 and above running on any platform, as well as commercial and derived versions of PostgreSQL such as EnterpriseDB, Mammoth PostgreSQL, Bizgres and Greenplum database.

pgAdmin III is designed to answer the needs of all users, from writing simple SQL queries to developing complex databases. The graphical interface supports all PostgreSQL features and makes administration easy. The application also includes a syntax highlighting SQL editor, a server-side code editor, an SQL/batch/shell job scheduling agent, support for the Slony-I replication engine and much more. Server connection may be made using TCP/IP or Unix Domain Sockets (on *nix platforms), and may be SSL encrypted for security. No additional drivers are required to communicate with the database server.

Install Postgresql and pgadmin3 in Ubuntu

PostgreSQL 8.2 version will be installed in Ubuntu 7.10 (Gutsy Gibbon)

sudo apt-get install postgresql-8.2 postgresql-client-8.2 postgresql-contrib-8.2

sudo apt-get install pgadmin3

This will install the database server/client, some extra utility scripts and the pgAdmin GUI application for working with the database.

Configuring postgresql in Ubuntu

Now we need to reset the password for the ‘postgres’ admin account for the server

sudo su postgres -c psql template1

template1=# ALTER USER postgres WITH PASSWORD ‘password';

template1=# \q

That alters the password for within the database, now we need to do the same for the unix user ‘postgres’:

sudo passwd -d postgres ( from the unprivilged user )

sudo su postgres -c passwd (password is dh^^^^^^^^^)

Now enter the same password that you used previously.

from here on in we can use both pgAdmin and command-line access (as the postgres user) to run the database server. But before you jump into pgAdmin we should set-up the PostgreSQL admin pack that enables better logging and monitoring within pgAdmin. Run the following at the command-line

we need to open up the server so that we can access and use it remotely -- unless you only want to access the database on the local machine. To do this, first, we need to edit the postgresql.conf file:

sudo gedit /etc/postgresql/9.1/main/postgresql.conf

Now, to edit a couple of lines in the ‘Connections and Authentication’ section

Change the line

#listen_addresses = ‘localhost'

to

listen_addresses = ‘*'

and also change the line

#password_encryption = on

to

password_encryption = on

Then save the file and close gedit.

Now for the final step, we must define who can access the server. This is all done using the pg_hba.conf file.

sudo gedit /etc/postgresql/9.1/main/pg_hba.conf

Comment out, or delete the current contents of the file, then add this text to the bottom of the file

#DO NOT DISABLE!

# If you change this first entry you will need to make sure that the

# database

# super user can access the database using some other method.

# Noninteractive

# access to all databases is required during automatic maintenance

# (autovacuum, daily cronjob, replication, and similar tasks).

#

# Database administrative login by UNIX sockets

local all postgres ident sameuser

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only

local all all md5

# IPv4 local connections:

host all all 127.0.0.1/32 md5

# IPv6 local connections:

host all all ::1/128 md5

# Connections for all PCs on the subnet

#

# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD

host all all [ip address] [subnet mask] md5

and in the last line, add in your subnet mask (i.e. 255.255.255.0) and the IP address of the machine that you would like to access your server (i.e. 138.250.192.115). However, if you would like to enable access to a range of IP addresses, just substitute the last number for a zero and all machines within that range will be allowed access (i.e. 138.250.192.0 would allow all machines with an IP address 138.250.192.x to use the database server).

That’s it, now all you have to do is restart the server

sudo /etc/init.d/postgresql-8.2 restart

That's it you can start using postgresql in Ubuntu

Create a Database from command line

You can also use pgadmin3 for all postgresql related

To create a database with a user that have full rights on the database, use the following command

sudo -u postgres createuser -D -A -P mynewuser

sudo -u postgres createdb -O mynewuser mydatabase

) http://www.ubuntugeek.com/howto-setup-database-server-with-postgresql-and-pgadmin3.html


Here, we will add an entry to allow any computer on the local network (in this case the
subnet 192.168.x.x) to connect to any database with password authentication. (If you require
a different access policy, refer to the comments in the configuration file.) We add a line to the
end of pg_hba.conf that looks like this:
host        all        all        192.168.0.0/16    md5
This means that all computers with an IP address that begins 192.168 can access all databases.
Alternatively, if we trust all of the users on all of the machines in a network, we can allow
unrestricted access by specifying trust as the authentication mechanism, like this:
host        all        all        192.168.0.0/16         trust
The PostgreSQL postmaster server process reads a configuration file, postgresql.conf
(also in the data directory) to set a number of runtime options, including (if not otherwise
specified in a -D option or the PGDATA environment variable) the location of the database data
files. The configuration file is well commented, providing guidance if you need to change any
settings. There is also a section on runtime configuration in the PostgreSQL documentation.
As an example, we can allow the server to listen for network connections by setting the
listen_addresses variable in postgresql.conf, instead of using the now deprecated -i option
to postmaster, as follows:
listen_addresses='*'
In fact, setting configuration options in postgresql.conf is the recommended approach
for controlling the behavior of the postmaster process.


Starting the postmaster Process
Now you can start the server process itself. Again, you use the -D option to tell postmaster
where the database files are located. If you want to allow users on a network to access your
data, you can specify the -i option to enable remote clients (if you haven’t enabled
listen_addresses in postgresql.conf, as in the preceding example):
/usr/lib/postgresql/9.1/bin/postmaster -i -D /usr/llib/postgresql/9.1/data >logfile 2>&1 & (run from the unprivilged user)

for pg_ctl run
/usr/lib/postgresql/9.1/bin/pg_ctl --version (it would tell us about the version  of the postgresql running)
i was unable to connect pgadmin3 to server then i modifies the pg_hba file to the following contents

# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# Refer to the "Client Authentication" section in the PostgreSQL
# documentation for a complete description of this file.  A short
# synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access.  Records take one of these forms:
#
# local      DATABASE  USER  METHOD  [OPTIONS]
# host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
#
# (The uppercase items must be replaced by actual values.)
#
# The first field is the connection type: "local" is a Unix-domain
# socket, "host" is either a plain or SSL-encrypted TCP/IP socket,
# "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a
# plain TCP/IP socket.
#
# DATABASE can be "all", "sameuser", "samerole", "replication", a
# database name, or a comma-separated list thereof. The "all"
# keyword does not match "replication". Access to replication
# must be enabled in a separate record (see example below).
#
# USER can be "all", a user name, a group name prefixed with "+", or a
# comma-separated list thereof.  In both the DATABASE and USER fields
# you can also write a file name prefixed with "@" to include names
# from a separate file.
#
# ADDRESS specifies the set of hosts the record matches.  It can be a
# host name, or it is made up of an IP address and a CIDR mask that is
# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that
# specifies the number of significant bits in the mask.  A host name
# that starts with a dot (.) matches a suffix of the actual host name.
# Alternatively, you can write an IP address and netmask in separate
# columns to specify the set of hosts.  Instead of a CIDR-address, you
# can write "samehost" to match any of the server's own IP addresses,
# or "samenet" to match any address in any subnet that the server is
# directly connected to.
#
# METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",
# "krb5", "ident", "peer", "pam", "ldap", "radius" or "cert".  Note that
# "password" sends passwords in clear text; "md5" is preferred since
# it sends encrypted passwords.
#
# OPTIONS are a set of options for the authentication in the format
# NAME=VALUE.  The available options depend on the different
# authentication methods -- refer to the "Client Authentication"
# section in the documentation for a list of which options are
# available for which authentication methods.
#
# Database and user names containing spaces, commas, quotes and other
# special characters must be quoted.  Quoting one of the keywords
# "all", "sameuser", "samerole" or "replication" makes the name lose
# its special character, and just match a database or username with
# that name.
#
# This file is read on server startup and when the postmaster receives
# a SIGHUP signal.  If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect.  You can
# use "pg_ctl reload" to do that.

# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local connections, you need to add more
# "host" records.  In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.




# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5



ON 22/10/12 I WAS starting the server using command service postgresql start as root user but it give the error in file /etc/postgresql/9.1/main/postgresql,conf then i changed  contents of this file by coping from the file in email account .
then i  start the server and it started working

Installing PHPpgadmin

use command sudo apt-get install phppgadmin

then configure the file /etc/phppgadmin/config.inc.conf  in that  file look 'extra_login_security’ =true change the same to false then try to connect postgresql through phppgadmin it would work