Questions tagged [database-design]

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

Filter by
Sorted by
Tagged with
11 votes
3 answers
22k 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 ...
span's user avatar
  • 415
29 votes
5 answers
28k 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 ...
RichardW1001's user avatar
87 votes
7 answers
64k views

EAV - is it really bad in all scenarios?

I'm thinking to use an entity-attribute-value (EAV) model for some of the stuff in one of the projects, but all questions about it in Stack Overflow end up to answers calling EAV an anti-pattern. But ...
Giedrius's user avatar
  • 1,314
13 votes
1 answer
9k 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 ...
Antonio2011a's user avatar
  • 1,209
9 votes
2 answers
737 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. Oracle'...
Tulains Córdova's user avatar
37 votes
6 answers
49k 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 as ...
Laz's user avatar
  • 761
14 votes
9 answers
5k 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 ...
psr's user avatar
  • 12.9k
131 votes
10 answers
65k views

Storing a re-orderable list in a database

I'm working on a wishlist system, where users can add items to their various wishlists, and I plan to allow users to re-order the items later on. I am not really sure about the best way to go about ...
Tom Brunoli's user avatar
  • 1,413
54 votes
7 answers
3k views

What happened to database constraints?

When I review database models for RDBMS, I'm usually surprised to find little to no constraints (aside PK/FK). For instance, percentage is often stored in a column of type int (while tinyint would be ...
Arseni Mourzenko's user avatar
53 votes
13 answers
15k 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 ...
Shaheer's user avatar
  • 889
32 votes
9 answers
8k 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 ...
aKzenT's user avatar
  • 457
9 votes
2 answers
15k 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 ...
gage's user avatar
  • 91
4 votes
2 answers
3k 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 ...
JPCF's user avatar
  • 182
96 votes
8 answers
107k views

Why would you store an enum in DB?

I've seen a number of questions, like this, asking for advice on how to store enums in DB. But I wonder why would you do that. So let's say that I have an entity Person with a gender field, and a ...
user3748908's user avatar
  • 1,627
81 votes
7 answers
20k 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 ...
RubberDuck's user avatar
  • 8,961
43 votes
3 answers
48k 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 - ...
NimChimpsky's user avatar
  • 4,627
24 votes
3 answers
7k 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 ...
ThinkBonobo's user avatar
16 votes
5 answers
425 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 ...
Rachel's user avatar
  • 24k
16 votes
4 answers
30k views

Is storing a list of strings in single database field a bad idea? Why?

Recently, I started to work on some legacy system. People that developed it, came to an idea to store list of strings in single field of database table. Lets say that it is an identifier for object ...
mpasko256's user avatar
  • 336
15 votes
2 answers
12k 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 that ...
c0da's user avatar
  • 1,526
14 votes
4 answers
10k 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. ...
GSto's user avatar
  • 8,531
7 votes
2 answers
2k views

Database design - maintaining versions of an object

Suppose I've a Person object. This Person object in turn is having name, address, accountNumber and many other fields. Now my requirement is that I want to maintain multiple version for each person ...
Lavish Kothari's user avatar
6 votes
3 answers
264 views

Pattern for group of entities with one required primary member

Given a parent entity with a collection of child entities where there must be exactly one primary child of the group? To make the question more concrete, I've seen a number of ways that this pattern ...
g .'s user avatar
  • 707
5 votes
4 answers
4k 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 ...
amphibient's user avatar
  • 1,591
2 votes
5 answers
8k views

Best approach for an online store which changes its product’s price over time? [Designing the DB Table]

I am currently creating a website which will allow people to buy consumer products (deodorant, shampoo, toothpastes, etc…) from a store. Those products tend to change price and other details twice per ...
Jose A's user avatar
  • 295
2 votes
3 answers
1k 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 ...
trejder's user avatar
  • 2,386
2 votes
1 answer
3k views

How to handle db authentication in multi tenant application with separate databases?

