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 (2)

6
votes
2answers
6k 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 ...
6
votes
4answers
202 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
619 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
4answers
3k 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?
6
votes
1answer
9k 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 ...
6
votes
2answers
434 views

How can I organize my tables?

My database contains more than 200 tables. When I want to modify or insert a table, I find it difficult to find it among all those tables. Is there any way to collect related tables in a folder ...
6
votes
1answer
666 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 ...
6
votes
2answers
228 views

Design: How do you decide when items are related vs when they are attributes?

I've been trying to figure this out for a while. Let's say you have a person, for example. An attribute of a person is his or her social security number, right? But a person also has a social ...
6
votes
1answer
557 views

What makes an OLTP database an OLTP database?

This is a pretty broad question. I know the definition of OLTP. But it seems like unless you have an environment that has a clear distinction between OLTP and OLAP, in the sense of a database that ...
6
votes
2answers
174 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
1answer
2k views

2NF decomposition + database normalization

If anyone knows about 2NF I'd greatly appreciate if you could tell me if my understanding of it correct my book does not even mention it (except to say is of "historical significance") and I haven't ...
6
votes
2answers
426 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 ...
6
votes
3answers
992 views

How to normalize Time Sheet data?

I am having a terrible time finding an appropriate way to store this data in a database. The current excel (simple) version looks something like this: http://i.stack.imgur.com/BEZOw.png Staff and ...
6
votes
1answer
174 views

Better to use a RANGE RIGHT or RANGE LEFT Partition Scheme

I'd like to know if it is better to use a RANGE RIGHT partition scheme or a RANGE LEFT partition scheme? Is one of these options by default a better choice, or is it heavily dependent on the actual ...
6
votes
2answers
530 views

Extracting entity from attribute in entity-relationship diagram

I am developing a database model where I need to capture information about rent contracts for parking places within a parking. I have thus defined an entity called PARKING CONTRACT. Requirements (r.) ...
6
votes
1answer
941 views

What should every DBA know? [closed]

As a SysAdmin who "wears all the hats", I find myself increasingly pulled into database projects. I took one database course back in college and since then I've always scoured resources online (like ...
6
votes
3answers
448 views

Representing N:N relation as a functional dependency in a database design

As a software developer, I already have some experience in designing a more or less normalized database schema, but I haven't received any formal training in it before. This semester, I took a ...
6
votes
1answer
361 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 ...
6
votes
1answer
179 views

Use multiple columns for location or supertype/subtype?

