Tag: database-design

Efficient Ad-hoc SQL OLAP Structure

Over the years I have read a lot of people’s opinions on how to get better performance out of their SQL (Microsoft SQL Server, just so we are all on the same page…) queries. However, they all seem to be tightly tied to either a high-performance OLTP setup or a data warehouse OLAP setup (cubes-galore…). […]

Re-using aggregate level formulas in SQL – any good tactics?

Imagine this case, but with a lot more component buckets and a lot more intermediates and outputs. Many of the intermediates are calculated at the detail level, but a few things are calculated at the aggregate level: DECLARE @Profitability AS TABLE ( Cust INT NOT NULL ,Category VARCHAR(10) NOT NULL ,Income DECIMAL(10, 2) NOT NULL […]

schema for storing different varchar fields over time?

This app I’m working on needs to store some meta data fields about an entity. The problem is that we can already foresee that these fields are going to change a lot in the future. Right now every entity’s property is translated to one column in the entity table, but altering table columns later down […]

database design pattern: many to many relationship across tables?

I have the following tables: Section and Content And I want to relate them. My current approach is the following table: In which I would store Section to Section Section to Content Content to Section Content to Content Now, while I clearly can do that by adding a pair of fields that indicate whether the […]

SqlServer constrain field value dependent on another field value

As the question title, is there a way to do this? eg: DrivingLicence ————— CanDriveCar (bool) CanDriveMotorbike (bool) CanDriveBus (bool) You can’t drive a bus without a car licence, so I want the DB to throw an exception if anyone tries to give someone a bus licence when they haven’t a car licence.

Create a join that can (include some) or (include all except some) records

Consider the following two tables: User – UserID – UserName Group – GroupID – GroupName The obvious association is that Users will be in Groups. This by itself is a simple many-to-many join situation, so lets add a 3rd table: UserGroup – UserID – GroupID Under this textbook schema, I can easily include a specific […]

Please help explain if I'm destroying my DB Schema for the sake of performance :(

I’ve got a database in production for nearly 3 years, on Sql 2008 (was ’05, before that). Has been fine, but it isn’t very performant. So i’m tweaking the schema and queries to help speed some things up. Also, a score of main tables contain around 1-3 mill rows, per table (to give u a […]

SQL Server – Syncing two database

We have a warehouse database that contains a year of data up to now. I want to create report database that represents the last 3 months of data for reporting purposes. I want to be able to keep the two databases in sync. Right now, every 10 minutes I execute a package that will grab […]

SQL Table design assitance

I’m building multiple web based tools for a business group. Many of these tools require a managed list of city names. Right now, each tool has its own look-up table of locations and I’d like to centralize it so one location table can be used for all tools, so a location doesn’t need to be […]

SQL Server Schema Design

I have several Products – Plants relationships that I need to create tables in SQL SERVER. A product (around 8000) is manufactured in all 8 Plants but rarely (3-4 products out of 8000) a Product is manufactured in one/two Plants. I am thinking to implement relation-ship in either of two ways. (I know 1st approach […]

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