For questions about structuring the data within a database. How to lay out tables, whether to use a relational DB or not, etc.

learn more… | top users | synonyms

90
votes
12answers
93k views

Is it a bad practice to store large files (10 MB) in a database? [closed]

I am currently creating a web application that allows users to store and share files, 1 MB - 10 MB in size. It seems to me that storing the files in a database will significantly slow down ...
62
votes
7answers
9k views

Code First vs. Database First

When I design and create the software I work on, I typically design and create the back-end SQL tables first and then move on to the actual programming. The project I'm currently working on has me ...
60
votes
10answers
6k views

Is it premature optimization to add database indices?

A colleague of mine today suggested that we go through all of the queries in our application and to add indices accordingly. I feel this is premature optimisation because our application is not even ...
48
votes
13answers
6k views

Is it necessary to create a database with as few tables as possible

Should we create a database structure with a minimum number of tables? Should it be designed in a way that everything stays in one place or is it okay to have more tables? Will it in anyway affect ...
47
votes
7answers
5k views

How can I argue convincingly against duplicating database columns?

I've started working at a new organization and one of the patterns I've been seeing in the database is duplicating fields to make writing queries easier for the business analysts. We're using Django ...
42
votes
8answers
6k views

Is the use of NoSQL Databases impractical for large datasets where you need to search by content?

I've been learning about NoSQL Databases for a week now. I really understand the advantages of NoSQL Databases and the many use cases they are great for. But often people write their articles as if ...
31
votes
5answers
4k views

No central database

I have a client that is looking to get a website/mobile apps/desktop apps built that deal with very sensitive data (more sensitive than bank/card details). Because of the sensitive nature of the data, ...
27
votes
5answers
12k views

Configuration data: single-row table vs. name-value-pair table

Let's say you write an application that can be configured by the user. For storing this "configuration data" into a database, two patterns are commonly used. The single-row table CompanyName | ...
26
votes
8answers
9k views

Front end first or Back end first. Of the two which is a Good system design pratice?

I have a client right now requiring me to develop a school enrollment system. Now this is the first time im having this kind of challenge. Most of the past software that i created are not that complex....
25
votes
13answers
2k views

How To Deal With Terrible Design Decisions [duplicate]

I'm a consultant at one company. There is another consultant who is a year older than me and has been here 3 months longer than I have, and a full time developer. The full-time developer is great. ...
25
votes
4answers
14k views

EAV - is it really bad in all scenarios?

I'm thinking to use EAV for some of the stuff in one of the projects, but all questions about it in stackoverflow end up to answers calling EAV an anti pattern. But I'm wondering, if is it that wrong ...
25
votes
9answers
4k views

How do you organize highly customized software?

I'm working on a large software project which is highly customized for various customers arround the world. This means that we have maybe 80% code which is common between the various customers, but ...
24
votes
6answers
1k views

Does the advent of the SSD have any implication for database optimization?

Today I was browsing through a book on SQL Server optimization and it seemed that a certain amount of the ideas were based on a linear model of storage. As SSDs have a completely different storage ...
23
votes
3answers
14k views

self referencing tables, good or bad? [closed]

Representing geographical locations within an application, the design of the underlying data model suggests two clear options (or maybe more?). One table with a self referencing parent_id column uk - ...
22
votes
6answers
14k views

Should I use one database per application or share a single database amongst multiple applications [closed]

I have multiple applications some that use data from the same sources. Is it best practice (or what are the pros/cons) to: leave the data in databases shared by multiple applications saves space ...
22
votes
1answer
12k views

When should you use a document vs relational vs graph database? [closed]

For the purposes of discussion let's consider a FourSquare scenario. Scenario Entities: Users Places Relationships: Checkins: users <-> places, many to many Friends: users <-> users, ...
21
votes
8answers
6k views

Favoring Immutability in Database Design

