Tag: database-design

Centralized database for common lookup tables

We currently have separate databases per application but I am finding more common tables (ex. countries, employee type) for each database that are replicated. I am considering created a separte lookup database to store all these “lookup” tables. The application databases would then reference the same key in the lookup table which would not only […]

How can SQL Server be used to efficiently store website page views?

I am currently recording basic page views on a website using a single column, incrementing by one on each page load. This gives a limited, very general view of the most visited pages, without taking into account pages being repeatedly loaded by a visitor, or being visited by search bots, etc. Without worrying about these, […]

Database Performance Improvement Structural Design

I has a structural performance question. I am preparing a book selling web site. I has books, authors, categories ect. I want to give users some discounts. But these discount can be about book, category, author and even can be about user. I am storing discounts at ‘discounts’ table and storing types. When people list […]

Table relationship issue in database design

I have Payrollsalary tables PayrollSalaryMaster (PayrollID,PayMonth,EmployeeID) PayrollSalaryDetail (DetailID,PayrollID,ComponentID,Amount) I have Amendment table that record the amendments in salary if there is deviation form approved salary PayAmmendments (AmendmentID,Detailid,Amount) Now I have new requirement that Employee Salary’s or any of its component can be Hold (i.e.) It will not be paid with the current salary. I modified […]

SqlServer large nvarchar clustered index

I have a table for caching geocoding results where I had planned to use the search string as the primary key/clustered index, since I wanted this to be both unique and indexed for quick lookup. This would be pretty big, probably nvarchar(300). I’ve seen that large cluster keys are generally advised against due to size […]

How to design database tables for dynamic forms (User defined fields)

Single value such as Text, Number, Decimal, is not hard to do, but what about radio options? checkboxes? Single value UDF FORM – Id – AttributeName – DataTypeId FORMVALUE – Id – FormId – UserId (user that entered the value) – Value DATATYPE – Id – DataTypeName

Is it better to use separate tables to store rows that apply to “a more general classification”, or are nullable columns suitable?

I am building a database for a company that sells warranty packages. There are a number of attributes associated to each package that factor into the final price. For example, the age of the vehicle. Prices for the age of the vehicle need to be set at the package level, but also can be set […]

Propagate Schema and Code Changes Across Multiple Databases

I am working on a Sql Server 2012 Express and have multiple duplicate databases that serve an application. One of the project requirements is to have data separation between clients due to various business and government laws. Due to upgrades and bug fixes we have to make table schema changes or updates to the stored […]

Does word alignment matter for keys?

I am not able to find any clear documentation on whether we need to consider word alignment for a key (whether it’s a primary key, clustering key or plain old index). With row compression, it can be set up so that we use up storage space efficiently but I’m more interested in the CPU performance […]

scalability mssql building 1 big table as a user history

I am developing a forum. I want to save each time a user visits a topic to be able to show new replies since last viewed topic on a per topic basis. The way I have set this up currently is to save userid and topicid to a table “visitstopic”. Then every time i display […]

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