Tag: database-design

select combined with an update, race condition

I would like to do something like this begin trans declare @maxpriceprice int; Select @maxpriceprice MAX(price) from products where typeId = 5 and later on Insert into products (price) values(@maxpriceprice + RAND() * 10) commit tran It is rather strange but it is strange application. between the select and insert i cannot afford to havve […]

Best way to store incomplete data in a relational model?

I am writing a web app that takes user input from a series of forms (think “wizard”) and stores it in a SQL database as the user progresses. Let’s say they are submitting an application form on behalf of a company that has an address. The application form will also have a list of supporting […]

What is a good way to organize and maintain predictive model input features in SQL Server?

Consider a toy forum database with tables like User, Post, Votes, etc. Assume that we have a machine-learning system for classifying users based on some features. The features were engineered by a team of modelers and may include values directly from the User column (say, user_age), as well as more complicated queries, which may require […]

Does the starting seed matter for sql server identity column

Are there any technical considerations when setting the seed value for an identity column in SQL server? This column will be the PK and marked as Identity auto increment. The business does not want to start IDs at 1 because they will be publicly visible in query strings and displayed on several pages. Using a […]

Scheme for Posts and Comments

I need to allow Comments to different entities such as Posts, Products, etc: One Post / Product / … can have many comments; One Comment can only be associated to a Post / Comment / … I though of different schemes and I came up with the following schemes and tables: 1. Each entity has […]

Comma separated values vs multirow approach for storing data in SQL Server based on performance and handling of data

Scenario: There are 2 tables. Table1 contains users and Table2 contains Hobbies User can have multiple hobbies(20-40). and number of users is over 100k. Approach 1. Create a UsersHobby Table with column 1 as UserID and column 2 as Hobbies and store hobbies as comma separated values. It reduces the number of rows. For example […]

Address table applied to Users and Stores

On a database I have the following tables: create table dbo.Stores ( Id int not null Name nvarchar (120) not null ) create table dbo.Users ( Id int not null Name nvarchar (120) not null ) Each User or Shop can have: 1 – One physical address; 2 – One website or social media addresses. […]

Pulling data from multiple databases on different db connections with a common target table

think of this scenario.. We have a web service that communicates with and pulls data from multiple, say 5, 6 or even 10K, databases. Each database has its very own design, tables and columns but they all implement a “Users” table. Our web service must look in each of those “Users” tables and access their […]

How does one create database tables (MSSQL) to handle notifications to users and system notifications to all users?

I would like to create a notification system for a web application I’m working on. I’m having trouble designing a database that is laid out in a “correct” format. This is what I would like to accomplish: Have different notification types, such as Office, User, Client, Vendors, and System. User, Client, and Vendors are different […]

How to perform INSERT/UPDATE to Linking (Join) table which has FKs to IDENTITY PKs

I am new to designing my own database. My project is orders/products/inventory type data, using SQL Server 2005. During my reading I came across several customer/order type of examples, and I decided to use linking-table (sometimes here called junction, or join table) for my Order_Details since I had many-to-many relationships between ORDERS and PRODUCTS. The […]

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