对数据库表中的列排序 [英] Ordering columns in database tables

问题描述

关于数据库表中的列顺序，是否有任何标准或至少是最佳做法？

When it comes to column order in DB tables, are there any standards or at least best practices?

这是我遵循的手工约定：

Here's a handmade convention that I follow:

• 主键（即 id ）;


• 列（电子邮件， ssn ）;


• code> article ）;


• 列包含用户生成的数据（即 first_name ， last_name ）;


• 列包含系统生成的数据;
  
  
  
  
  • 非布尔值（即 password_hash ）;
  
  
  • boolean（即 deleted ， verified ）
  
  
  • primary key (i.e. id);
  • unique columns (i.e. email, ssn);
  • foreign keys (i.e. article);
  • columns holding user generated data (i.e. first_name, last_name);
  • columns holding system generated data;
    • non-boolean (i.e. password_hash);
    • boolean (i.e. deleted, verified)

    推荐答案

    总之，你已经说过了标准约定以及您不会错过。海事组织，唯一的动作，将使某人看起来不专业，将没有首要的主键。之后的外键是正确的，是一个很好的约定，但不是一个大交易。 （包括外键的多字段主键当然应该是最初的，或者有人应该被打败。）我还要再补充两个想法：

    In short, you've stated the standard conventions well and you're not missing a lot. IMO, the only move that would make someone look unprofessional would be not having the Primary Key(s) first. Having the foreign keys come right after that is a nice convention, but not a big deal. (Multi-field primary keys that include foreign keys should of course be at the very beginining, or someone should be beaten.) I would add two additional thoughts:

    
    
    1. 具有相似主题的字段彼此靠近。例如，城市/州/ Zip字段被广泛分离将是无益的。我认为，无论user_role还是user_ip是第一个，但它们听起来应该是彼此相邻。
    
    
    2. 除了其他这样的约定，它不会伤害
    
    

    在数据库中添加其他约定是一个非常好的主意你提到总是有时间戳在结束）。如果你在很多表中有ChangeDate和ChangeBy字段，那么它们（彼此相邻并且一致）位置一致就是好的。

    Having additional conventions within your database is a very good idea (like as you mention always having the timestamp at the end). If you have ChangeDate and ChangeBy fields in a lot of your tables, having them (obvously next to each other and) consistently located is good.

    Additionaly， ErikE 提到，可以有一些效率在表的末尾有可能经常包含null的可变长度字段（varchar，nvarchar）。除此之外，我不认为在现代关系数据库中以某种方式安排事物有任何性能优势。

    Additionaly, ErikE mentioned that there can be some efficiency to having, at the end of your table, the variable length fields (varchar, nvarchar) that might often contain nulls. Other than that, I don't think there are any performance advantages to arranging things a certain way in modern relational databases.

    命名

    通常，当您决定列顺序时，您需要决定列名称，所以我想解释一下。你当然可以用命名字段做出可怕的，代价高昂的错误;这比你的列排序更重要。排序可以很容易改变，但不好的名字会导致你的问题永远。这是一个巨大的痛苦，一年后更改表/列名称当有十几个引用他们。我刚刚添加了一个答案 此处 解决这个非常重要的话题。

    Often when you're deciding column order is the same time you're deciding on column names, so I'd like to address that a little. You can certainly make horribly, costly mistakes with the naming of your fields; this is much more important than your column ordering. Ordering can be changed easily, but poor names will cause you problems forever. It's a huge pain to change table/column names a year later when there's dozen's of references to them. I just added an answer here to address this very important topic.

    这篇关于对数据库表中的列排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！