Tag Info

Hot answers tagged

31

The table name prefix has very good reasons. Consider: TableA (id int identity, stringdata varchar(max)) TableB (id int identity, stringdata varchar(max)) We want to DELETE from TableA records that exist in both tables. Easy enough, we will just do an INNER JOIN: DELETE a FROM TableA A INNER JOIN TableB B ON b.id = B.id ....and we just ...


30

Table aliasing is a common and helpful practice. It saves you keystrokes when referencing columns anywhere in your query. It improves the readability of your SQL when you are referencing many tables. Aliases let you give those tables a short name plus a little meaning to how they are being used. It is even required when you join a table to itself or when ...


23

OK, first NEVER put tbl in front of a table's name. It's a table, we already now that. That's called Hungarian Notation, and people stopped doing that 5+ years ago. Just call the object based on what it is. If a table holds employee data call it "Employee". If it holds information about computers call it "Computer". If it maps computers to employees ...


23

Mostly it's to keep foreign keys from becoming a tremendous pain. Let's say you have two tables: Customer and CustomerAddress. The primary key for both is a column named id, which is an identity (int) column. Now you need to have the customer ID referenced from CustomerAddress. You can't name the column id, obviously, so you go with customer_id. This leads ...


20

Up to you. Just be consistent though. Personally I prefer singular based on what each *row" stores: Order, Products etc. This matches my modelling ((via Object Role Modelling) where I use singular entities/types. Edit: One reason is that plural fails when you have link tables: Orders, Products would give OrderProducts or OrdersProducts. Neither sounds ...


16

You should describe the purpose of the column and not necessarily the data type. You can include date/time/timestamp in the name, but you should also include the meaning. For example CreationDate StartDate Start End StatusTime Accessed Updated Adding Date/Time/Timestamp and such at the end is particularly useful when the abscence of the addition ...


13

Oracle has had a long standing limit on table names of 30 characters. I suspect this is a legacy issue based off an original 16 bit environment. The length of a table name could have some minuscule effect on performance as all the names have to be stored in a data dictionary and also parsed for queries but I don't think you could measure the hit. A more ...


11

We use schemas (think of them as namespaces in SQL perhaps) for both permissions and grouping. So instead of "tbl" etc we have Data.Thing Data.ThingHistory Data.Stuff No code goes into the Data schema. No tables live outside the Data schema. This can extended of course to have a Lookup or Staging schema if you wish. For views we use vw but this was to ...


10

Here's a summary of all the answers about the advantages obtained from the convention to not use a common name for all primary keys: Less mistakes, since the identity fields are not named the same You cannot mistakenly write a query that joins on B.Id = B.Id instead of A.Id = B.Id, because the identity fields will never be named the exact same. Clearer ...


9

I feel there are two things that still need to be said or elaborated: Naming things is not as trivial as it sounds Whilst short meaningless names are always bad, long names are not always good - our brains have an inbuilt tl;dr threshold which is surprisingly low. 30 chars is usually enough but I prefer the RDBMS to allow more for the exceptional cases ...


9

To copy my answer from the linked question: There is a situation where sticking "ID" on every table isn't the best idea: the USING keyword, if it's supported. We use it often in MySQL. For example, if you have fooTable with column fooTableId and barTable with foreign key fooTableId, then your queries can be constructed as such: SELECT fooTableId, ...


8

I think using aliases helps the readability of a query if the table names are long or so similar to each other that someone reading it quickly might mistake them. Do you think that this... SELECT Really_long_table_name.ID, Even_longer_table_name_than_before.Name, Even_longer_table_name_than_before.Description, Even_longer_table_name_than_before.State FROM ...


8

This has been asked before on SO. Where you have common and very ambiguous names, then prefix with table name. That is, anything you're liable to have to alias in almost every query. So for an Employee table I'd have EmployeeID EmployeeName Comment Salary StartDate EndDate InsertedDateTime ... And Wikipedia actually says: The USING construct is more ...


7

I'd rather have the environment be the suffix assuming I had to pick one convention. Otherwise, it's too difficult to navigate through lists of login names that all use the same first five characters. Of course, if given my choice, I would prefer to use neither the prefix nor the suffix. I'd rather keep the same username in all environments and vary my ...


