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)

8
votes
3answers
323 views

Database design advice

I'm designing a database for our sales team to use as a quick job quoting tool. I would like some feedback on a particular aspect of the design. A quote is basically built up by selecting a list of ...
8
votes
1answer
2k views

Generating Invoices and Tracking

Every 2 weeks, the system will generate the invoices for the companies. Company will receive an invoice on the 1st and 16th every month. (It will run via Cron Job every 2 week. It scan through the ...
8
votes
2answers
1k views

How does using separate schemas affect SQL Server 2008 performance?

I want to use separate schemas for objects with different purposes in our SQL Server 2008 database. Right now we use a fairly mind-numbing naming convention to indicate the purpose of a table or ...
8
votes
1answer
84 views

Which schools are known for graduation level database research?

Which schools across the world are best known for research being carried out in database field in recent times?
8
votes
1answer
183 views

Relational database: in-RAM partitioning? Theoretical structure discussion

I'm rewriting an MMORPG server engine using some rather esoteric elements (theoretically good but rarely used in practice), and having a bit of doubt. Some elements of this are “solid” — but the point ...
8
votes
2answers
1k views

SQL Server database design for “archived but available” data

We have this large database (>1TB) that we intend to "shrink". The database revolves around one main entity, let's call it "Visit". For discussion, let's say it is a database for a medical practice. ...
7
votes
5answers
1k views

Database design: Dividing multiple identical tables, good or bad?

I am very new at SQL and databases in general. I only use them for the occasional homework so I haven't even tried to master them. I have seats at a theater, the seats are divided into 4 main areas ...
7
votes
3answers
1k views

SQL Database Structure for RESTful API

I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources. Initially, I though a table per resource would be a good way to go, but I'm ...
7
votes
4answers
632 views

How to create a database for unknown kinds of data?

I am in the process of designing a database for a new PHP/MySql-based application. My problem is that I do not and cannot represent what should be saved in the database because it is unlimited and ...
7
votes
6answers
6k views

Is there any good tool for making Database Design and Prototypes? [closed]

I would like to have a good tool for designing the Database Schema with all the tables, columns, data types and relations. Today I mostly do this with pen and paper but I would like to do it in a good ...
7
votes
3answers
1k views

What are negative keys used for?

Somewhat new to using standard SQL databases (currently working with MySQL mostly) I haven't run across many usages of this as of yet. When and why is it useful to have negative (or rather signed) ...
7
votes
2answers
402 views

Data Compression Through Base36 Encoding

I'm trying to store a lot of large numbers that truncate when stored as INT values. Unfortunately they are too large for this type. I'm thinking about using Base36 encoding to accomplish this. Are ...
7
votes
3answers
975 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 ...
7
votes
4answers
302 views

how to explain indexes

This question is about teaching: When I learned databases, we talked about indexes being like the card catalog in the town library. We had at least an author index, a title index, then the Dewey ...
7
votes
3answers
366 views

Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server

On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: ...
7
votes
3answers
2k 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 ...
7
votes
2answers
868 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
4k views

How to have a one-to-many relationship with a privileged child?

I want to have a one-to-many relationship in which for each parent, one or zero of the children is marked as a “favorite.” However, not every parent will have a child. (Think of the parents as ...
7
votes
2answers
980 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
5answers
4k views

Survey database design: associate an answer to a user

I'm doing the conceptual model for a survey database. The goal is store the answers given by users (it going to be an Android app). I have three entities: user, question and option. A question will ...
7
votes
4answers
854 views

Are there any good reasons for keeping date and time in separate columns?

I'm trying to understand our software vendor's decision to keep date and time in separate columns. For example, when the row was created or updated. Both time and date are DateTime columns. We are ...
7
votes
1answer
563 views

Concept of Schema in PostgreSQL

I am not able to understand concept and usage of schema in PostgreSQL. I have no idea how it can affect my database design. Why should I use it? Can it affect me in future if I decide to not think ...
7
votes
4answers
248 views

Arbitrarily ordering records in a table

A common need when using a database is to access records in order. For example, if I have a blog, I want to be able to reorder my blog posts in arbitrary order. These entries often have lots of ...
7
votes
2answers
376 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
2answers
5k views

Database behind a Multilingual User Interface

This question is about an issue somewhat more complicated than the one which has already been addressed in these old questions, all of which are duplicates of one another: Suggestion for database ...
7
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?
7
votes
5answers
2k 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
2answers
885 views

How to structure a model to properly and efficiently represent tree-like data on relational databases?

Based on Traversing tree-like data in a relational database using SQL question, I would like to know how the way regularly used to describe tree-like data on relational databases considering physical ...
7
votes
2answers
124 views

Should I add transitive foreign keys?

Simple example: there is a table of customers. create table Customers ( id integer, constraint CustomersPK primary key (id) ) All the other data in the database should link to a Customer, so ...
7
votes
3answers
1k 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
330 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
3answers
1k 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
3answers
185 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
212 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
2answers
5k 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
1k 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 ...
7
votes
1answer
321 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
5answers
1k views

Best way to document database mappings for migrations

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
2answers
424 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
1answer
246 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 ...
7
votes
1answer
322 views

Designing Simple Schema for Disaggregation of Demand Forecast

I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case: I have a parent-child hierarchy of products (example, Raw ...
6
votes
5answers
5k views

Importance of ER Diagrams

I am a student and I am developing several projects as a part of my academia. While developing the database for one of the projects, we came across a situation where we thought about whether ERD is ...
6
votes
3answers
4k views

One Big Database vs. Several Smaller Ones

We have a situation were we can (A) deploy instances of an applications in one MySQL database using table prefixing or (B) use different MySQL databases for each instance of the application, for e.g., ...
6
votes
4answers
487 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
4answers
5k views

Is there a tool to check if my database is normalized to the third normal form?

I learned about normalization recently, and understand how important it is when implementing a new schema. How can I check if my database is 2NF or 3NF compliant ? Manual review is a sure option, ...
6
votes
2answers
354 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
4answers
553 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
5answers
6k 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 ...
6
votes
5answers
889 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
1answer
648 views

Is that a good idea to use one database for 50.000+ shops?

I know Shopify use only one database for all shops. But how can they handle their database with such big data? Is that a good idea to use one database for 50.000+ shops?