Access databases often have a flat structure, that is, there are few tables and many fields in each table. Access works efficiently with tables containing many fields and fewer joins between tables, whereas SQL Server works more efficiently with narrower tables containing fewer fields and more relationships between tables. Combined with properly used indexes, you will see a vast improvement in database reads and updates, because SQL Server is efficient at selecting rapid, efficient joins between tables.
- A table should store only data for a single type of entity.
Attempting to store too much information in a table can prevent the efficient and reliable management of the data in the table.
- A table should avoid nullable columns.
Tables can have columns defined to allow null values. A null value indicates that there is no value. Although it can be useful to allow null values in isolated cases, it is best to use them sparingly because they require special handling that increases the complexity of data operations. If you have a table with several nullable columns and several of the rows have null values in the columns, you should consider placing these columns in another table linked to the primary table. Storing the data in two separate tables allows the primary table to be simple in design but able to accommodate the occasional need for storing this information.