The logical model and/or physical design of a database.

learn more… | top users | synonyms (4)

28
votes
5answers
30k views

When should you denormalize?

I think we are all familiar with database normalization. My question is: What are some guidelines that you use when you want to denormalize the tables?
8
votes
2answers
4k views

What exactly is a database engine?

I have gone through the definition on http://en.wikipedia.org/wiki/Database_engine several times: A database engine (or "storage engine") is the underlying software component that a database ...
6
votes
2answers
680 views

Table contains multiple names for same company

I have a table of monetary transactions that has a field for who the money was paid to or received from. Transactions are recorded using info from paper invoices, paper reciepts, internet reciepts, ...
26
votes
1answer
10k views

Designing a platform: one database or multiple databases?

We are building a web platform that incorporates multiple services, each with its own underlying data. These services are being built independently following the principles of Service-Oriented ...
2
votes
1answer
293 views

Where to save user data?

I hope this is the right stackexchange place to ask. Im really new using databases and I have a little question: Where to save the user data? I mean, if Im logged as "Foxandxss" on my app, and I want ...
5
votes
2answers
2k views

SQL Server database forum design sample

I am doing a design for a database using SQL Server 2008 R2, basically it is a simple database design for a forum (just like games fans forums). Fortunately I am struggling with it as I didn't find ...
20
votes
1answer
784 views

What is the reasoning behind the CAP theorem?

http://en.wikipedia.org/wiki/CAP_theorem http://www.cs.berkeley.edu/~brewer/cs262b-2004/PODC-keynote.pdf I think it is not very straightforward why only two of Consistency Availability Partition ...
3
votes
2answers
333 views

How can I model this relationship?

My database design skills are a bit rusty so I was hoping to get some help from y'all =) I have a 'user' table that contains all the typical fields (id,name,email,etc.). Users can have many ...
2
votes
1answer
1k views

Track daily view count of youtube videos

I am making program that searches youtube for given keyword and need to get links of active videos. Term "active" means that video must have 1000 views per day. Youtube API doesn't give such ...
4
votes
2answers
1k views

Using IDs from multiple tables in a single column

One of my co-workers created a schema similar to the following. This is a simplified schema including only the parts necessary to address this question. The system rules are as follows: ...
1
vote
1answer
287 views

MySQL Database Design Help?

I am going through my database and what I want to do is re-design it because I feel like it's very messy and that I am doing it wrong. This is just for practice; I've never had anyone teach me so I ...
5
votes
2answers
2k 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 ...
3
votes
2answers
133 views

What would be the best way to model my simple table?

What would be faster: searching for the right row through a table with 17 different columns, each with a tinyint value of 1-4? Or, searching for the right row through a table with only 1 column with ...
14
votes
4answers
6k views

Whether or not to create separate tables for different product types?

I'm in the process of designing a database and I'm having second thoughts about my initial design decisions... Product types are as follows... Models, parts, replacement part kits and options. ...
4
votes
1answer
375 views

Proper Database Partitioning

I had asked a previous question "Proper technique for storing users event data" and the correct answer in my opinion was to create a database partition. Now from what I have read on it there are ...
5
votes
2answers
2k views

Creating partitions on a production database

I have a production MySQL 5.1 database that is running well but I want to improve query performances. I have never used Partitions and just going through the manuals. I have two tables that involve a ...
4
votes
3answers
994 views

Table structure for many custom profiles

I'm currently developing an bulk-email campaign manager that will allow customers to upload as much information as they would like on their subscribers. For example, I have a basic profile that is ...
0
votes
1answer
1k views

Database design for head to head sports

I am trying to create a database to store details relating to darts matches. Im relatively new to this so was wondering if the design I've come up with below would work. It seems I have lots of places ...
10
votes
2answers
2k views

Proper technique for storing users event data

I am mostly a self-taught when it comes to database designs. I am posing this question because I have settled on this common structure, but am wondering if it is the most efficient or 'industry ...
3
votes
2answers
103 views

Exclude dataset from a query

