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

25
votes
4answers
13k 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 ...
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 ...
8
votes
1answer
4k 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 ...
10
votes
12answers
38k views

Using XML as data storage [closed]

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 ...
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 ...
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 ...
22
votes
6answers
13k 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 ...
4
votes
2answers
956 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 ...
4
votes
5answers
1k 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
446 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. ...
86
votes
12answers
90k 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 ...
61
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 ...
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 ...
10
votes
4answers
4k 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 ...
6
votes
3answers
7k 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 ...
13
votes
6answers
40k 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 ...
16
votes
5answers
355 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 ...
4
votes
2answers
4k 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 ...
3
votes
4answers
836 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
194 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 ...
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 ...
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 ...
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 ...
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. ...
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 ...
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 ...
4
votes
3answers
3k views

Designing a fitness / weight lifiting routine database

I'd like to create an app similar to Barbell Pro for Android, for practice / interest / educational purposes really. Or even as another example for database purposes, Fitocracy The problem is, I ...
11
votes
10answers
954 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 ...
9
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
1k 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 ...
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 - ...
9
votes
4answers
5k 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
756 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
3k 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
660 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?
13
votes
2answers
866 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 ...
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 ...
12
votes
2answers
1k 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
4answers
606 views

Do these specific tables need surrogate keys?

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

Designing a database with circular references for the-one-of-many case

Consider the following case: there are two models Image and User. Users have associated images and they can pick one of them as their profile image. There are essentially two ways of modeling this: ...
3
votes
2answers
109 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
662 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 ...
0
votes
1answer
4k views

Practical mysql schema advice for eCommerce store - Products & Attributes

I am currently planning my first eCommerce application (mySQL & Laravel Framework). I have various products, which all have different attributes. Describing products very simply, Some will have a ...
5
votes
2answers
259 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 ...
4
votes
1answer
517 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 ...
4
votes
3answers
532 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 ...
4
votes
3answers
395 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 ...