-5
votes
0answers
35 views

Roomwise track of beds sql server table design [on hold]

Well I am bit confused. I am a new student of SQL database. Today, I conceived of a system to track accommodation status in my hostel. I do not know how to plan the database field and constraints - ...
10
votes
5answers
874 views

Why does a UNIQUE constraint allow only one NULL?

Technically, NULL = NULL is False, by that logic no NULL is equal to any NULL and all NULLs are distinct. Shouldnt this imply that all NULLs are unique and a unique index should allow any number of ...
-3
votes
0answers
30 views

Design a queue and table in SQL Server as per below data [on hold]

Design a queue that holds messages (varchar(100)) for any number of users. Each message also has a priority of 1, 2, or 3, with one being highest priority. When a user gets a message (DEQUEUE), they ...
0
votes
2answers
31 views

Design a Lab Report tables [closed]

I need to design tables for a laboratory test. I will have Lab test name, and its component to be saved. The component can be changed with time. I need to design a UI for update. Also each ...
1
vote
2answers
51 views

Unique column values between tables

I'm keeping track of teams and their members. Each team has one leader, one contact person, and any number of players, and each of them has a unique email address. The problem is that one physical ...
1
vote
1answer
95 views

Database design for an online babyShop ,Toy shop

Hi i'm designing a database for a toy shop this shop offer many products with different categories. boystoys,girlstoys,puzzles,beds,stroller, etc i designed my database as it is consists of 5 tables ...
0
votes
2answers
53 views

Best Way to Migrate 100 tables To New Database

I need to migrate 100 out of 150 tables (data and structure) and everything else (sprocs, views etc.) to a new empty SQL Server database. I have been using the Import/Export wizard and it has been ...
1
vote
0answers
56 views

DB Design: Looking for Performance improvement with a Column used in all SQL Queries

I recently got added to a new ASP .NET Project(A web application) .There were recent performance issues with the application, and I am in a team with their current task to Optimize some slow running ...
1
vote
1answer
108 views

Execution plan index suggestion - difference between similar queries

