Tag: database-design

SQL Server 2012 Many Parents Join Tables?

I have a single table (“#detail” in example below) that will have a child relationship to one of three tables (always one table and no others). For example, #detail rows ‘aaaa’ and ‘bbbb’ need a column value that relates to #metainfo1,row1 and row ‘cccc’ needs to be related to #metainfo2.row1 and ‘dddd’ needs to be […]

Database design for storing LCS information?

I have a table that contains 2 columns, one is a id, and other is column containing long strings eg. Id strings 1 AGTTAGGACCTTACTCTATATCTGTTCTGTTGGTATGGAG 2 GTACTTGTATTCTGATATCTAGGGTTTTCTAATTACTTCTG 3 GTATTCTCTTTCTAGCTGATCGTAATTAAATCTTATCTAA when the user is performing a search, I would find the longest common subsequence in the search string and all the data in the table. Eg. the […]

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 […]

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