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)

5
votes
4answers
4k 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
1answer
377 views

Bad Design or Complicated Query?

Problem: For fun I am building a Database for lottery results to get some statistics off of. As of now I am only interested in a Pick 3 style lotto and might introduce more later. How would you ...
5
votes
1answer
2k 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
4answers
2k views

Are there any free online collaboration database schema tools? [closed]

I am interested in a free and simple MySQL tool which can offer online collaboration functionality in creating database general schema. Do you know any?
5
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 ...
5
votes
1answer
309 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
616 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
801 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
356 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
617 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
1answer
111 views

For alignment optimized table is bigger than original table - why?

In another question I learned that I should optimize the layout from one of my tables to save space and have better performance. I did this, but ended up with a larger table than before and ...
5
votes
1answer
2k 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
358 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
1k 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
9k 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 ...
5
votes
5answers
3k 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 ...
5
votes
3answers
610 views

Embedding vs foreign table in one-to-one relations?

I'm currently taking a university course about databases. Consider the following modeling scenario where a car exists only in the context of its owning student: Student (0,1) <---> (1,1) Car ...
5
votes
2answers
2k views

Composite Primary Key efficiency as a Foreign Key

I have a table with a Composite Primary key (consisting of 4 columns) which is used to ensure no duplicates are entered into the table. I am now in need of a new table which will need to reference the ...
5
votes
2answers
3k 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 ...
5
votes
2answers
1k views

free database modelling software

What is available in the realms of database modelling software that is free. I am developing an SQLExpress database. CA ERWin - community version although is free, I can't see an option to allow ...
5
votes
1answer
636 views

Denormalized table designs in normalized database?

There was a discussion between the developers and DBA's last week on set of tables that the developers were creating with more than 400 columns in our OLTP environment with combinations of VARCHARs, ...
5
votes
2answers
6k views

How can I specify the position for a new column in PostgreSQL?

If I have a table with the columns: id | name | created_date and would like to add a column, I use: alter table my_table add column email varchar(255) Then the column is added after the ...
5
votes
2answers
325 views

Best design for adding a folder/file relationship

I was given a database that has Projects and Media_files. They were in a simple 1 to many relationship. I now need to add Folders to Projects. Media_files can be placed in Folders, and Folders ...
5
votes
1answer
120 views

Many:Many with Shared Relation

I'm modelling data with multiplicity like this: Each Composition/Anthology related pair must share a Composer. Also, each Anthology must contain at least one Composition. How would you recommend I ...
5
votes
3answers
4k views

What are the advantages and disadvantages of storing binary data in a SQL table vs a network drive? [duplicate]

Possible Duplicate: Files - in the database or not? I've been reading "Programming Entity Framework Code First" ...
5
votes
2answers
223 views

How many fields is too much for a primary key?

I am creating a table to store information about the placement of objects in a grid (game map). For example, I have a 10x10 grid and I want to store, in the database, that cell (5,5) has a blue box, ...
5
votes
3answers
1k 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 ...
5
votes
2answers
176 views

Web Database - How to store user information

I'm starting to design a database to use in a web solution and would like to understand if there is any benefit to storing passwords (and the rest of the user information for that matter) in a ...
5
votes
1answer
1k views

EAV structure explained in Layman's terms

I've read several articles online that explain how EAV structures work, but there are different interpretations of them. Some articles mention that it's a representation of a data model metadata (or ...
5
votes
2answers
279 views

Help with good RDBMS transactional schema design that mimicks a sports league

I need to design a data model for a typical RDBMS that will mimic the structure of your typical sports league. Architecturally my requirements are: Well normalized Transactional Application ...
5
votes
2answers
220 views

Is there a reason/benefit I am not aware of for this kind setup?

I now manage a MySQL database that was designed and created by another. I am confused about a certain setup that the original developer created.. He did not use auto increments for table IDs. ...
5
votes
2answers
872 views

How can I save a severely fragmented database/table?

I've recently made a discovery that might explain my slow SQL run times. I found out that the default in SSMS is to allow mdf growth of 1MB at a time. When the size of one table alone is roughly 23 ...
5
votes
2answers
95 views

Per person weighted answers

Hi I'm new to database design but am trying to make sure I get things right first time so as not to pick up bad habits. My question is I have a set of people for which I want to record needs mapped to ...
5
votes
2answers
541 views

How should I model an “either/or” relationship?

Say I have an entity named Software and two sub-types FreeSoftware and NonFreeSoftware. The NonFreeSoftware entity has attributes such as purchase date, vendor, etc.. The FreeSoftware entity has ...
5
votes
1answer
340 views

Dimension Help - Deciding Fact or Dimension

We have the following Dim and Facts: Customer Dim: SCD Type 2, Info about the customer ie first purchase date, name, address, etc Product Dim: SCD Type 2, about our products Customer Snapshot ...
5
votes
2answers
1k views

How to implement soft deletes?

What is your method of implementing soft deletes in a database? Ideally the solution would allow for decent performance on large tables take relationships into account take unique keys into account ...
5
votes
1answer
1k views

How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?

According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause some ...
5
votes
1answer
391 views

How does PostgreSQL physically order new records on disk (after a cluster on primary key)?

Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows ...
5
votes
2answers
111 views

Relationship that are optionally more specific

Forgive my title, I couldn't think of anything that accurately describes what I'm talking about. I currently have the following relationship. CREATE TABLE Events ( ID INT IDENTITY(1,1) NOT ...
5
votes
2answers
873 views

Choosing MyISAM over InnoDB for these project requirements; and long term options

Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific. My project's aim is to let users search a (huge) database of variety of products. ...
5
votes
3answers
814 views

How to store many smallish time-series in a relational DB?

Input Data Multiple Tests Beds generate measurement data of various complexity. In it's most basic form, not considering any meta-data, one measurement on a Test Bed will be a small (1 - a few ...
5
votes
2answers
224 views

How should an object that can reference an object of multiple types be modeled in a relational schema?

Apologies for the obscure question, it may make more sense with a concrete example: In my application, I may create a portfolio that contains a set of projects. However, I may also add a ...
5
votes
2answers
149 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 ...
5
votes
2answers
902 views

How to design a schema to store search keywords

I am doing a database design that stores search keywords of a search form, used to generate the most popular search keyword reports by date, month and year. I have made a simple schema: CREATE TABLE ...
5
votes
1answer
491 views

Can filtered indexes help improve queries that are based on a time entered or should this be avoided?

I'm just learning about the new filtered indexes in MS SQL 2008 and I'm trying to understand where they would hurt and where they would help. I can see that adding a filtered index to an employees ...
5
votes
1answer
110 views

Many-to-many relationship with attributes

I'm working on a project where I need a book database. The problem is as follows: A book can be related to multiple people (authors, editors, traslators, etc.). Then a person may contribute to ...
5
votes
2answers
50 views

How to enforce the structural constraints of rectangularly arrayed data?

(I'm particularly interested in answers that view the question below as a special case of the question: how should a RDBMS enforce structural constraints that are more specific than "one-to-many" and ...
5
votes
1answer
629 views

Full text search through many tables in MySQL

We have high traffic NEWS websites, I want to add a feature that every user can search through over all content of site, such as news, polls, comments, galleries,etc . Each of contents type has its ...