I am trying to analyze queries on this table: CREATE TABLE [dbo].[Values]( [tid] [smallint] NOT NULL, [t] [datetime2](3) NOT NULL, [v] [real] NOT NULL, CONSTRAINT [PK_Values_Unique] ...
1
vote
2answers
56 views

Database Design for Preserving Values and Additional columns

My colleague and I are having some trouble coming up with a database design, and I am doing my best to stay away from EAV for now. We have an entity called Preparation that stores configuration data ...
1
vote
3answers
65 views

Removing chat history for a particular user from database

I'm currently developing a web chat application using SignleR and SQL server this is my database schema so far I've added an option where the user can Clear Chat history for a particular ...
1
vote
0answers
63 views

How to handle many columns / variable schema?

I apologize in advance in case this question has been asked already. I'm working on revamping a reporting application used within my company. The requirements are: Support addition of new fields ...
5
votes
1answer
374 views

Bad Design or Complicated Query?

Problem: For fun I am building a Database for lottery results to get some statistics off of. As of now I am only interested in a Pick 3 style lotto and might introduce more later. How would you ...
2
votes
2answers
417 views

Is there any way to do a SELECT query without any optimization in SQL Server?

I am trying to test the speed of SQL server queries. However, once I do the query once, it becomes a lot faster. The database is live so I can not make any changes to it, only to the SELECT query ...
2
votes
1answer
154 views

Database design for a table with both bulk insert and single row insert

I have to design a table where records will be inserted in two ways. Bulk insert from a batch process from a delimited text file between 2 - 3 million rows in a short burst. Later the same records ...
0
votes
1answer
74 views

Text Storage and Database Design Optimization in a SQL Server Database

Given a database with a number of columns (c), where some of these columns contain text that is one of multiple values due to a set CONSTRAINT on the columns, what is the most efficient way to ...
0
votes
0answers
42 views

Struggling with normalisation (database design critique)

I'm relatively new to database design and have been reading up a lot on best practice, and I'm starting to design a schema for a new application that will be developed and I am struggling with a few ...
1
vote
2answers
42 views

Store n:m data with most n:1 tuples

I have these tables: create table data ( id int identity not null, ref int not null ); create table reference ( refid int not null ); with ~2 million rows in data and ~200k rows in ...
0
votes
1answer
99 views

Need Advice on Normalizing Database

I have a group of database tables that lack normalization. They all have a similar primary key called 'doc_index', which is either a PK by itself or part a composite PK. As you can see below, there ...
3
votes
5answers
360 views

Using Timestamp plus Integer as Primary Key

In Sql Server, what would the drawbacks and disadvantages be of using a primary key that is: Two Character Table Identifier + YEAR + MONTH + DAY + HOUR + MINUTE + MILLISECOND + random integer between ...
1
vote
2answers
66 views

How to find usage of each object in other objects from a database?

I am with the task of taking an old SQL Server database and identify all of it's objects and their relationships, because the one who made it did not bother to use any foreign key, he just made all ...
0
votes
1answer
122 views

Database revision needed for sports events database

This is not a code review but more of a database schema review. I have a sports events website that I am doing in my spare time. I need assistance with the database design. I am about 80% completed. ...
0
votes
0answers
56 views

Database design for large incoming data

I've been tasked to design a database to handle a large volume of incoming traffic from sensor readings, possibly 1,000s an hour when it's at full strength. Each reading will be relatively short - ...
1
vote
1answer
113 views

Enforce referential integrity between two groups of data

I have two sets of records: A collection of rules. A collection of actions to perform based on those rules. What I'm trying to model is something like this: Multiple rules can be grouped ...
0
votes
0answers
85 views

Uploaded files corrupted except txt/csv

I have an ASP.NET MVC 4 Application with standard CRUD functionality plus a file Upload capability. Currently I am struggling with the files being corrupted after the download. I created a ...
0
votes
4answers
285 views

Database/Storage engine suggestion for a project involving heavy inserts?

I am looking into a project which involves large number of inserts daily.I will have a list of user's (for example a user set of 500k ) , for which I need to monitor daily certain activities ...
0
votes
1answer
84 views

Problem creating database using default mdf and ldf path

I'm trying to create a database using the default paths on several SQL Servers at once. To achieve this I'm trying to use a script that should create the database on each server, however I've run into ...
1
vote
2answers
80 views

Best way to store immutable/read-only copies of data for logging?

I'm storing data about tasks like so TASK (id, name, employee_id, machine_id, ...) For logging purposes I would like to store any information about that TASK once it was made. Since the reference ...
1
vote
1answer
194 views

Horizontal schema vs Vertical schema

I am but a modest SQL DBA. I would like to request your advice on the design of the central part of our new proposed database. Brent Ozar had something to say about this: Building A SQL table ...
1
vote
1answer
62 views

Should I use a surrogate key in a mapping table?

I am deploying a mapping table which connects Items to ItemGroups Though an Item will only ever be in a single ItemGroup please accept a priori it is not appropriate in my use case to append a column ...
1
vote
0answers
80 views

“Replicate” Table structures with sp_addscriptexec

In our customers environment is a massive central server (running SQL Server Enterprise 2008R2) and some distributed PCs (running SQL Server Standard 2008R2). The central server has a replication for ...
2
votes
2answers
214 views

Design of an application log database

We are generating logs like following example(this is a table no actual pipes): 2014-06-10 09:00:03.457 | Channel1 | Operation3 | Function15 | 15ms 2014-06-10 09:00:08.245 | Channel2 | Operation5 | ...
2
votes
0answers
113 views

Database Design - Multiple Dbs or Single - Security & Data

I've hit a brick wall with this and just cannot make a decision so want to throw this out there and see if there are any issues I have'n't considered. Basic Topography I have a system that currently ...
1
vote
1answer
217 views

Slow delete caused by many foreign keys

My DB has about 90 tables. Most of the tables have an UpdatedBy and AddedBy column that have foreign keys pointing back to the user table. With even moderate amounts of data spread throughout the ...
1
vote
1answer
60 views

Delete and Update of Foreign keys [closed]

I'm working on a school project. And I'm required to implement it using Java-EE and SQL Server. I want the design to withstand any kind of faulty user requests. My questions are how should I design ...
3
votes
1answer
106 views

Is this a step in the right direction for normalization? SQL 'inheritance' and Many-to-Many relationships

Context: So, this is kind of a compound question, but they go together, and definitely wouldn't make sense split up into separate questions. I've been working on a new database for what will end up ...
0
votes
0answers
79 views

Is there better way to design SQL tables

I need to design a quick application for small warehouse. I just want to check if there is a better design approach. I am using polymorphic associations for comments tables. Business Rules If ...
4
votes
1answer
171 views

What is the correct way to ensure unique entries in a temporal database design?

I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read this answer, but I'm afraid I ...
0
votes
0answers
29 views

Enterprise system database strategy [duplicate]

Here is my current database design: Separated databases based on area of domain (Sales, HR, Accounting, Core such as login and menus, etc). Each database has a single service login, each with ...
6
votes
1answer
606 views

Database design for handling 1 billion rows and counting

We receive real-time GPS data at a rate of around 5000 pr. minute (from 4 TCP servers). Each server uses a single connection to insert the data, and buffers data in between inserts. Every 15 minutes ...
0
votes
1answer
64 views

Which one is correct and optimized in sql?

Imagine that I have three entities (EntityA, EntityB, EntityC) that can have some images. So there are two ways: Make an image table for each entity. It means that EntityA has a image table named ...
0
votes
1answer
89 views

Database analysis

I have database which that store the sold products in Orders Table and another table called Transaction to set the time and the total price for the individual order ,my question is: How to combined ...
2
votes
1answer
132 views

Clustered composite key high fragmentation and fill factor

I've recently made a table to hold the language preferences of my users as follow: CREATE TABLE [dbo].[systemUserLangPreference]( [systemUserID] [int] NOT NULL, [langID] [int] NOT NULL, ...
1
vote
1answer
124 views

data warehouse design help

I'm struggling with the design phase for a data warehouse. It's not the traditional sales example you find on MSDN or all books etc. I've been tasked with creating an education data warehouse, that ...
2
votes
1answer
72 views

Confused on how a value gets added with the DEFAULT constraint

A recent definition I've read about Default Constraints: DEFAULT is specified for a column to assign a column a specific value if no value is specified when a row is added. I believe I've seen code ...
0
votes
1answer
129 views

understanding (and modelling) grocery shopping lists

I am having a hard time understanding how multiple product orders are mapped out. What is the best way of modelling a shopping list for multiple customers? I am developing an C# application that ...
2
votes
1answer
38 views

How can I model and enforce constraints on a categorical item contained in a collection?

Scenario: There is a collection of items where each item belongs to 1 category. There is a collection of buckets which may contain at most 1 item from each category. Oh, and an item may belong to at ...
0
votes
1answer
77 views

Beginner table design

I have been coding in C# for a couple of years where I usually try to follow the 'single responsibility principle' which states that each class should ideally be responsible for only 1 goal. Now I ...
3
votes
1answer
92 views

Help with “Flexible” vs Fixed Tables

We are designing a database in SQL Server to handle sales commissions. A diagram of the current schema looks like this: There are only a couple of us on the project, and our boss dropped this ...
3
votes
2answers
45 views

Calulating savings between two schemes

I've normalized a single table to reduce the number of nullable columns from 3 down to 0. The idea was to save a little drive space but I'm seeing a 45% decrease in space needed to store the same ...