Tag: database-design

SQL Server to PostgreSQL – Migration and design concerns

Currently migrating from SQL Server to PostgreSQL and attempting to improve a couple of key areas on the way: I have an Articles table: CREATE TABLE [dbo].[Articles]( [server_ref] [int] NOT NULL, [article_ref] [int] NOT NULL, [article_title] [varchar](400) NOT NULL, [category_ref] [int] NOT NULL, [size] [bigint] NOT NULL ) Data (comma delimited text files) is dumped […]

How Many Tables Should be Placed on the Same Partition Scheme?

My company just provided me with SQL Server 2005 Enterprise Edition and I wanted to partition some tables with large(r) amounts of data. I have about about 5 or 6 tables which would be a good fit to partition by datetime. There will be some queries that need 2 of these tables in the course […]

SQL Server Database Design Problem/Challenge

I have a database that has node & nodetype tables. Nodes table NodeID ParentNodeID NodeTypeID NodeName … NodeType Table NodeTypeID ParentNodeTypeID NodeTypeName ….. Both tables have a relationship to itself. There are different types of node i.e Node Site Building Office These are hierarchical, so information (attributes) that is applied to i.e Nodes of type […]

SQL design for various data types

I need to store data in a SQL Server 2008 database from various data sources with different data types. Data types allowed are: Bit, Numeric (1, 2 or 4 bytes), Real and String. There is going to be a value, a timestamp, a FK to the item of which the value belongs and some other […]

Any tool to automatically de-normalize a database?

I have got a normalized transactional database. I am setting up a data warehouse for reporting purposes. Are there any tools which will automatically make de-normalization suggestions? Or something to automate database changes in that manner?

Preparing to move to a single database

We have an application that has 1000+ databases and 600+ sprocs. Each database represents a different client. Problem: We need to move this to a single database while creating as little effect on the ui as possible, meaning dont change all the sproc signatures at 1 time. The connection string currently sets the database attribute, […]

Is using multiple tables an advisable solution to dealing with user defined fields?

I am looking at a problem which would involve users uploading lists of records with various field structures into an application. The 2nd part of this would be to also allow the users to specify fields to capture information. This is a step beyond anything ive done up to this point where i would have […]

Best way to store and retrieve comment replies in sql server

I want to store comment replies in database table. I have a table to store comments: comment_id comment_par_id, comment_from comment_text comment date …. New comment has par_id=0 while the replies has par_id set to comment id to which it was replied. The nesting is just one level. Reply to a reply also has the same […]

When to use a covering index, a composite index, and unique columnar indexes

Let’s say I have the following table in SQL Server 2008: ProfileID int //identity; index: unique, primary key, clustered ClientID int RegionID int ProfileName nvarchar(50) Columns 2 and 3 link to their respective tables via foreign relationships. Let’s say my most common query is this: SELECT ProfileID, ProfileName FROM Profiles WHERE ClientID = ? AND […]

Need recommendation for a table structure

I have an entity which has 4 different types of property that could have only one value for each case which are boolean, decimal, string or text. I don’t want to define the table with 4 boolean, decimal, nvarchar and ntext columns. What would you recommend to cover this case? Update: I’m using MS SQL […]

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