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

1
vote
1answer
260 views

Store data for multiple User Type

I am developing an app where four types of user share same module. So is it a good idea to create: one table and user_type and user_id (something like that) table prefixes basically 4 diff tables of ...
2
votes
0answers
53 views

Best Way to Handle Meta Information in a SQL Database [duplicate]

I've got a database where I want to store user information and user_meta information. The reason behind setting it up in this way was because the user_meta side may change over time and I would like ...
8
votes
4answers
553 views

What exactly does it mean that storing “large blobs in the database reduces performance”?

To someone who knows database internals this may be an easy question, but can someone explain in a clear way why storing large blobs (say 400 MB movies) in the database is supposed to decrease ...
10
votes
7answers
524 views

Nested Entities and calculation on leaf entity property - SQL or NoSQL approach

I am working on a hobby project called Menu/Recipe Management. This is how my entities and their relations look like. A Nutrient has properties Code and Value An Ingredient has a collection of ...
0
votes
3answers
190 views

Modeling a Student Application/Committee Relationship

I'm developing an ERD for a graduate student manager program (it's for a university class, so it's a fairly trivial implementation). In this snippet of the model, I'm trying to work out the ...
1
vote
1answer
52 views

How to validate (and group) User Listings in Marketplace

I am building a hobby marketplace (comic books). Users are able to list comic books they own for trading with other members. The problem I'm running into is that I would like to standardize the ...
18
votes
4answers
2k 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 ...
1
vote
1answer
269 views

Database designs, parallel tables, or extra fields?

I run a database for a sequencing centre. There are other developers getting data out of this and running automated and semi automated processes from the database. We are discussing a change to the ...
1
vote
1answer
427 views

Immutable Data Structure For Time Series Data

I'm wondering what the best data structure (for storing data on disk) is for storing immutable time-series data (99% of the data is truly immutable, the 1% is metadata that is separate from the ...
1
vote
1answer
188 views

Best practices when dealing with lots of empty table columns?

I have a schema that allows for multiple post types in my Django project (kinda like Tumblr). Users can make Posts of different types. One of those types is a photo, which I'll want the EXIF data for. ...
1
vote
3answers
180 views

Storing a drawn graph information in database for web application

Well while developing a web application that allow users to draw graphs (flow charts, ER diagram , UML, .... etc) the information of drawn items and their relation to each other and position on canvas ...
1
vote
2answers
184 views

Self reference using a new table vs concatenated list of ids

At my current workplace there is a common pattern in database design: they don't use foreign keys but they list all corresponding ids in a column like this: some_table id name image_ids 1 a ...
1
vote
2answers
302 views

What must I take into consideration when designing a UI around a 0..1:1 relationship?

I'm designing the database schema for a new product feature. In my current design I have some related optional data. Rather than have nullable fields I have a separate table with a 0..1:1 relationship ...
1
vote
2answers
260 views

Should model / table names be as generic as possible?

When naming a model, should the name be as generic as possible-- even if that won't be the name displayed to users? For example let's imagine a Django model for liking another user's posts. Does it ...
5
votes
2answers
104 views

Tracking changes to posts

I'm currently in the process of writing a support ticket system... Let's say it's a small forum application, or something like Uservoice. Now I want my users to be able to edit their tickets, but ...
2
votes
1answer
793 views

Status update & news feed database design / architecture

Imagine a application similar to Facebook, where you have status updates and a news feed. The names and profile pictures can be changed. There are also several different types of updates requiring a ...
1
vote
1answer
117 views

Advice on designing a robust program to handle a large library of meta-information & programs

So this might be overly vague, but here it is anyway I'm not really looking for a specific answer, but rather general design principles or direction towards resources that deal with problems like ...
5
votes
3answers
248 views

Is creating database index part of design, implementation or optimisation process?

There are a few questions around this subject: Is creating all indexes at the start (design) over engineering? In reality, I've added indexes after performance issues were found and we 'explain ...
11
votes
1answer
646 views

How much does the data model affect scalability and performance in so called “NoSQL” database?

You can't ever have a talk about so called "NoSQL" database without bringing the CAP theorem (Consistency, Availability, Partition : pick two). If you have to pick say, between MongoDB (Partition, ...
0
votes
2answers
262 views

Why should a column in a Relational Database have a single purpose?

Consider a table in which a column represents a monetary value in some cases and a link to the value of another record in other cases so that we have the following: TABLE 1 Id Product Composite 1 ...
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 ...
6
votes
4answers
422 views

Distance calculation and data normalization

I'm designing a system which needs to report basic distance between zip codes. It will use the Google Distance Matrix API but I want to cache the results in the database so that multiple requests for ...
1
vote
4answers
2k views

Storing application users in SQL: create a new “Users” table or use built-in database user management?

I am specifically interested in SQL Server, but the same question applies in general. When creating a new application, the way I see it, there are two options: Create a table called "Users" and ...
1
vote
2answers
146 views

Getting related foreign keys from parent entities

I'm thinking about a design issue which affects my project's data base. Supposing there are three diferent tables: CLIENT ORDER PACKING_SLIP Each order has its client and different packing ...
0
votes
1answer
271 views

