Best practice to design big entities

What would be the best practice to design a big entity like an Employee table that contains over 100 attributes?

Should I keep them as a single table with 100 columns or should I split them and to 1..1 relations and then compose the Employee object in my code?

  • Globalize Database ASP.NET MVC
  • SQL 2012 bcp call returns SQLState = 28000. NativeError = 18456 Login failed for user
  • SQL Server 2005 How Create a Unique Constraint?
  • How to search more character string into smaller string through sql
  • SQL Server 2005 ROW_NUMBER() without ORDER BY
  • echo image according to a condition
  • Any opinions? Pros and cons of each method?

  • Getting the primary key of an newly inserted row in SQL Server 2008
  • Importing new database table
  • SQL 2008 setting compatibility level
  • Local database without sql server
  • Find out when a database backup was made
  • is it possible to save metadata about mdf file inside the file?
  • 2 Solutions collect form web for “Best practice to design big entities”

    The answer here lies not in the Employees table, but in the wider database design. If all attributes are definitely 1-1 then I would definitely have one entity. SQL Server has optimizations you can employ when you get to the physical design, such as SPARSE columns for columns that have many NULL values.

    I assume you are going through the process of normalization and Entity Relationship Diagrams at the moment. If you are, then I would suggest looking at a SuperType/SubType approach, for which Employees is normally a great candidate.

    In this approach (as example) you may have a “Contacts” table, which would contain First Name, Last Name, Phone Number, etc. This would then link to your Employees table, your Customers table, your Vendors table, etc. Your employees table would then just contain the attributes that are unique to Employees, such as Staff Number, Start Date, etc.

    There are several benefits to this.

    • Firstly, if an employee is also a customer, for example, then you reduce data redundancy.
    • You are likely to achieve a better compression ratio. This is because when you have fewer columns, there will be more rows stored on a page, meaning the name “Smith” will appear on the same page more often.
    • From a Master Data perspective, if a company standard for data type of an email column is introduced, then you can change it in one place, not three places. (Forgive the slightly contrived example here but hopefully it illustrates the point).
    • As both the Super Table and the Sub Table have fewer columns, each can be read faster in isolation. If joined in the same query, and placed on separate Discs then the 2 tables can be read in parallel.

    Try and read up on Star-Schema and Snowflake-Schema. These are terms used when it comes to designing data warehousing schemas, and the pros and cons probably are very similar to what you are facing here.

    http://en.wikipedia.org/wiki/Star_schema

    http://en.wikipedia.org/wiki/Snowflake_schema

    http://www.diffen.com/difference/Snowflake_Schema_vs_Star_Schema

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.