7

The SQL standard defines that identifiers are not case sensitive (unless quoted). Your desire for CamelCase names conflicts with the standard and you'd better learn to live with it. Or you start quoting your identifiers and never forget to do so. Most RDBMS' actually implement standard(-like) behavior. Even MySQL tries, lately, but it's very hard to break ...


6

Laziness. IntelliSense and 3rd party options make typing a real tough excuse to justify. I'd much rather the names have meaningful and readable words.


6

How about xyz_at for a timestamp and xyz_on for a date field - eg start_at or start_on? Normally I'd avoid including the data type in the field name - much better if you can infer what you need to know about the type from the name of any field (a field called description is unlikely to be an integer) - but being able to tell the difference between a ...


6

Table names are like aptrx (Accounts Payable Transactions) and apmaster_all (curiously, this is the vendors table). It's an extremely complex database, so I was wondering if there was any logic to the convention or if it was simply being obfuscated intentionally or otherwise. Well-known abbreviations are usually preferable to spelling things ...


6

After normalizing a database schema to limit redundancy, tables are divided in smaller tables with established relations (one to one, one to many, many to many). In the process single fields in the original table can appear in multiple normalized tables. For instance a database for a blog could look like this in its unnormalized form, assuming a unique ...


5

Pick a standard, make sure it makes sense, and document it. There have been plenty of debates about this (e.g. whether the identity column in the contacts table should be called ID or ContactID, or whether the contacts table should be called dbo.Contacts or the unnecessarily verbose dbo.tblContacts), and you're never going to find agreement among your ...


5

Table aliasing (for the sake of shorter table names) isn't bad practice. I normally use it when the tablenames are long and then only use the alias that makes sense: SELECT tTable.stuff FROM track_table tTable; If you want to improve readability, you can use the AS keyword: SELECT tTable.stuff FROM track_table AS tTable; But, as you get used to the ...


5

I looked at your profile and it says you work with SQL Server and in SQL Server TIMESTAMP data type has nothing to do with date or time and its used to kind of version stamping the rows. This is very useful in identifying which rows have been modified from a given point of time. If you use TIMESTAMP then you don't have to specify a column name and SQL ...


5

Personally I'm a great fan of the Fanö Bedingung, meaning here that not name is allowed to be the beginning of another valid name. And second the Hamming distance between two names is better more than one different letter. Yes that are rule from predigital times, but they make life easier. And third names must be pronounceable, or you will became mad, ...


5

I use: created_at updated_at


5

One third-party vendor we hired to create something for us actually named all their identity columns Ident just to avoid using Id. Using "Ident" instead of "Id" doesn't really solve anything if "Ident" ends-up being used on all of their tables. There's a good article on SQL coding conventions on the Drupal site that indicates a good practice for this ...


5

I'm going to basically mirror Verace's comments and state this, making it semi-official: There is no one best practice that will cover every circumstance. What follows makes the following assumptions (and what to do if you haven't done this): You have already discussed this with your team (people working by themselves often just have to make up their ...


4

THe following book talks about using ID as a SQL antipattern and I gree with the author that it is. http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557/ref=sr_1_1?s=books&ie=UTF8&qid=1330025134&sr=1-1 This is a particular problem when you are doing complex reporting and need more than one id as you then have to ...


4

I name my columns CustomerID instead of ID, so whenever I type FROM dbo.Customers AS c JOIN dbo.CustomerOrders AS o SQL Prompt immediately suggests the following ON c.CustomerID = o.CustomerID It saves me a few keystrokes. Yet I think naming conventions are very subjective and as such I don't have a strong opinion one way or another.


4

This is a matter of style, and likely to get religious. Personally, I do find it valuable to explicitly label ID fields TeamID and PlayerID; as you say, it makes it perfectly clear that the field stores an ID and not some other type of data, such as a label. Being explicit is usually more valuable than being brief, in the long term. I prefer using ...


4

Table names have to be stored just like any other data (in the system catalog pg_class to be precise), and there are multiple objects that are typically named matching the table name (constraints, indexes, sequences, ...) so there is a marginal cost for longer strings. But you will not be able to notice any difference in performance. Internal references are ...



Only top voted, non community-wiki answers of a minimum length are eligible