Tag: database-design

Versoning in relational database

I have a problem to introduce a good versioning in my database design. Let’s make a easy example. A little rental service. You have a table Person (P_ID, Name), table Computer (C_ID, Type) and a table Rent (R_ID, P_ID, C_ID, FromData, ToData). I want to be able to change say the user name, create a […]

SQL Server loosely coupled tables – performance considerations?

I am building a database schema for a new application from scratch, and my two goals are loose coupling (scalability) and performance (but performance is the MOST important). I am not sure if it would be a good idea to include foreign key columns in central tables. My question would probably be best understood using […]

database design asking for advice

I need to store entries of the schema like (ID:int, description:varchar, updatetime:DateTime). ID is unique primary key. The usage scenario is, I will frequently insert new entries, frequently query entries by ID and less frequently remove expired entries (by updatetime field, using another SQL Job run daily to avoid database ever increasing). Each entry is […]

what is the best db strategy for column indexing?

Two examples are: Columns that will show up in a queries where clause (where Name = “xtz”) Columns that you are going to order (sort) on in queries Is this correct and are there other important use cases? Can SQL Server recommend fields to index based on usage patterns ?

A better database design on SQL Server?

In SQL Server, I need to design a User table. It should have a UserID (int), a RoleID (int) and a UserName (nvarchar(255)). A user can be assigned with a name but possibly multiple roles so a UserId may correspond to multiple RoleIDs. This makes it difficult to assign primary key to UserID as UserID […]

Accounting database design question “accounts” and “transactions”

Option 1) user can have multiple “accounts” (eg payment, deposits, withdraws etc) Option 2) user can only 1 single account, and transaction has types (payment, deposit, withdraw) Both option will work just fine! They both can produce the same result! However option 1 uses more resources, but it’s more flexible, option 1 is not flexible […]

Should I better use varchar(6) or char(6) for an SQL Server column which values can be either 6 or 5 symbol long?

I use ISO 3166-2 codes to designate states (of many countries, not just the USA) in my database. Code is the primary key of my States table and and it is referenced by StateCode foreign key column of my Cities table. As far as I can see, there are 5 (like US-CA) and 6 (like […]

No direct access to data row in clustered table – why?

[11] tells: “In a nonclustered index, the leaf level does not contain all the data. In addition to the key values, each index row in the leaf level (the lowest level of the tree) contains a bookmark that tells SQL Server where to find the data row corresponding to the key in the index. A […]

.NET – Unit Testing Your Code and State in Your Database

Assumptions: We use .NET coding against SQL Server 2005 I was just wondering how most people incorporate state into the Unit Tests that affect their database? I know when and where to use mocking, but when you want to move past that and actually do some database tests… what strategies do you use to setup […]

Can a Primary Key be a Non Clusterd Index on a Clustered Table?

I am designing a Members Table to store the users of a website. It will be used every time a user logs on to the website and occasionally accessed to update user details. The users will log on with an email address and password and every account will have a unique email address. Therefore it […]

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