Given an Equipment entity, it has a location, generically called Whereabouts. This could be a reference to: a User entity (the employee who will use and be responsible for the eqipment for the next ...
6
votes
1answer
876 views

Schema designing for handling multiple payment gateways

This is more of a question that requires feedback. I'm designing a database that handles multiple payment gateways. A payment gateway mostly requires a table for order details before making the ...
6
votes
2answers
283 views

Definition for “logical difference”?

I'm currently reading "SQL and Relational Theory" by C.J. Date. And although I'm quite far in the book, I've got a few basic questions. I want to know what the term "logical difference" means, The ...
6
votes
1answer
138 views

What is a good way to determine the performance I should expect from a PostgreSQL server?

Trying to figure out how I should expect my database to perform. Basically, I need to determine when the server I have set up is reaching its performance limits - which would help me plan server ...
6
votes
1answer
2k views

Could too many idle connections affect PostgreSQL 9.2 performance?

Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I ...
6
votes
2answers
1k 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 ...
5
votes
5answers
5k views

Pros/Cons Using multiple databases vs using single database

Working on a new project which has the requirement to use 7 databases. Arguing performance, stability, optimization is easier implemented. While I don't agree I'm having trouble collecting good ...
5
votes
6answers
147 views

How to do a bulk load of a database when application are relying on it?

In a daily routine, I need to drop and do a bulk load of a database. The problem is that I have a WebApp relying on that database. So I can't drop the database just like this. What would be a good ...
5
votes
2answers
351 views

Introducing an 'age' category in a patients database

I want to do the following in my DB related to patients: when patient inserts his birthday it shows his age category as: infants, children, adult, elderly... etc. to help me classify my patients how ...
5
votes
2answers
383 views

How to design index for faster deletes

I want to speed up following piece of code delete from ssn_sdo where (art_id=@art_id and skl_id=@skl_id and @level=0 ) or (art_id=@art_id and skl_id=@skl_id and @level=1 and ...
5
votes
2answers
199 views

Is there a name for this “restricted one to many” relationship? Can it be enforced in the DB?

In an application I'm working on, we have a kind of "restricted one-to-many" relationship. I'm wondering if this has a name, and whether it's possible to enforce at the database level. A standard ...
5
votes
3answers
2k 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 ...
5
votes
2answers
734 views

What are the advantages of a database design with single table for MtM relationships?

The database that backs our software product has a table design like the following: Positions ID Name Parts 1 One 12345 2 Two 12346 3 Three 12347 Collections ID ...
5
votes
2answers
517 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?
5
votes
4answers
483 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 ...
5
votes
4answers
3k views

Database design for a time tracking application

I'm designing the database schema for a time tracking application and I need a little piece of advice. The application must permit the user to enter for each day of the week the amount of time he ...
5
votes
4answers
6k 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 ...
5
votes
2answers
266 views

Alternative to EAV for dynamic fields in a star schema data warehouse

I need to support dynamic fields and values in a big datawarehouse for storing API requests log, my user case is that I need to store all API requests query string and able to perform query against ...
5
votes
1answer
1k views

What's difference between standard view and indexed view in SQL Server [closed]

I learn that the indexed view holds the data from base tables while standard view does not holds data.Is it means that the indexed view need memory and standard view don't? Indexed view needs to be ...
5
votes
2answers
1k 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 ...
5
votes
2answers
811 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 ...
5
votes
1answer
295 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 ...
5
votes
3answers
574 views

how do I model this in database?

I am using mysql as my backend database. Here is the use case; A HolidayPackage is configured with some default offerings. These default offerings can be up/downgraded based on the options provided. ...
5
votes
3answers
689 views

Scalable table structure for periodically updated stats that get aggregated over time

I receive statistical data every 30 seconds that I want to store in my database so that I can analyze later. For example, every 30 seconds I could receive the number of oranges sold at a store in the ...
5
votes
1answer
307 views

How can I design a table for quick versioning of textual data?

Version i am talking about here is not of version control systems. Like in SO i got some textual data(compare them to questions) that could be versioned(in the sense edited, saved, rollback). Content ...
5
votes
1answer
565 views

LDF and MDF file sizes for a dynamically created Database

I am creating a dynamic database from C# code, and would like the database to have a fixed size (for example 5 GB). While creating the DB through code, I need to specify MDF and LDF max file sizes. ...
5
votes
3answers
641 views

Should I seperate 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 ...
5
votes
1answer
1k 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 ...
5
votes
1answer
325 views

Best way to index a table with two datetime columns that are equally queried

Sql Server 2008 R2. I have a table with ~70m records, about 10 inserts per second. It's currently clustered on a CreatedAt datetime column, which always increases. 50% of queries involve this ...
5
votes
2answers
930 views

What Is The Point of a Primary Key?

Why do I need to have a primary key on my database for it to function correctly? In every tutorial I read, you need to make the id key the primary key. What does the primary key do differently than ...
5
votes
2answers
1k views

Database Hardware and Software to handle million requests per second?

I'm curious about the necessary hardware and software requirements to support a strong MMORPG. For example, what kind of requirement (both hardware and software) would I need if I were to build a ...
5
votes
2answers
8k views

Relational Design - Multiple tables into one foreign key column?

Take a table that tracks meat purchases. It has a "meat_id" foreign key column to indicate what type of meat the purchase was. But, the different types of meat are unique in some way (such as USDA ...