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

2
votes
1answer
61 views

what's the proper way to organize code that straddles multiple objects?

Let's say I have the following classes: person, widget, foo_bars. A person can have multiple widgets and foo_bars. When an admin deletes a person, I want my code to do a cascading delete... and ...
3
votes
1answer
165 views

What is the standard practice for extracting an auto-incrementing primary key?

I have some objects that will be created an entered into the database. There is no guarantee that any of their fields will be unique except for the auto-incrementing primary key. I would like to be ...
4
votes
4answers
376 views

eCommerce use case: removing a product

I am starting to design the database schema for the eCommerce element of a web service I am creating. The thing I'm trying to get my head around is how to deal with the use case of a seller (user of ...
2
votes
2answers
535 views

MongoDB: Is this a good schema design?

I'm working on a Meteor app which lets users create events and assign them crew members. I've played around with MongoDB before and made some experiences along the way, where for instance my first ...
4
votes
4answers
302 views

Typed search over complex model. Possible CQRS / NoSQL solution

Given the following relational model: I need to implement a typed search for hotels, which should be able to query on name, city, country, category, hoteltype, roomprice, customtype, room custom ...
1
vote
2answers
154 views

Should a DR & CR entry both be created or a single-entry accounting is fine

A new project has an amount. It is always related to a client. One client can have many projects. And he may pay in steps for a particular project. In order to keep track of client payments, I've ...
1
vote
1answer
144 views

Design Application to “Actively” Invite Users (pretend they have privileges)

I am designing an application where users message one another privately, and may send messages to any Entity in the database (an Entity may not have a user account yet, it is a professional database). ...
2
votes
1answer
129 views

How to handle when one user deletes the object while the other has it opened in edit mode in a webapp?

I am developing a CRUD type web application. It is a project management system having projects, milestones, tasks, employees etc. Each employee has his/her own account to login and view the system. ...
0
votes
1answer
109 views

How to store Status for Reservation table

I have a reservation table (a user fills out a form to request a reservation). It has two parts that need to be confirmed. isReservationAccepted (decline,accept,waiting) and hasReservationBeenSent ...
3
votes
1answer
1k views

Database design/relationship for threading messages

What's the database design or business logic for creating an app for messaging between users? I am having difficulties with choosing how to approach the relationship between each Conversation of User ...
2
votes
3answers
418 views

db design: circular references for the-one-of-many case

There are many questions about circular references around (if they are bad or not) but this one will be concrete. Consider the following case. There are two models Image and User. Users have ...
2
votes
3answers
308 views

Which database schema should I pick?

I am planning a database scheme and I hesitate between two designs, which one should I choose? For the purpose of the question, assume we want to prepare a database scheme for an application managing ...
2
votes
2answers
107 views

How should this relationship be structured in a relational database?

In a recent project I was asked to implement an events system. An Event had to have a Location which was originally specced out as simply a physical location with some optional extra notes. Then the ...
0
votes
1answer
87 views

Multiple schemas vs aditional column per customer

If you have different database per customer as there is no need to share any data between them does the same apply if a customer has multiple stores ? I believe that it is easier to do queries for ...
0
votes
2answers
412 views

Is storing stack traces in database recommended?

The current system I am working on goes through a set of documents & performs some logic using the metadata of the document. If the metadata of a document is fishy..it throws an exception. The ...
0
votes
1answer
170 views

How does this normalized example work?

I had the following DB schema: Customer Car Rental ------- ---- ------ Name Name Car_ID ID ID Customer_ID Date This is said to be non normalized ...
2
votes
2answers
341 views

How should I make searching a relational database more efficient? [duplicate]

This is in the scope of a web application. I have a database which has a few nested relations. There is a feature which depicts the history of a large chain of relations. It is essentially a data ...
0
votes
2answers
136 views

How should I structure my database to gain maximum efficiently in this scenario?

I'm developing a PHP script that analyzes the web traffic of my clients websites. By placing a link to a javascript on the clients website (think of Google Analyses), my script harvests information ...
4
votes
3answers
307 views

How to conciliate OOAD and Database Design?

Recently I've studied object oriented analysis and design and I liked a lot about it. In every place I've read people say that the idea is to start with the minimum set of requirements and go ...
2
votes
2answers
1k 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 ...
0
votes
1answer
147 views

Implementing new required feature after software release

Fake Scenario There is a software that was released 1 year ago. The software is to map and register all kind of animals on our planet. When the software was released, the client only needed to know ...
1
vote
1answer
180 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 ...
0
votes
1answer
202 views

Best way to implement database design for a set of entities

Lets say I have a scenario like so: I have many claims to get money from another entity and this claim has to go through two gateways. This claim is sent from an origin system and can possibly be ...
7
votes
4answers
499 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
375 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
164 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
50 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 ...
17
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
232 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
336 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
153 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
153 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
157 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
276 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
177 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
99 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
570 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
77 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
224 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 ...
10
votes
1answer
508 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
240 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 ...
4
votes
3answers
3k 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
300 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
1k 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
133 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
194 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
376 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
752 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
508 views

Do these specific tables need surrogate keys?

Background I have this tables +-------------------------+ +------------------------+ |Airport | |Country | |-------------------------| |------------------------| ...