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
3answers
21k 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 ...
27
votes
5answers
26k 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 ...
13
votes
1answer
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
2answers
681 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'...
77
votes
6answers
55k 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 ...
36
votes
6answers
45k 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 ...
13
votes
9answers
4k 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 ...
102
votes
10answers
47k 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 ...
31
votes
9answers
7k 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 ...
53
votes
13answers
14k 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 ...
54
votes
7answers
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 ...
8
votes
2answers
14k 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
2answers
2k 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 ...
88
votes
8answers
87k 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 ...
79
votes
7answers
19k 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 ...
24
votes
3answers
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 ...
39
votes
3answers
45k 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 - ...
14
votes
2answers
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 ...
13
votes
4answers
9k 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. ...
6
votes
3answers
234 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 ...
1
vote
5answers
7k 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 ...
5
votes
4answers
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 ...
15
votes
4answers
23k 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 ...
2
votes
1answer
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 ...
16
votes
5answers
420 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 ...
2
votes
3answers
659 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 ...
231
votes
13answers
246k 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 ...
61
votes
4answers
30k 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 ...
62
votes
10answers
30k 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 ...
34
votes
8answers
31k 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
8answers
26k 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....
37
votes
2answers
21k 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 ...
31
votes
12answers
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. ...
23
votes
8answers
22k 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 ...
36
votes
2answers
29k 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 ...
28
votes
4answers
8k 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
4answers
7k 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 ...
20
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 ...
13
votes
12answers
63k 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 ...
4
votes
1answer
9k 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 ...
16
votes
4answers
15k 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,...
9
votes
2answers
20k 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 ...
5
votes
4answers
15k 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 ...
10
votes
4answers
6k 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
2answers
2k views

Encrypting stored data for multiple unique users to access information

Developing a database for many users, and thousands more "people" in the database, one section requires we encrypt the personal data - Youth's information, medical information, etc. We have users who ...
14
votes
8answers
2k 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 ...
14
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 ...
14
votes
6answers
11k 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 ...
9
votes
6answers
2k views

Is the "One Description Table to rule them all" approach 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 (...
8
votes
3answers
806 views

How to share a code base among 10+ projects and minimize pain?

I have a number of applications that share the same data in the same database. To try and minimize redundancy of code the Data Access Layer is a shared project. This prevents each project from needing ...