Tag: database-design

How to implement referential integrity here?

I got the following structure – which I admit is not ideal, but so much is built on that, tat I want to minimize changes. I am not sure about how to properly implement referential integrity between Documents and Delivery Adresses. Can it be done here without using triggers ? The problem is that the […]

which is the good way for minimum performance issue in sql

From client machine i have to get 600*10 records for every 30 secs and it should be stored in DB.I have some 600 ids and each ID have 10 parameters so total is 6000.So i am thinking for a table structure which will have minimum performance issue. 1st Idea Column names will be ids ranging […]

Creating a table specifically for tracking change information to remove duplicated columns from tables

When creating tables, I have generally created them with a couple extra columns that track change times and the corresponding user: CREATE TABLE dbo.Object ( ObjectId int NOT NULL IDENTITY (1, 1), ObjectName varchar(50) NULL , CreateTime datetime NOT NULL, CreateUserId int NOT NULL, ModifyTime datetime NULL , ModifyUserId int NULL ) ON [PRIMARY] GO […]

About database keys

I’m trying to figure out a way to design my SQL Azure database. There is a lot of information to be found about what your primary key should be (int versus guid) and advantages/disadvantages of both approaches, so I’m aware of the war going on there 🙂 But, I was thinking to apply best of […]

Improve performance of inserting data for one to many relationship in EF

I am getting really poor performance in EF because of a particular design structure for my database. Here are the relevant relationships: I have the following data model: public class Sensor { [Key] public int Id { get; set; } [Required, MaxLength(64)] public string Name { get; set; } [Required, ForeignKey(“Type”)] public int SensorTypeId { […]

Number of Characters in SQL Server varchar(max)?

Microsoft’s documentation on the varchar(max) data type: “Variable-length, non-Unicode string data. . . max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes” http://technet.microsoft.com/en-us/library/ms176089.aspx I thought 2^31 bytes = 2 GB, not that 2^31-1 bytes = 2 GB. Am […]

Design for amplifier make/model settings database?

As a personal project, I essentially want to create a web application that allows users to submit amplifier settings for specific tones, which will render images and create an archive of guitar tones for specific amps. I know that I first should design a database to support this web application. After reading about relational databases […]

Metadata database design

I am trying to store meta data about a document into a SQL Server. The document are stored into a document archive, and returns back an identifier so I can get back that document by asking the archive to get the document by identifier. Our user would like to be able to search for this […]

Database design for Order Management

I would like to know the best practice for designing the database for an Order Management System. I have an order table and orderitems table. My current design is as follows. Order table orderid int primary key totalprice int orderstatus int Order Items table orderitemid int primary key orderid int foreign key productid int quantity […]

How to implement a 1:N relationship in a relational database where one of the Ns is special?

I have to implement keywords in an MVC application used for storing animal lines (we develop database-first). Each keyword has a M:N relationship to the animal lines. The problem is that each keyword has a bunch of synonyms and alternative spellings. So, if an animal line is especially interesting to immunologists, it may get the […]

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