Tag: database-design

Preparing to move to a single database

We have an application that has 1000+ databases and 600+ sprocs. Each database represents a different client. Problem: We need to move this to a single database while creating as little effect on the ui as possible, meaning dont change all the sproc signatures at 1 time. The connection string currently sets the database attribute, […]

Is using multiple tables an advisable solution to dealing with user defined fields?

I am looking at a problem which would involve users uploading lists of records with various field structures into an application. The 2nd part of this would be to also allow the users to specify fields to capture information. This is a step beyond anything ive done up to this point where i would have […]

Best way to store and retrieve comment replies in sql server

I want to store comment replies in database table. I have a table to store comments: comment_id comment_par_id, comment_from comment_text comment date …. New comment has par_id=0 while the replies has par_id set to comment id to which it was replied. The nesting is just one level. Reply to a reply also has the same […]

When to use a covering index, a composite index, and unique columnar indexes

Let’s say I have the following table in SQL Server 2008: ProfileID int //identity; index: unique, primary key, clustered ClientID int RegionID int ProfileName nvarchar(50) Columns 2 and 3 link to their respective tables via foreign relationships. Let’s say my most common query is this: SELECT ProfileID, ProfileName FROM Profiles WHERE ClientID = ? AND […]

Need recommendation for a table structure

I have an entity which has 4 different types of property that could have only one value for each case which are boolean, decimal, string or text. I don’t want to define the table with 4 boolean, decimal, nvarchar and ntext columns. What would you recommend to cover this case? Update: I’m using MS SQL […]

GridView edit problem If primary key is editable (design problem)

I would like to ask about the design of table based on it’s editability in a Grid View. Let me explain. For example, I have a table named ProductCustomerRel. Method 1 CustomerCode varchar PK ProductCode varchar PK StoreCode varchar PK Quantity int Note text So the combination of the CustomerCode, StoreCode and ProductCode must be […]

Database with users design

I am in database design development phase. Application will work with large number of users (LARGE :)) I designed 80% of database but I have one Users table which is connected to everything else: Users {UserId, FirstName, LastName, Username, Password, PasswordQuestion, PasswordAnswer, Gender, RoleId, LastLoginDate etc etc} I saw asp.net membership database structure where Users […]

Cascade deletes on linked table joining 2 tables that each have a cascade delete to another table. How?

I’m pulling my hair out with this one. Let’s assume the following objects as tables in SQL: Companies Employees (refers to companies) Meetings (also refers to companies) And employees can be in meetings so we have a link table: EmployeeMeetings (i.e. FK to both employees and meetings) Furthermore I can’t touch the Companies table (e.g. […]

Facebook like Feed

I want to agregate updates of my tables into a feed. Let´s take sample tables News, User->[Info-Update, Friendships, Images, Videos], SomeOtherTable On every update or insert an event is fired. Now there will be, for this different types, some handlers that should generate the feed. Problem 1: FeedTable, its columns, localized titles and text. My […]

Mapping fragments exception with Table-per-Hierarchy (Entity Framework 4)

If i have SQL Server tables like this: Location ———- LocationId int PK Field1 int Field2 int etc Score ———————————— LocationId int PK, FK IsSingleLevel bit PK (discriminator) Field1 int Field2 int etc Technically this is mapped as a Location 1..0..* Score, but with the PK of LocationId/IsSingleLevel, it’s a Location 1..0..2. When i drag […]

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