One of the items in Joshua Bloch's Effective Java is the notion that classes should allow mutation of instances as little as possible, and preferably not at all. Oftentimes, the data of an object is ...
20
votes
4answers
3k views

Best Practices for Redesigning a Database

I'm aware of some general best practices when designing a database for an application, but what about redesigning? I'm on a team tasked with re-designing an internal business application, though ...
19
votes
9answers
6k views

Best way to reference static database data in code?

Many applications include 'static data': data that doesn't really change during the lifetime of the application. For example, you might have a list of Sales Areas that is likely to be a fixed list for ...
18
votes
4answers
2k views

Why many designs ignore normalization in RDBMS?

I got to see many designs that normalization wasn't the first consideration in decision making phase. In many cases those designs included more than 30 columns, and the main approach was "to put ...
17
votes
9answers
2k views

Constraints in a relational databases - Why not remove them completely?

Is there any reason to build constraints between tables (inside SQLserver) nowadays? If so, when? Most applications in my area are built on object principles and tables are joined on demand. Demand ...
17
votes
7answers
732 views

De-facto standards for customer information record [closed]

I'm currently evaluating a potential new project that involves creating a DB for typical customer information (userid, pwd, first & last name, email, adress, telfnr ...). At this point, ...
16
votes
3answers
7k views

Should I store False as Null in a boolean database field?

Let's say you have an application that has a boolean field in its User table called Inactive. Is there anything inherently wrong with just storing false as null? If so can you please explain what the ...
16
votes
5answers
356 views

Should my multi-server RDBMS or my Application handle database Referential Integrity?

Should items like Foreign Keys, Constraints, Default Values, and so on be handled by the database management system (in this case, MS SQL 2005) or the application? I have heard opinions from both ...
15
votes
8answers
2k views

Handling deleted users - separate or same table?

The scenario is that I've got an expanding set of users, and as time goes by, users will cancel their accounts which we currently mark as 'deleted' (with a flag) in the same table. If users with the ...
15
votes
3answers
4k views

Decentralized Data Management - encapsulating databases into microservices [closed]

I've recently been taking a course on Software design and there was a recent discussion/recommendation about using a 'microservices' model where components of a service are separated into microservice ...
15
votes
4answers
3k views

Is it bad practice to allow user defined fields?

Generally speaking, is it considered bad practice to allow for user created fields in a database for a webapp? For example, I am making a home inventory webapp for my wife, and she is going to want ...
15
votes
2answers
7k views

What is the difference between collation and character set?

I have a general question about databases. We usually use the term collation with databases. I would like to know how its different from character set. I guess collation is a subset of character set. ...
14
votes
10answers
4k views

How do you approach database design? [closed]

I am primarily a web developer and I have a couple of personal projects which I want to kick off. One thing that is bugging me is database design. I have gone through in school db normalization and ...
14
votes
6answers
41k views

what do database programmers do?

Every time I read about Oracle programmers etc, I get confused. I don't know what exactly they do. From my understanding, application programmers need to develop the core functionality. The libraries ...
14
votes
8answers
1k views

Are there examples of non CRUD approaches?

I'm a programmer but also have worked as an archivist. As archivist it's a lot about keeping data. I often get into arguments with colleagues when it comes to operations on data. I don't like the U ...
14
votes
7answers
13k views

Best practices to follow with database indexes [closed]

What are some DOs and DONTs for improving database performance using index? A DO would be a case in which an index should be created, or another indexes related tip that will improve performance. A ...
14
votes
7answers
3k views

What special considerations are needed when designing databases to hold financial records?

I hope this question isn't too broad. In the future I may need to add some accounting and financial-tracking systems to some applications (mostly web-based applications, but my questions pertains to ...
13
votes
3answers
4k views

Database schema for a ToDo list

I am trying to make a very simple todo list application with PHP, MySQL, Jquery templating and JSON... However, my schema seems to complicate things in JSON. What's the best way to do it? A new ...
13
votes
5answers
1k views