I have 2 tables EXAM (StudentID, SubjectID, SchoolYear, Period, Mark) and CANCELED_EXAM (StudentID, SubjectID, SchoolYear, Period). Primary key for both tables are (StudentID, SubjectID, ...
4
votes
4answers
997 views

Speed problem with a simple select statement

I'm having a problem with a select statement taking way too long to run. I would like to know if I'm doing something wrong or if it taking this long makes sense. I am using Microsoft SQL 2005 ...
3
votes
2answers
3k views

Most Effificient Way to Store Ingredients in a Food Database

I have a database that I've called "food" and I'm a bit stumped on how to store the ingredients for a recipe. Should I create a separate table for the ingredients or just have the ingredients insert ...
9
votes
5answers
12k 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 ...
2
votes
4answers
177 views

Is there a “standard way” to log network messages (events) into a database?

I have two applications communicating over a LAN via events contained in network messages. Each message (i.e. an UDP packet) contains only one event. Messages are heterogeneous, meaning that the first ...
37
votes
6answers
30k views

Best practices on common person fields (Name, email, address, gender etc…)

What are the most common best practices on length and data type on common fields like: First Name Last Name Address Email Sex State City Country Phone Number etc....
8
votes
9answers
2k views

Which DBMS are fast enough for an online game (few thousand players)?

I'm currently making an MMORPG game, which could have a few thousand players online at the same time (probably not; just wishful thinking). First we wanted to use MySQL, but I have heard it isn't fast ...
4
votes
2answers
2k views

Data validity and accuracy according to database integrity

What does it mean that data is valid and accurate according to database integrity?
2
votes
2answers
88 views

Importing order information

Our new project is to write a customer order management system. My specific task is to import customer orders from various different sources - XML, CSV, XLS... etc. Our old DBA has left the company ...
13
votes
3answers
4k views

Column name naming conventions and best practices

I would like some expert opinion on best practices when it comes to column naming. The background is that according to Wikipedia, the following syntax, SELECT ... FROM Employees JOIN Timesheets ...
6
votes
2answers
2k views

Unknown Db file format - best way to find out?

A client has given me the data he wants visualised in my project - but I don't know what DB format it's in. He's pretty unclear, also.. I have access to the table structures, but don't know how to ...
7
votes
1answer
907 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. ...
3
votes
2answers
7k views

Database to track employee performance

This is going to be very basic as I'm a complete newbie when it comes to database design. I've been tasked with setting up a way to track employee data over a number of months and potentially years. ...
6
votes
6answers
155 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 ...
3
votes
2answers
5k views

Database Design for Airline Reservation System

I'm just a beginner seeking some answers. I'm creating a very simple database system for an airline company, just for learning purposes. Here are my questions: How to show relationships for entities ...
8
votes
2answers
514 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 ...
5
votes
2answers
10k views

What are some common and useful fields for a users table in a database?

I want to begin implementing user functionality on my website, but I'm not sure exactly what kind of information I should be storing. Obviously the basic things like username, password, password hash, ...
10
votes
3answers
6k views

Is there a standard implementation of a Users Database?

I need to implement basic personalized user functionality for my website. Is there a standard structure for databases of this type? Like is it common practice to have all user info and data in a ...
8
votes
2answers
3k 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 ...
3
votes
1answer
923 views

Groups, Users, and Inbox

We're working on a PM system where a user can send an email to other users or groups. We currently have the following tables: Users Groups Users can be part of many groups. A user doesn't have to ...
5
votes
2answers
2k views

Removing Archive Logs after PostgreSQL PITR online-recovery

I'm using pgpool-II + PostgreSQL 8.4 on three servers: primary + standby1 + standby2. Replication mode is "on" Load balance mode is "on" (between primary and standby1) I followed the official ...
5
votes
1answer
62 views

How to link Similar-but-distinct Models of Medical Imaging Data?

I'm tracking a set of medical images in a logbook-type application, and am having a problem with how to model my records. The problem is that for every group of images collected, there are two ...
17
votes
2answers
17k 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
1answer
441 views

Temporal database design, with a twist (live vs draft rows)

Not sure if/how I should be cross-posting this... But in case some follow DBA but not SO... Is there a generally accepted means of managing live vs non-live rows in a row-version controlled ...
4
votes
1answer
328 views

What scope(s) is “hasMany” a part of?

I realize, when talking about structures, obviously if you say "post hasMany comments" there are many records about comments relating to each post. What I don't understand is what context these sort ...
22
votes
5answers
17k views

In SQL, is it composite or compound keys?

About SQL (Computing/Databases): When we have two or more fields in a table that, together, identify its records uniquely, what's the proper way of calling them? Composite or compound keys? I've ...
8
votes
2answers
387 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 ...
5
votes
3answers
1k views

Suggestion required for Large Database Optimization and performance

We are creating a large database with estimated growth rate 200 million+ records per annum. The database is use for analysis purpose and we will generate different tabular and graphical reports from ...
9
votes
3answers
2k views

Suggestion for Large SQL Server Database Design

We are creating a database in MSSQL 2008 R2 Standard where we will be storing a large number of records. We estimate 200 million+ records in one table annually and we are primarily INSERTing with very ...
2
votes
1answer
579 views

Which NoSQL database(s) should be used for advertising platform? [closed]

What NoSQL database or combination of databases best meets these criteria? Very fast writes and reads Persistent data that won't get lost in a crash Plays nice with PHP Works well in the cloud (EC2) ...
5
votes
2answers
489 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 ...