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.
100
questions
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 ...
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 ...
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 ...
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 ...
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'...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 - ...
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 ...
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 ...
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 ...
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 ...
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. ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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
...
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....
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. ...
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 ...
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 ...
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 ...
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-...
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 ...
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,...
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 ...
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 ...
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 ...
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 ...
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 ...
13
votes
4
answers
1k
views
Do these specific tables need surrogate keys?
Background
I have this tables
+-------------------------+ +------------------------+
|Airport | |Country |
|-------------------------| |------------------------|
|...
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 ...
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 ...
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 ...