Is it the job of programmers to design the database?

I've been a programmer for the past six years. Throughout my career, I have worked on many web applications. Most of the time, when a database was needed, it was given to us (the programmers) or we ...
13
votes
9answers
15k views

Should I use multiple column primary keys or add a new column?

My current database design makes use of a multiple column primary key to use existing data (that would be unique anyway) instead of creating an additional column assigning each entry an arbitrary key. ...
13
votes
4answers
15k views

Multi-tenancy - single database vs multiple database

We have a number of clients, whose systems share some functionality, but also have quite a degree of diversity. The number of clients is growing - always a healthy thing! - and the diversity between ...
13
votes
2answers
944 views

Is it wasteful to create a new database table instead of using enum data type?

Suppose I have 4 types of services I offer (they are unlikely to change often): Testing Design Programming Other Suppose I have 60-80 of actual services that each fall into one of the above ...
12
votes
6answers
2k views

In plain English, what is a database index most similar to? [duplicate]

Setup: Suppose you are teaching an introduction to Databases class, the students are CS students that have a working knowledge of tree structures, how they can speed up searches, and have probably ...
12
votes
2answers
2k views

Why store flags/enums in a database as strings instead of integers?

I've been browsing SQL dumps of some famous CMSes, including Drupal 7, Wordpress (some quite very old version), and some custom application based on Python. All of these dumps contained data with ...
12
votes
3answers
2k views

General thought process for “How would you build this website/app” interview questions [closed]

I've collected a bunch of interview questions like "Describe how you would design a photo album application", "Describe how you would design this particular feature of this particular website" (e.g. ...
12
votes
4answers
616 views

Do these specific tables need surrogate keys?

Background I have this tables +-------------------------+ +------------------------+ |Airport | |Country | |-------------------------| |------------------------| |...
12
votes
5answers
4k views

Is there any reason to use VARCHAR sizes rounded to a 128/256/4096 byte offset?

In database schema's, I often notice VARCHAR sizes are rounded to the byte offsets 128/256 or 4096. I've done it before as well, and the idea behind it was probably something with efficiency. However,...
12
votes
2answers
7k views

Why NoSQL over SQL? [duplicate]

Possible Duplicate: When would someone use MongoDB (or similar) over traditional RDMS? How well the SQL and NoSQL go head-to-head. I read somewhere that SQL databases are not well for data ...
12
votes
8answers
7k views

How should repetitive calendar tasks be stored in the database?

This is for a small personal project for micro-management. Basically, I store tasks in a SQLite3 database that looks like this: id INTEGER PRIMARY KEY AUTOINCREMENT label TEXT deadline ...
12
votes
1answer
848 views

How much does the data model affect scalability and performance in so called “NoSQL” database?

You can't ever have a talk about so called "NoSQL" database without bringing the CAP theorem (Consistency, Availability, Partition : pick two). If you have to pick say, between MongoDB (Partition, ...
11
votes
10answers
960 views

How could RDBMSes be considered a fad?

Completing my Computing A-level in 2003 and getting a degree in Computing in 2007, and learning my trade in a company with a lot of SQL usage, I was brought up on the idea of Relational Databases ...
11
votes
5answers
3k views

Agile methods and Databases at the start of the project

New to agile and I'm not sure how to start. The idea is to create small parts of the project in sprints. However the project I'm working of requires a database and the database must be almost ...
11
votes
5answers
840 views

Version controlling a database's contents

I'm working on a web project that involves user-editable content, and I'd like to be able to do version-tracking of the actual content, which lives in a database. Basically, I want to implement wiki-...
11
votes
3answers
541 views

When accessing/manipulating complex data, is it better to store it in many small pieces or one large chunk?

I am building a web app that manipulates fairly complex data: guitar tabs. As a reference, guitar tabs look like this: Eb|-------------------------------------------------------------------------|...