In a multi tenant application with separated databases for each tenant, what would be the best way to authenticate connection to each of these separate databases. My goal is to: Main security ...
Starx's user avatar
  • 279
244 votes
13 answers
269k views

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

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 ...
B Seven's user avatar
  • 3,125
68 votes
4 answers
39k 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 ...
Dennis's user avatar
  • 8,217
64 votes
10 answers
36k views

When would you use a long, string ID instead of a simple integer? [closed]

I'd like to use Youtube as an example: they use IDs in the form of PEckzwggd78. Why don't they use simple integers? Or imgur.com - they also use IDs such as 9b6tMZS for images and galleries. Not ...
Rakori's user avatar
  • 787
46 votes
2 answers
26k 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 ...
trejder's user avatar
  • 2,386
39 votes
2 answers
34k views

Do multi-tenant DBs have multiple databases or shared tables?

Is a multi-tenant database: A DB server that has a different (identical) database/schema for each customer/tenant?; or A DB server that has a database/schema where customers/tenants share records ...
smeeb's user avatar
  • 4,870
37 votes
8 answers
39k views

What's a universal way to store a geographical address/location in a database? [closed]

What's a correct format of a geographical address/location which is a good fit for any address on the Earth? At the moment I have: country city street number text data (for simplicity) zip lat/lng ...
Xwaro's user avatar
  • 411
33 votes
8 answers
29k 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....
drexsien's user avatar
  • 441
32 votes
12 answers
5k 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. ...
splatto's user avatar
  • 422
31 votes
4 answers
9k 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 ...
Yosi Dahari's user avatar
29 votes
4 answers
8k views

Is there an industry standard for gender model other than male and female?

I am modeling a database that should be used as generic non-functional requisite for all services of the startup company, like persons, users, services and commercial data like coupons, signature ...
NaN's user avatar
  • 549
23 votes
8 answers
26k views

How would you design a user database with custom fields

This question is around how should I design a database, it can be relational / nosql databases, depending on what will be the better solution Given a requirement where you'll need to create a system ...
noobcser's user avatar
  • 331
21 votes
6 answers
13k 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-...
Fake Name's user avatar
  • 464
20 votes
9 answers
3k 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 ...
Independent's user avatar
16 votes
4 answers
21k views

How should I represent an enumerated type in a relational database?

I am working on developing a relational database that tracks transactions that occur on a device I'm working on for my company. There are different types of transactions that could occur on the device,...
c.hughes's user avatar
  • 486
16 votes
5 answers
3k 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 ...
ajm's user avatar
  • 1,255
14 votes
6 answers
13k views

Is it a bad practice to have a "record status" column in a database table?

I have to clarify first that the status column is not intended to reflect the status of a real-world item represented by the record (row) in the table. Rather, it is intended to show the status of the ...
ADTC's user avatar
  • 679
14 votes
6 answers
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 ...
wfoster's user avatar
  • 483
14 votes
8 answers
3k 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 ...
Pieter B's user avatar
  • 13.2k
13 votes
12 answers
64k 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 ...
Kian's user avatar
  • 315
13 votes
4 answers
1k views

Do these specific tables need surrogate keys?

Background I have this tables +-------------------------+ +------------------------+ |Airport | |Country | |-------------------------| |------------------------| |...
Tulains Córdova's user avatar
12 votes
5 answers
7k views

Is it an anti-pattern to create ORM entities based on existing database schema?

I maintain a large legacy app working with SQL database. The app uses raw SQL queries to query the database. Together with app rewrite I plan to introduce ORM to work with the database. The simplest ...
user14967413's user avatar
12 votes
2 answers
22k views

Creating a separate table for images or adding "image fields" to many tables?

I need to create a database in which several tables have images. For example, users have profile pictures and uploaded ones and products have many pictures, as well. Is it better to have one SQL table ...
Computer's Guy's user avatar
11 votes
2 answers
4k 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 ...
user34401's user avatar
  • 751