Database design is the process of specifying the logical and/or physical parts of a database. The goal of database design is to make a representation of some "universe of discourse" - the types of facts, business rules and other requirements that the database is intended to model.

learn more… | top users | synonyms (4)

7
votes
2answers
2k views

Fact table foreign keys null?

I am new to data mart design and need to clear a few concepts. I have read up a bit on dimension modelling where I see that fact tables store foreign key references to dimension tables. Now suppose ...
7
votes
2answers
1k views

Database for building a realtime analytics system

I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on Real time counting of unique URI visit/PV by different ...
7
votes
2answers
1k views

How to find parent rows that have indentical sets of child rows?

Suppose I have structure like this: Recipes Table: RecipeID Name Description RecipeIngredients Table: RecipeID IngredientID Quantity UOM What are some good ways for ...
7
votes
3answers
12k views

Visio 2010 and SQL 2012 - Reverse Enginnering

We have upgraded to SQL 2012 and Am trying to reverse engineer our Dbs into Visio 2010. I setup a connecting using SQL 11 driver and Visio complains that that driver is not supported. Any one run ...
7
votes
4answers
11k views

How to design a relationship database table to store friendship relationship?

I want to design a table to store friendship relationship in my web project It should satisfy at least the following 4 conditions: who send the add-friend request e.g.(if A TO B then this ...
7
votes
2answers
2k views

Should a log table get an id field or primary key?

I have a log table that captures the datetime stamp of when certain files were exported to another system. The exportedLog table currently has three fields: id (primary key) messageId ...
7
votes
2answers
624 views

Clustered indexing is now must - why?

Earlier, there were non-conclusive to me debates/discussions on whether to (always) engage/avoid clustered indexes. Well, I understood that they are to be used sometimes with proper + specific ...
7
votes
4answers
5k views

Finding empty columns of a table in PostgreSQL

What query would return the name of the columns of a table where all rows are NULL?
7
votes
2answers
1k views

Is my understanding of Fact table granularity correct?

Myself and another DBA at our company are tasked with reviewing a database design that a vendor has developed for us. The vendor has said they use Kimball as the basis for their design. (NOTE: I am ...
7
votes
1answer
20k views

What happens when you modify (reduce) a column's length?

Lets say I have two columns of type NUMBER (without precision, and scale) and VARCHAR(300). I saw that these columns are way too large for my data, so I want to modify them to NUMBER(11) and ...
7
votes
5answers
3k views

Are there opensource hierarchical database management systems

I was looking for a hierarchical database management system and the only one i came across was IBM's ims.Are there opensource systems that one can use?.
7
votes
3answers
868 views

Should I separate frequently updated columns?

I have a users table that contains users' information and a column named credits that is frequently updated. On index page I'm showing list of users with basic user information but I don't need ...
7
votes
2answers
7k views

How can you have two auto-incremental columns in one table?

I have a MySQL table that contains information on invoices of a company. However, this company has two branches, and each of them has a unique invoicing sequence; a "Serie A" and "Serie B", so to ...
7
votes
2answers
5k views

Designing a user authenication (Roles & Rights) module

I am trying to model a User Authentication module for a MS SQL Server database that will be the back end to a Delphi UI Application. Basically, I want to have user accounts where the user belongs to ...
7
votes
5answers
4k views

What is the most efficient way to alter a column definition in a table with millions of rows

I need to change a column from NOT NULL to NULL in a table that contains millions of rows. I've tried a simple alter table Table1 ALTER COLUMN Column1 XML NULL but it takes forever. So here are my ...
7
votes
1answer
302 views

Adding nullable column to table costs more than 10 minutes

I have problems to add a new column on a table. I tried to run it a couple of times, but after more than 10 minutes running, I decided to cancel the query because of lock time. ALTER TABLE mytable ...
7
votes
3answers
2k views

Foreign keys - link using surrogate or natural key?

Is there a best practice for whether a foreign key between tables should link to a natural key or a surrogate key? The only discussion I've really found (unless my google-fu is lacking) is Jack ...
7
votes
3answers
2k views

Store a formula in a table and use the formula in a function

I have a PostgreSQL 9.1 database where part of it handles agent commissions. Each agent has his/her own formula of calculation how much commission they get. I have a function to generate the amount of ...
7
votes
2answers
422 views

Can I losslessly decompose this table?

I've stumbled across a database design problem that's out of my league, and my go-to DBA guru is off on fire drills. In essence, I have a table with the following primary key: child_id integer ...
7
votes
1answer
1k views

Best practice for storing record metadata

What is the best practice for storing metadata of individual records in a database? I need to store common meta data such as creation time and time of last update for many tables in my database. I ...
7
votes
3answers
2k views

Appropriate multi-language schema, or overkill?

UPDATE 2: I've actually ended up using this, and it's great after a couple tweaks. Here's my post on its actual design, and in action: http://tim.hithlonde.com/2013/lemon-schema-works/ I am building ...
7
votes
1answer
93 views

sysschedules datatype choices

I am curious, why does SQL Server save the values in msdb.dbo.sysschedules for date and times as int instead of datetime. I assume the reason dates back to something in SQL Server 2000. Was it a ...
7
votes
1answer
2k 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 ...
7
votes
3answers
192 views

Choice- or ids-list- field representation

I wonder what is the best way to represent a user setting that could either take a value from a set (such as 'High', 'Medium' or 'Low') or be a list of IDs from another (or the same!) table (Many to ...
7
votes
1answer
239 views

How can I make my database more concise?

I am writing an application for Android using SQLite (backed by ORMlite if it matters). So far I'm in the conceptual stage and working on the database design. The database is being designed to contain ...
7
votes
1answer
512 views

How to store and query products the price of which depends on a complex set of conditions?

I haven't decided on a database. I have expirience with MySQl and I'm intrigued by MongoDB. Let the products be packages of cable tv providers: { name: "Virgin TV", price: 20 }, { name: ...
7
votes
2answers
8k views

SQL Server: query performance (search 2 million rows)

Got an interesting one for all of you SQL guru's out there. Now this search is only taking a couple of seconds, but it's quite intensive and there must be a better way. Maybe I'm expecting too much? ...
7
votes
1answer
329 views

Could Stack Overflow run on SQL Server Web Edition?

I am starting a new web venture that may need to scale to a high number of users. I am confident with the SPLA licencing for SQL Server Web edition, but want to know if I will need to factor in ...
7
votes
2answers
605 views

Best relational database structure for this data

I'm in the process of creating a database scheme for the following scenario: There are users Users have roles (such as "Developer" or "CEO") Roles have applications (such as "Topdesk") Applications ...
7
votes
6answers
3k views

Best way to document database mappings for migrations [closed]

I'm working on a project that includes mapping database elements for a migration and I wanted to know what tools others were using to do this? Excel is a very flexible way to document simple ...
7
votes
1answer
151 views

Is it ever ok to not test a feature? [closed]

Is there any point at which you become so familiar with your language/database/system that there is no need to test a new feature/configuration/query/etc. by contained/simulated testing before ...
7
votes
1answer
520 views

Temporal Validity and Primary/Foreign Key Relationships

I've read through several of the oracle tutorials that show temporal validity and time features. However, in the examples I'v read, there is no primary key used in the demo tables. ...
7
votes
1answer
299 views

Performance of query with a range condition and order by

We have the following table (in SQLite on Android) which a tree structure (Nested Set model) of words and their frequencies: lexikon ------- _id integer PRIMARY KEY word text frequency integer ...
6
votes
4answers
518 views

Purpose of IDs in a User Table Database

In a table, such as a user table, where there will only be 1 row with a for any particular user, why have an ID field, let alone as the primary key?
6
votes
2answers
397 views

What is the name of this schema pattern

Its been a few years... but I've run into a problem-set that reminds me of a schema design pattern and I'm trying to recall the details. Essentially, the pattern consists of one database which is 3 ...
6
votes
5answers
3k views

Sharing a single primary key sequence across a database?

Is it an acceptable practice to use a single sequence as a primary key across all tables (instead of a primary key being unique for a given table, it is unique for all tables)? If so, is it ...
6
votes
4answers
690 views

How can a database be normalized when an optional field causes denormalization?

Suppose you have a warehouse full of widgets. Each widget in the warehouse is stored in a specific identifiable location within the warehouse. You might have a schema which looks like this: A ...
6
votes
3answers
3k views

SQL Server Primary key / clustered index design decision

Looking for some advice regarding a table / index design decision I've got to make on some tables that I've got to port into SQL server from an existing 4GL based database. I've got a product history ...
6
votes
3answers
1k views

PK as ROWGUIDCOL or use a separate rowguid column?

There's a long-winded debate going on here so I'd like to hear other opinions. I have many tables with uniqueidentifier clustered PK. Whether this is a good idea is out of scope here (and it's not ...
6
votes
2answers
1k views

Increase all primary key values by 1

How do I increase all of my EmployeeID values by one if it is the primary key?
6
votes
2answers
2k views

Why use separate databases for high traffic/volume tables?

While looking at the database structure of an application I am using I recognized that it uses 3 different databases on the same SQL Server instance for different things. The first one contains the ...
6
votes
4answers
716 views

Can this business logic be enforced by a conditional database constraint?

I am trying to duplicate the business logic embodied an intranet C# web application in the database so that other databases can access it and work under the same rules. This "rule" seems difficult to ...
6
votes
2answers
297 views

Am I violating any NF rule on my database design?

I am a newbie on creating database... I need to create it for my recruitment web application. My application needs to schedule screenings, exams and interviews of applicants and save result in the ...
6
votes
2answers
2k views

Best way to handle dates prior to 1000 A.D. in MySQL?

I am creating a database for records that extend prior to 1000 AD, but MySQL Date and DateTime fields only support dates starting at 1000. Is there a way that would be more convenient than either ...
6
votes
3answers
401 views

Help with a scenario of music groups and members in ERD design

Can someone please show me how to construct this ER diagram with the following scenario? I’m confused with the ‘or’ part of it. Description An Artist has a Name, and must be either a Group or a ...
6
votes
4answers
228 views

Dynamic contacts database

I'm trying to create a database for contacts. I must have many categories for every contact, like Home, Office, Emails, Phones. In every category one can add more than one value. Also the categories ...
6
votes
6answers
808 views

What is the best database design for this situation?

I'm working on building a business application for my company and struggling to pick the most appropriate database design for a particular situation. Let's say I have the following entities: ...
6
votes
1answer
4k views

Many to Many and Weak Entities

I have an entity that cannot exist without being defined by another, and I want this entity to participate in a many-to-many relationship. Example: An artist has an album (the album cannot exist ...
6
votes
2answers
392 views

Should I add a column or 1-1 table if property is usualy null

There's a new unique property we need to add, which is relevant only to a specific subset (less than 1%). We thought it's better to add a new table which will be 1-1, than to add a new column which ...
6
votes
2answers
25k 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 ...