Tag: database-design

Access Control List per row in table

I have a tasks table on the cloud and I want users to be able share their tasks with others. I need to be able to tell which users can read each given row. Do I need to create a 1 to many table to specify which users have access to a given row? Or […]

Best table structure for tracking state changes

I’m currently trying to model an aspect of a system whereby components that are stored can change state, eg OK, FAILED, REPAIRED etc. On the web side I will need to show the current state, but also the history of previous (if any) states. I’m torn between these two designs, could anyone shed any light […]

Proper way to make this data dictionary

I have question about what is proper way to create data dictionary. I have never did it before but now I must. At this moment I have to data tables: public class Device { public int DeviceId { get;set; } public string DeviceSerialNumber { get; set; } [Required] [StringLength(14)] public string DeviceUser { get; set; […]

Sharing a table row between users in SqlServer Azure

Context: A mobile note taking application that is connected to windows azure mobile services. (Sql Server Azure) Currently I have 2 tables: Users & Notes. A user downloads their respective notes by querying the Notes table and asking for all notes that have the userID match their own. Example: SELECT * FROM Notes WHERE userID […]

SQL Server/Table Design, table for data snapshots where hundreds of columns possible

We have a business process that requires taking a “snapshot” of portions of a client’s data at a point in time, and being able to regurgitate it later. The data set has some oddities though that make the problem interesting: The data is pulled from several databases, some of which are not ours. The list […]

Primary Key – Foreign Key relationship between two different database

This question already has an answer here: Foreign keys in alternate schemas with Oracle? 2 answers

Storing multiple choice answers of a survey in database

I try to create a survey app using asp.net (c#) based on ms sql server. The user can see multiple choice, single choice, and textbox answer choices to input. I am able to store textbox and single choice answers in my database but I am stuck on storing multiple answers. My design is the following: […]

Enforcing referential integrity from one table to any table

I have a variety of tables that represent business objects, e.g. Products, People, Locations and I’m wanting to add a Tags table (as in taxonomy). CREATE TABLE Tags ( tagId bigint IDENTITY, name nvarchar(50) ) How can I allow multiple tags to be applied to multiple types of entities in a way that allows the […]

Database designing

Table Column Name Datatype Allow Null ——————————————————— LOGIN Table LOGIN NAME Varchar No PASSWORD Varchar No View Table id int No About Varchar No From Varchar No Date Varchar No Rating int yes message Varchar(max) No Delete status Boolean yes Public/private Boolean No Anonymous Boolean yes Employee Boolean No Manager Boolean No sent tag status […]

No nvarchar: “Operand data type nvarchar is invalid for sum operator”

I have a view that returns ProductName, Qty, and other columns Defined as: SELECT ProductName, Qty, /* etc. */ FROM SomeConcreteTable SomeConcreteTable defines ProductName as nvarchar(40) and Qty as float. SELECT ProductName, SUM(Qty) FROM MyView GROUP BY ProductName This causes an error Operand data type nvarchar is invalid for sum operator When the view returns […]

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