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

53
votes
12answers
32k 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 ...
42
votes
12answers
3k 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 ...
23
votes
10answers
2k 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 ...
22
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. ...
20
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 ...
19
votes
6answers
4k 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 ...
19
votes
4answers
2k 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 ...
17
votes
6answers
5k 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 ...
16
votes
9answers
3k 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 ...
16
votes
7answers
666 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, ...
15
votes
9answers
1k 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 ...
15
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 ...
15
votes
6answers
726 views

What do DBAs do?

Yes, I know they administrate databases. I asked this question because I'd like to get a further insight into the kind of day-to-day duties a DBA might perform, and the real-world business problems ...
14
votes
8answers
2k 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 ...
14
votes
2answers
4k views

self referencing tables, good or bad?

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 - ...
14
votes
5answers
319 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 ...
14
votes
1answer
6k 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, ...
13
votes
10answers
3k 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 ...
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
7answers
2k 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
4answers
736 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 ...
13
votes
4answers
474 views

Do these specific tables need surrogate keys?

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

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

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
3answers
1k views

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

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
9answers
7k 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. ...
11
votes
10answers
897 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
8answers
1k 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 ...
11
votes
3answers
513 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: ...
11
votes
2answers
337 views

What are the advantages of separating 'result' from 'status'

Let's say you have some automated processes that generally go through the following states; scheduled - initiated - validating - executing - completed On top of that these processes can prematurely ...
11
votes
4answers
310 views

Can I use the database structure of an open source project?

I found a database structure for a CMS system and I want to copy it with the EF that this database is done in, is it OK to copy the database structure of open source software which is under GNU v2 ...
10
votes
3answers
2k 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 ...
10
votes
4answers
4k 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 ...
10
votes
7answers
787 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 ...
10
votes
7answers
281 views

Nested Entities and calculation on leaf entity property - SQL or NoSQL approach

I am working on a hobby project called Menu/Recipe Management. This is how my entities and their relations look like. A Nutrient has properties Code and Value An Ingredient has a collection of ...
10
votes
4answers
2k views

Weaknesses with different types of NoSQL databases

Here's my question: What are the weaknesses with different types of NoSQL databases? Specifically, what're the weaknesses of key-value stores, graph data stores and document stores? I've had an easy ...
10
votes
5answers
2k 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. ...
10
votes
1answer
443 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, ...
10
votes
8answers
3k 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 ...
10
votes
1answer
321 views

Preferring Database Normalization vs. Schema Transparency?

A new requirement has surfaced on an old codebase, that basically enables direct (internal) communication between two formerly not-directly related classes of users (stored in different tables with ...
9
votes
6answers
21k 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 ...
9
votes
5answers
4k 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 | ...
9
votes
2answers
2k 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. ...
8
votes
11answers
14k views

Using XML as data storage

I was thinking about the XML format and the following quote: “XML is not a database. It was never meant to be a database. It is never going to be a database. Relational databases are proven ...
8
votes
7answers
701 views

Design Parts DB

I'm developing a tool that handles (electrical) parts. The parts can be created, viewed, modified, deleted, grouped and so on... In order to make this question useful for future visitors I like to ...
8
votes
7answers
2k views

Copying a competitor's database schema? [closed]

I am going to be releasing some software soon which will require users to run a local database. There is a competitor in the space that is doing the same thing and they have a pretty sophisticated ...
8
votes
2answers
367 views

Is there a canonical source supporting “all-surrogates”?

Background The "all-PK-must-be-surrogates" approach is not present in Codd's Relational Model or any SQL Standard (ANSI, ISO or other). Canonical books seems to elude this restrictions too. ...
8
votes
2answers
2k views

Should we do entity-relationship modeling or object-oriented modeling first?

When building an application from scratch, should I start with the object-oriented (OO) model or the entity-relationship (ER) model?
8
votes
7answers
649 views

Should a surrogate key ever be exposed to a user?

Often in a table that has no natural key, it is still useful for users to be able to have a uniquely generated identifier. If the table has a surrogate primary key (and in such a case you would ...
8
votes
2answers
317 views

When is cloud database right for me?

Some say that DBaaS (database as a service) -- aka cloud database -- is not suitable for Business Intelligence (BI), analytics (OLAP) or archiving. Is this true? More generally, when DBaaS is the ...
8
votes
3answers
1k views

Storing n-gram data

(hopefully I landed on the right stack exchange site) I was hoping to brainstorm a little bit on the subject of storing n-gram data. In my project, I am trying to solve linguistic problems where I ...