Contents
When do you need to have multiple tables in a database?
The database layout should not change when you add more data to it, so the user data should definitely be in one table. Having multiple tables means that you have to create queries dynamically.
Which is the most efficient way to store data in dedicated SQL pool?
Clustered columnstore indexes are one of the most efficient ways you can store your data in dedicated SQL pool. By default, tables in dedicated SQL pool are created as Clustered ColumnStore. To get the best performance for queries on columnstore tables, having good segment quality is important.
How does having multiple tables affect SQL performance?
Having multiple tables means that you have to create queries dynamically. The cached query plan for one table won’t be used for any other of the tables. Having a lot of data in one table doesn’t affect performance much, but having a lot of tables does.
What do you need to know about loading with CREATE TABLE?
Loading with the CREATE TABLE AS SELECT (Transact-SQL) requires CONTROL permissions of the database. The CONTROL permission gives control access to all schemas. You might not want all loading users to have control access on all schemas. To limit permissions, use the DENY CONTROL statement.
Do you have separate tables for customer and admin?
Later (for fast search) you may decide to add separate tables Admin, Customers, Users with FK to the Person table. In general, having separate tables for customers and admins should speed-up any admin/customer related query. If a user can only be one type, you’d be better off with one table and a bit field for IsAdministrator, etc.
How to keep user and user profile in different tables?
Identity data: user name, password hash, email address, last login time, etc. User profile data, which includes users preferences, latest activity, status updates etc. Note that there are some attributes about the user that can fall in either category (e.g. user’s date of birth).
Which is better single user or single post table?
Your first proposal of having a single user and a single post table is the standard approach to take. At the moment posts may be the only user-specific feature on your site, but imagine that it might need to grow in the future to support users having messages, preferences, etc.