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

7
votes
1answer
3k views

Database Application Design Best Practices [duplicate]

Once upon a time it was common practice for both the business logic and database logic to be written in the same language (eg PL/SQL, Transact SQL, etc), more recently the practice is to separate the ...
19
votes
4answers
9k 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 ...
45
votes
13answers
5k 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 ...
11
votes
4answers
10k 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 ...
10
votes
12answers
28k 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 ...
4
votes
2answers
845 views

How to design a website with search capabilities?

Suppose you want to create a web page with some search capabilities over a database. Search has to be restricted both on some categorization of data and open text given by the users. Functional and ...
19
votes
6answers
9k 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 ...
9
votes
2answers
6k 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 ...
3
votes
5answers
865 views

Good techniques to speed up database execution

I have an ASP.Net application that is using a MySQL database. My queries are not executing as fast as I would like them too. Are there some standard ways to increase the speed of a MySQL database ...
7
votes
2answers
424 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. ...
56
votes
7answers
5k 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 ...
10
votes
4answers
3k 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 ...
9
votes
6answers
31k 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 ...
5
votes
3answers
4k views

Storing menu items with user permissions

I'm creating a menu system in PHP and MySQL. I will have several different menus and each menu will have a set of menu items connected to it. On the site, I also have different user permissions, some ...
4
votes
2answers
2k views

Database model for keeping track of likes/shares/comments on blog posts over time

My goal is to keep track of the popular posts on different blog sites based on social network activity at any given time. The goal is not to simply get the most popular now, but instead find posts ...
15
votes
5answers
344 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 ...
72
votes
12answers
68k 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 ...
24
votes
9answers
3k 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 ...
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 ...
25
votes
8answers
6k 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 ...
16
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 ...
23
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. ...
15
votes
6answers
760 views

What do DBAs do? [closed]

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 ...
12
votes
6answers
1k 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 ...
11
votes
10answers
933 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 ...
8
votes
7answers
1k 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 ...
7
votes
6answers
981 views

Is the “One Description Table to rule them all” approch good?

Long ago, I worked (as a client) with a software which use a centralized table for it's codified element. Here, as far as I remember, how the table look like : Table_Name (PK) Field_Name (PK) Code ...
9
votes
4answers
4k views

Design Patterns / Strategies For Custom Fields & Data Types

Are there any common strategies or design patterns for designing applications that have either the ability to add custom fields to data objects, or for creating your own custom definition of objects. ...
8
votes
7answers
743 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 ...
7
votes
3answers
1k views

How to separate sensitive data in database(MySql)

I need to design a database that will contains information about personal disease of users. What can be the approach in order to implement the columns of the DB's tables: encrypt the information, ...
4
votes
4answers
600 views

In an online questionnaire, what is a best way to design a database for keeping track of users all attempts?

We have a web app where users can take online exams. Exam admin will create a questionnaire. A questionnaire can have many Questions. Each question is a multiple choice question (MCQ). Let's say ...
2
votes
1answer
2k views

How does Facebook calculate mutual friends?

How does Facebook calculate mutual friends? Does it cache all mutual friends for each user? Does it use MySQL to calculate mutual friends with a query?
22
votes
2answers
9k 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 - ...
13
votes
4answers
580 views

Do these specific tables need surrogate keys?

Background I have this tables +-------------------------+ +------------------------+ |Airport | |Country | |-------------------------| |------------------------| ...
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 ...
5
votes
5answers
622 views

Should I use a relational datamodel or Cassandra with ColumnFamily based indexes?

Currently we are working on some storage problems for log data from various servers and communication message logs (HTTP(S), XMPP). There will be many write operations and for read operations we will ...
3
votes
2answers
105 views

Database Design for Inheritance

In my project I have an object Player (attr example: pl_id, pl_data1) that is one of these types: Its GoalKeeper (attr example: gk_data1) OR Defender (attr example: df_data1) OR Forward (attr ...
2
votes
1answer
102 views

What is the best way to store a formula in a database for a table driven formula? [closed]

So I am changing my code over to a table driven formula system to make it sustainable in the long term. So basically I have 5 foreign keys that will get me to a single formula to use. However I'm ...
11
votes
2answers
568 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 ...
5
votes
2answers
220 views

Storing stop/start points in a database

Let's say I am storing start and stop points per user into a database table. For example... let's say in a chat system, a user only needs to see lines 24-293, and 500-512. (Let's say he logged off ...
3
votes
3answers
472 views

Is it better to use a switch statement or database to look through 5,000 to 10,000 instances?

I have some JSON data in this format (5,000 instances for now): {"id":"123456","icon":"icon.png","caseName":"name of case"} I am allowing the user to search for the name of the case and then return ...
3
votes
3answers
332 views

Design pattern to dynamically create patterns found in a list of links

Can anyone help me think of a way to dynamically generate the patterns section of a tool I am creating? I'm not sure how to store and generate these "patterns" dynamically. What the program does is ...
3
votes
4answers
598 views

(Dis)advantages of datetime vs. long in globally used applications [closed]

I am currently designing a web/mobile application the bulk of whose users will be distributed across the different U.S. time zones with a potential of scaling up to other countries and time zones as ...
2
votes
3answers
128 views

At which point using string-based enums can become a real problem?

This is a direct follower to this question. An answer given to that questions says (my understanding), that nowadays using string-based enums instead of integers isn't a much problem, because hosting ...
2
votes
3answers
346 views

What are the advantages/disadvantages of creating a new set of tables for each user?

I'm working/playing on an app where individual users' data will be completely independent from each other. Of course, there will be common tables for other parts of the app (e.g. "users"), but for the ...
2
votes
1answer
340 views

Eventual consistency in Banking systems

I keep hearing people say Banking systems like ATM use Eventual consistency model for data synchronization, but I am wondering if it is true. Definitely the amount you withdraw might be delayed ...
2
votes
3answers
576 views

db design: circular references for the-one-of-many case

There are many questions about circular references around (if they are bad or not) but this one will be concrete. Consider the following case. There are two models Image and User. Users have ...
2
votes
3answers
1k views

Naming convention for associative entity

When designing Many-to-Many relationship on a database, we need to put another table in the middle so it become 3 tables with 1-to-many relationship. Wikipedia told me that it's called associative ...
1
vote
2answers
145 views

One Enum vs Multiple Enums

I am developing an application where a user submits a mission and other users accept the mission. Pretty simple. I want to keep a track of the mission progress status and store it into a database. ...