Generic params table design

We have a generic parameter table whose important attributes are : id number auto increment not null domain varchar (200) not null classification varchar (200) not null param_name varchar (200) not ...
7
votes
2answers
421 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. ...
8
votes
7answers
969 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 ...
13
votes
4answers
567 views

Do these specific tables need surrogate keys?

Background I have this tables +-------------------------+ +------------------------+ |Airport | |Country | |-------------------------| |------------------------| ...
1
vote
2answers
551 views

External id for every relational table

Should I add an external id for every table? For example if I have these tables : Customer ======================================= Id Name Gender --------------------------------------- ...
3
votes
1answer
717 views

Database Schema for Pricing Products (packages, promotions, qty based, limited time offer…)

I'm working on a new point of sale for a company that products for different prices depending on the product mix. All products have a base price. To explain my problem, I will use the following ...
1
vote
1answer
886 views

Banking Applications - Saving Account Balance

From what I know, most banking applications periodically store the account balance at a certain point in time as it would not be plausible to calculate it over the entire history of transactions every ...
4
votes
1answer
197 views

Is there an industry standard for systems registered user permissions in terms of database model?

I developed many applications with registered user access for my enterprise clients. In many years I have changed my way of doing it, specially because I used many programming languages and database ...
0
votes
2answers
223 views

Full Text Search Strategy For My Website

I have a website that allows users to search for items in various categories. Each category is a separate area (page) of my website. For example, some categories might be cars, bikes, books etc. At ...
3
votes
1answer
161 views

Database Design - Optimise Relationships

I am about to embark on a redesign of an application, one where querying the database is particularly annoying. I intend to redesign the database as much as possible but the data shape cannot change ...
1
vote
1answer
71 views

SQL Table Design Modification based on data roles or implementation convenience?

I've got an existing SQL table structure that looks like this: Orders - order_id - organisation_id - [all the rest of the order data] Organisations - organisation_id - [all the rest of the ...
0
votes
1answer
144 views

Google Datastore and deep records

I'm in the beginning of creating a website that uses Google Datastore to store information related to clients and I'm curious if the record is too deep for NoSQL or if I'm fine. Essentially I'll be ...
1
vote
1answer
972 views

Mongodb array vs object

New with Mongodb, and I'm not sure what implications or benefits there are between the use of an array vs an object. So.. As above what are the implications and benefits between the use of arrays ...
0
votes
2answers
171 views

How to salvage a business model from a mismatched database?

There is an application that involves billing enterprise customers for the services their customers utilize. The database schema does not properly reflect the business model. For instance, on a given ...
4
votes
4answers
592 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 ...
3
votes
2answers
341 views

Single Responsibility principle VS KISS principle

Let's assume the modeling of User model in a context of a social network. User concept is composed of two notions: Authentication elements like userName/Password/Email etc... Extra data information ...
2
votes
4answers
577 views

How to genericize foreign key references in table?

I am trying to design a table to store references to multiple tables as a "target" of the item in each row. An example should clarify what I am trying to do: Imagine you have 4 tables (images, ...
2
votes
3answers
1k views

Is there a better way of handling access control logic instead of it being in the UI?

Through most of my dev experience, I've never had to deal with much variety of access control architectures. They've all been pretty straight forward: Group [Create, Update, Delete] - User 1 ...
0
votes
1answer
116 views

Keyword Collection, ManyToOne or OneToMany relationship?

I hope this is the right place for this type of problem. An Object of my class Movie can have several keywords ( like 'fight club', 'brad', 'fincher'), and be part of different categories (like ...
1
vote
1answer
3k views

How to model/structure data that contains parent/child relationships for the same entity?

I think it is just my thinking that is off here. Suppose I have two entities: question and choice. A question can be a parent or child, or both (in relation to other questions). It seems that this ...
2
votes
1answer
2k views

Representing a parent/child hierarchy in a Relational Database

I am working on an application that connects to another application (Quickbooks) using an API by sending/receiving XML requests/responses. I am able to work with the data in the XML response and load ...
3
votes
3answers
324 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 ...
8
votes
2answers
3k views

Handling subscriptions, balances and pricing plan changes [closed]

Preamble My aim is to create reusable code for multiple projects (and also publish it on github) to manage subscriptions. I know about stripe and recurring billing providers, but that's not what this ...
3
votes
2answers
234 views

Referencing countries via Foreign Key Constraint

To make things simple to explain: I've got two tables: Table1 and Countries. The Countries table contains a nice flag and some information, such as DisplayName, the ISO3166 ALPHA3 code and the phone ...
12
votes
3answers
2k views

General thought process for “How would you build this website/app” interview questions [closed]

I've collected a bunch of interview questions like "Describe how you would design a photo album application", "Describe how you would design this particular feature of this particular website" (e.g. ...
3
votes
2answers
2k views

What is the best practice for saving TimeZones in the database?

We want to start collecting timezones for each of our addresses in the database. What is the best practice to store the timezones? How would you go about acquiring timezones for the existing address ...