Have Geek Brain

Is there a standard / convention for ordering columns in a database table definition?

Is there a standard convention on how columns should be ordered in a database table definition, and if so, what is the motivation for this standard? (Pros / Cons)

For example, should the primary key be the first column? Should foreign keys directly follow the primary key, or should they be placed in the far right corner of the table?

If there is a difference between the conventions for different DBMSs, the DBMS in this case is Microsoft SQL Server 2005.

Thanks / Erik

+1


a source to share


8 answers


I don't know of any standard, but the way we structure our columns

  • Primary key (s)
  • Any foreign keys
  • Data


Our large columns of data, such as comments, are placed at the end. This makes it easy to view as much data as possible in Query Analyzer without having to scroll to the right.

+6


a source


I always structure my tables like this:



  • Primary key (s)
  • Column tracking (DateModified, ModifiedBy, etc.)
  • Any foreign keys
  • Data
+2


a source


I'm not sure about the legend settings, but we always put the ID / Primary column as the first column in the table. I guess this is simply because it clearly shows pk. I would also say that with fk fields, but there is no real standard after that, could you do it by datatype?

Besides, this is not the best practice, just a personal choice.

+1


a source


No, which I know about. You can always reorder the columns during selection, so it hardly matters unless there is some really obscure optimization that you can do on some databases by ordering the columns accordingly.

+1


a source


There is no official standard that I am aware of, but it is common practice to take the primary key first (usually a surrogate) and then group the other fields by functionality. Thus, in the person table, you can group the fields containing the person's address data together. Most people add FKs, indexes, and other constraints outside of the actual table definition.

+1


a source


mm .. As far as I know, there are no restrictions. It's just a matter of clarity.

0


a source


I agree with most of the posts above, primary key first (at least). The rest is personal preference. If you have a standard, then stick to that standard.

I prefer to keep the columns quite logically together. Sometimes a fully normalized data structure is not suitable, so you have "minor entities" stored in the same table (ie, not removing NULLs). An example would be address fields or different phone, mobile phone, work phone columns placed together.

The most striking example I can give is HOW NOT TO DO IT. If the developer autogenerates the schema and the columns are created alphabetically (and even the PK was hidden in the middle of the table structure) then this is annoying MOST.

0


a source


As per edoode: -

Primary key(s) 
'Tracking' columns (DateModified, ModifiedBy and such) 
Any foreign keys 
Data

Plus

Fixed width 'not null' columns
Fixed width 'nullable' columns
Variable Width columns VARCHAR NVARCHAR etc.
CLOBS
BLOBS

You get the weeny little performance gain ever so small if you insert columns that can vary in length at the bottom of the table.

0


a source






More articles:


All Articles