Tagged Questions
0
votes
2answers
72 views
Database Design Confusion
I am making a Phonegap based ERP solution for school in which every school can configure their own school individually and can manage the whole school from that single application, in which a main ...
2
votes
1answer
47 views
How to enforce a nullable foreign key?
I have a relation between two tables. The foreign key table can have a row related in the primary table. There's a way to enforce the value of the FK column to be NULL or one of the values of the PK ...
1
vote
2answers
99 views
Unique Constraint with multiple null columns
I have two tables
PC(Id,EmpName Not NULL, PCName NULL, HostName NULL,.... PhysicalLocation NULL, PCType Not NULL)
PCNetwork(Id, EmpName Not NULL, PCName NULL, HostName ...
1
vote
3answers
84 views
How to handle update or insert situations
From time to time my stored procedures looks like
create procedure handle_data
@fk int
,@value varchar(10)
as
begin
if exists (select * from my_table ...
2
votes
3answers
310 views
How to Handle TimeZone Properly in SQL SERVER?
I have some issue which needed to be fixed quickly. My local development server is in middle east. But my production server is in UK. Now, I need to show the date to user to thier timezone. For ...
2
votes
1answer
129 views
Bitmask Flags with Lookup Tables Clarification
I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries ...
1
vote
1answer
73 views
table design to track significant events associated with an item
An Orders table I'm dealing with at present in design phase has about 10 or more columns.
Each OrderID in the Orders table will have certain significant events or milestones. The number of Orders ...
3
votes
1answer
179 views
Schema design: Use of association (aka: bridge/junction) table vs foreign key constraint with composite index containing a non-key field
This is an inventory database for IT assets. The models used are trimmed in order to focus on the problem at hand. Using SQL Server 2008. Thanks for taking the time to read and for any input you can ...
2
votes
1answer
91 views
How to avoid Repetition in the Database Design?
I have currently the following database structure,
Retailers
-----------------------------------------------------------------
ID Name XXX XXXX XXXX XXXX XXXX XXXX
...
1
vote
1answer
176 views
How to handle huge table
We have a problem with a few tables that are very very big slowing down the system. so my question is if that size is normal for the amount of rows in the table, and also what could be done to make it ...
0
votes
2answers
296 views
SQL Server Database Diagram in SQL Management Studio creating multiple schemas
I'm using a multiple schema approach (Multi-Tenant arhitecture) in the same DB and it's working great. I can use EF bounded contexts and split domains appropriately.
According to MSDN, ...
4
votes
1answer
103 views
How should I model a worflow of status/activities ? (rules like “if is 3 go to 4, if is 4 go to 5” etc)
I'm trying to model a workflow (status and activities) to create "rules".
Let me give you an example.
The main "object" let's say are "vehicle requirements", I will have this table:
idRequirement, ...
3
votes
1answer
104 views
Help to create indexes to improve query performance and reduce report generation time
The database in SQL Server 2008 has these tables below - the main table being the Master:
Master
A_ID (PK)
SYSSerialNumber
Bootlog
SequenceNumber
FileName
Cfg
Unique(serialnumber,sequencenumber)
...
2
votes
1answer
207 views
Database design for Magazine website
I need to design a CMS based website using asp.net web form (c#) and MS SQL Server as backend database for a magazine website which will have monthly issue.
I am finding it hard to design a database ...
2
votes
2answers
512 views
Separate archive tables or soft delete for inventory database
I'm building an inventory database that tracks computer equipment and other hardware devices. At some point in any device's life it is retired and gets archived. After it becomes archived it needs to ...
2
votes
2answers
220 views
Closed loops in database model, does it affect the performance?
I have heard that having loops in the database relational model should be avoided. From what I can think of, it is not a good practice, because there will be more than one different pathes to get the ...
2
votes
2answers
538 views
How would you design a table for a booking system?
I have a design database for a booking system. The booking system requirement are as follows:
The booking system allows user to book a time slot (1 hour) between 8 am to 1 pm for Monday to Friday.
...
0
votes
2answers
471 views
How do I normalize a database with lots of many-to-many relationships?
I am building a database that is based around a business project, in this case called a "Program." Each Program will have multiple Reports, Clients, and Partners.
My first thought was to place each ...
3
votes
2answers
275 views
Optimizing real-time Database table in Microsoft SQL server 2008
We are using a web interface to monitor our jobs state and its started and completed timestamps. We have >200,000 job instances running on each day for different job run-users. We maintain the state ...
3
votes
3answers
2k views
Add a new Column and define its position in a table
I have Table A with 5 columns:
TableA
--
Name
Tel
Email
Address
I want to add a new column (mobile) in between Tel & Email:
TableA
--
Name
Tel
Mobile
Email
Address
If I use
ALTER TABLE ...
4
votes
1answer
228 views
Database structure, relational or data warehouse?
Good morning,
Im having some issues with how my database should be laid out and would appreciate some guidance. I have several tables (rainfall data), each containing the following columns:
Date ...
1
vote
1answer
463 views
Replacing a table with new version
I am a programmer changing the way a process works, and will be using a different table to do this. The process is just sending emails from a web application. Currently the database scripts run during ...
15
votes
4answers
818 views
Does the order of columns in a table's definition matter?
When defining a table, it's helpful to order the columns in logical groups and the groups themselves by purpose. The logical ordering of columns in a table conveys meaning to the developer and is an ...
4
votes
2answers
258 views
Using surrogate key after merging the person's data?
This question is about business intelligence with sql server 2008 R2.
If you have a person named Jim Brown with his birthdate and social security number (SSN) being located in three different source ...
1
vote
1answer
87 views
Splitting a table based on business logic
This is mostly a design question and I'm looking for any feedback about the merits or flaws of this approach.
Typically, I break database design into two categories, Business Logic and Recorded Data. ...
1
vote
1answer
351 views
how to set/change the font style in database?
This question may be duplicate with another but my question is this.
I want to set or change the font style in header of table (like hindi font or any other) of database. I am using sql server 2008 ...
6
votes
1answer
491 views
Are there any performance benefits to using a hash table with no clustered index?
I have this table:
CREATE TABLE [dbo].[relatea] (
[mid] [varchar](16) NOT NULL,
[sid] [varchar](16) NOT NULL
)
It stores hash matches. Is there any benefit to having this as a heap? It has ...
2
votes
4answers
734 views
Best pratice to store dates group by months-year key par value
I am making one service which needs to hold data grouped and calculated by year-month combination. I know how to calculate data and put it on new table. But I am confused what kind of data type I ...
4
votes
2answers
82 views
Design for storing data only applicable to a small subset of records
So my boss and I are in disagreement how to best design our database for storing miscellaneous data for certain clients.
Basically, we have a bunch of tables in some format similar to this (in ...
6
votes
4answers
4k views
SQL Server database on an SSD - any advantage to a separate file for every table?
I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key ...
6
votes
3answers
228 views
Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server
On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: ...
6
votes
2answers
377 views
Using 100's of schemas in a database SQL2008R2?
We are in SSAS type of environment with 100's of clients.
Currently each client gets a database and we have 200 per instance on SQL Server 2008R2. This presents limitations with snapmanager being ...
9
votes
2answers
223 views
How many Sproc Parameters is too many?
I've just started writing a Stored Procedure in SQL Server 2008 and have 30+ parameters. I've never written one with more than ~10 parameters, and that got me thinking... At what point are there too ...
0
votes
1answer
131 views
Implementing Pub/Sub initiated from the database
I have a number of state-full Windows Services that currently poll the database looking for new or changed records in specific Tables and/or Views. For obvious reasons this limits scalability and is ...
3
votes
1answer
1k views
Storing XML in varchar(MAX) or use SQL-XML
I am defining a schema for a new set of resources using SQL Server 2008... In this case, each record (e.g. row) will need to store XML fragments. From time-to-time; although not frequently; I'll need ...
8
votes
2answers
264 views
Decision criteria on when to use a non-dbo schema vs a new Database
I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out ...
0
votes
1answer
407 views
Database design : multiple category of users login into same system
In our new system we have three type of people
Business owner
Assistant of business owner
Staff (a skilled employee)
I am having following entity
[Business] - include detail about business ...
2
votes
2answers
468 views
How to select a subset of data from a large ER model?
I have a quite large ER model in my database and lots of data. Now I would like to take a subset of this data into a different database for testing purposes. The problem is how to get a subset from ...
1
vote
2answers
1k views
how to Export a document contain tables name, columns names and column description from SQL Server 2008?
I created a db in sql server 2008 and I wrote for some columns description to describe them, What I want now is to find a way to export these tables design including the description for each column ...
4
votes
1answer
95 views
Possible pitfalls of condensing 2 SQL Server instances into 1
I currently have 2 SQL Server 2000 database instances (each running on a different physical server), each containing around 12 databases (not all are active). We are planning an upgrade to SQL Server ...
1
vote
2answers
254 views
Best type to use in a composite key when one of the values varies in type
I am building a data store for the content of multiple blog sites which have been scraped. Each of these sites is going to have an entry in a Blog table
BlogId Url ...
1
vote
1answer
283 views
Does composite cluster index affect performance of seek/scan non-cluster index which is part of the composite?
I have bank transaction table with columns
Division
Brcode
TrDate
ProjectCode
Accountnumber
EffectiveDate
DrCr
Amount
EndBalance
In the other table the Brcode itself is unique and is assigned under ...
8
votes
2answers
601 views
Why can't I use NEWSEQUENTIALID() as the default value for my column?
I'm trying to create a table in Management Studio and after reading about the new (from SQL 2005 on) NEWSEQUENTIALID() function, thought I'd give it a go.
This is what I'm doing:
But it's not ...
4
votes
2answers
280 views
Best way to design this mileage table?
I will be populating a Miles Per Gallon (MPG) table. It's coming from an odometer source.
It's currently set up as so:
id (primary_key)
, truck_num
, start_date
, end_date
, ...
14
votes
4answers
2k views
SQL large table design
I have a general question about SQL Server 2008 table(s) design. We currently have a table that is over 600GB and grows at about 3GB a day. This table has the appropriate indecies but is becoming a ...
4
votes
6answers
896 views
Any way around unique index 16 column max
According to the CREATE INDEX documentation:
Up to 16 columns can be combined into a single composite index key.
We've got a table with ~18 columns that need to form a unique combination. This ...
6
votes
2answers
641 views
How does using separate schemas affect SQL Server 2008 performance?
I want to use separate schemas for objects with different purposes in our SQL Server 2008 database. Right now we use a fairly mind-numbing naming convention to indicate the purpose of a table or ...
19
votes
7answers
4k views
Writing a simple bank schema: How should I keep my balances in sync with their transaction history?
I am writing the schema for a simple bank database. Here are the basic specifications:
The database will store transactions against a user and currency.
Every user has one balance per currency, so ...
1
vote
2answers
253 views
Indexing of Data Warehouse tables?
Maybe I have misunderstood something but I can recall reading an article which said that there is no reason to create non-clustered indexes on very large data warehousing tables? That is because ...
8
votes
5answers
886 views
Do empty columns take up space in a table?
I have table that holds from very basic info. Just a title and a few date fields.
There's one field called comments which is varchar(4000)
Most of the time we leave it blank, but some times will enter ...
