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

5
votes
4answers
6k views

Best approach for a database of long strings

I need to store questions and answers in a database. The questions will be one to two sentences, but the answers will be long, at least a paragraph, likely more. The only way I know about to do this ...
2
votes
2answers
1k views

What's the best way to modularize a User schema so it's generic

I have a database design question. Basically I want to be able to create a schema for a User model, then use this User model in other models that extend User but I want to design it in such a way that ...
8
votes
2answers
3k views

Should we do entity-relationship modeling or object-oriented modeling first?

When building an application from scratch, should I start with the object-oriented (OO) model or the entity-relationship (ER) model?
2
votes
2answers
1k views

Storing translation data as JSON column

We're deciding on how to store translations of some descriptions of database items. We could go the traditional way and keep a translations table (and a language table and an object_translation ...
2
votes
2answers
120 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
3answers
299 views

Are there cases where it is a good idea to disallow remote access to a database?

My coworker is working on a project and decided to have the database subside entirely on the glassfish server with glassfish being the only thing that can directly access the database. He will then ...
11
votes
2answers
367 views

What are the advantages of separating 'result' from 'status'

Let's say you have some automated processes that generally go through the following states; scheduled - initiated - validating - executing - completed On top of that these processes can prematurely ...
5
votes
4answers
306 views

Putting audit functionality into the database

Our database does not have any audit functionality. It does not records who inserts the record or who who changes it It does not keep a history of the changes made Nothing can be restored if ...
1
vote
2answers
131 views

In which format should I sent the values in database in PHP?

I am developing an application which is based on the quiz contest for competetive exams. The user can give different categories of tests, which will contain different types of questions of following ...
1
vote
2answers
181 views

Using a column to store the company or a separate database?

I am writing an system for the company to put their promotion material on the site, which is like some kinds of CMS. So, I need a database that store their data, but I am considering how to store ...
9
votes
8answers
5k views

How to store record statuses (like pending, complete, draft, cancelled…)

Quite a lot of applications require records in their tables to have a status, such as 'complete', 'draft', 'cancelled'. What's the best way of storing these statuses? To illustrate what i'm getting at ...
5
votes
1answer
731 views

Are XML Schemas bad for constantly evolving file formats?

I'm struggling with a client-server project where I have Java apps out on the Internet that store data to a backend server. The format of this data is well-defined, but the project is constantly ...
1
vote
1answer
226 views

Is using dynamically generated code as cache a good idea?

I have a web search interface that can compare products in a table. This data set changes a few times a week. I have been storing a "DISTINCT" list (used for parametric selection) in a cache table. ...
16
votes
3answers
7k views

Should I store False as Null in a boolean database field?

Let's say you have an application that has a boolean field in its User table called Inactive. Is there anything inherently wrong with just storing false as null? If so can you please explain what the ...
8
votes
1answer
521 views

How do I efficiently store all OpenStreetMap data in an indexed way?

I have a PBF file that contains the following information about a country: Nodes, each with their own longitude, latitude and properties; used to store points in a 2D space. Ways, each with their ...
3
votes
5answers
726 views

Using XML field Vs. creating a table for unstable organization

I am in the middle of design an application to issue and store invoices for an organization. The problem is the organization is not stable at all. There are many types of invoices and they may ...
2
votes
1answer
399 views

Is it bad practice to store user input in the same table where the admin stores data?

If in a web app, let's say an app that has a table with stored street address (Strings), the admin of the app will be adding data often to grow his archive. The table (MySQL) has a primary key with ...
2
votes
3answers
309 views

Standard semantic name for 3rd table in 3NF

For 3 tables that are in 3NF, a many-to-many relationship, is there a standard name or term to describe the 3rd table, the one that associates the other tables? Basically, I'm looking for a semantic ...
2
votes
2answers
381 views

Factory design pattern - getting the types from a table

I've implemented a generic factory as described here I need to register elements to the factory hard-code like this: Factory.Register(1, typeof(MyClass)); this factory will have to be correlated ...
12
votes
8answers
7k views

How should repetitive calendar tasks be stored in the database?

This is for a small personal project for micro-management. Basically, I store tasks in a SQLite3 database that looks like this: id INTEGER PRIMARY KEY AUTOINCREMENT label TEXT deadline ...
11
votes
4answers
376 views

Can I use the database structure of an open source project?

I found a database structure for a CMS system and I want to copy it with the EF that this database is done in, is it OK to copy the database structure of open source software which is under GNU v2 ...
3
votes
2answers
868 views

Is there a design pattern for modelling job execution flow?

In my application I have a set of jobs to execute: Each job goes through the states "not started", "started", "completed", "failed" etc. Each job has a set of pre-conditions and post-conditions. ...
3
votes
1answer
401 views

What are the advantages and disadvantages of storing the number of comments in an integer column vs. calculating it on the fly?

Let's take CodeReview page as an example. If you go here, you will see the list of the questions with their vote numbers and comment numbers Now, I am wondering if these numbers are calculated on the ...
3
votes
1answer
169 views

Who's responsible for a master / slave switch?

We are introducing a master / slave replication schema in our web application. At certain scenarios we would prefer to explicitly direct reads to a slave and writes to the master, which to me is an ...
20
votes
4answers
3k views

Best Practices for Redesigning a Database

I'm aware of some general best practices when designing a database for an application, but what about redesigning? I'm on a team tasked with re-designing an internal business application, though ...
1
vote
1answer
264 views

What's the best way to design a data and php model for a multi-account system?

I'm doing some high-level sketching of a system that will require users to register, when they do they get an account and can invite others to that account. To be clear, an account would be similar ...
3
votes
2answers
397 views

two approaches to database modeling - which one to use when

recently I have noticed a new approach to data modeling vs what I have used so far. This seems to be a way to address modeling of an entity that has a very large set of fields and quite often many of ...
2
votes
5answers
858 views

Worst practices in developing database applications [closed]

In constrast to http://programmers.stackexchange.com/questions/123591/database-design-good-practices I wanted to ask: What are the top worst practises you encountered when having to work with other ...
2
votes
1answer
873 views

Querying key/value store

I don't know whether this question is reasonably answerable (and therefore will be closed), but I will take my chances: What are the possible problems (and solutions), one might encounter, when ...
7
votes
4answers
2k views

Add an ID in every table even if it's not needed?

Is this good or bad design to add, by default, ID field in every table in a database, even when you don't see currently to usage of this ID (For instance in a MxN table) ?
12
votes
5answers
4k views

Is there any reason to use VARCHAR sizes rounded to a 128/256/4096 byte offset?

In database schema's, I often notice VARCHAR sizes are rounded to the byte offsets 128/256 or 4096. I've done it before as well, and the idea behind it was probably something with efficiency. However,...
1
vote
2answers
548 views

Database design

I'm on the way developing an application which requires a kind of dynamic database, So this is what I want, This is the for reading the details of a class, the number of variables and methods ...
3
votes
4answers
192 views

What is the best way to design a table with an arbitrary id?

I have the need to create a table with a unique id as the PK. The ID is a surrogate key. Originally, I had a natural key, but requirement changes have undermined this idea. Then, I considered ...
4
votes
3answers
3k views

What advantages do we have when creating a separate mapping table for two relational tables

In various open source CMS, I have noticed that there is a separate table for mapping two relational tables. Like for categories and products, there is a separate product_category_mapping table. This ...
2
votes
4answers
2k views

Recommend Stanford Online Database Class? [closed]

I am taking the Stanford Online Machine Learning class which is outstanding. http://ml-class.org Is anyone taking the Stanford Online Database class: http://www.db-class.org? Does it seem useful if ...
4
votes
5answers
1k views

Good techniques to speed up database execution

I have an ASP.Net application that is using a MySQL database. My queries are not executing as fast as I would like them too. Are there some standard ways to increase the speed of a MySQL database ...
4
votes
3answers
5k views

How to design a database wherein multiple tags(string) are to be associated with an id?

I have to design a database wherein I have to associate an audio_id with multiple tags(words). I am considering following approaches to select one from these: 1) To have multiple fields for multiple ...
1
vote
3answers
239 views

Should I structure my DBs based on the data's true form, or how I plan to use that data?

This is mostly a hypothetical question, but it's something I've been tossing around and I wondered what other people though. Here's a contrived example to illustrate: Let's say I'm building an app ...
6
votes
2answers
411 views

What are some useful resources for learning tuple calculus?

I've been trying to go over my tuple calculus over and over again. What's worse is that my professor and tutors are not much help during their hours. Does anyone know a good place to learn about ...
2
votes
2answers
225 views

Are there standard strategies for defining job flow and dependencies?

I'm working on a project that involves the chaining of separate jobs into a single master job, though there may be parallel paths in the chain leading up to the final output. Job and chain details ...
3
votes
4answers
951 views

Which database should I use to manage relationship?

I need 2 capabilities: calculating mutual friends distinguishing between different types of edges, (e.g. FRIEND, ENEMY and other) getting relationships distinguishing between different types of ...
5
votes
2answers
728 views

How can I design an efficient moderation system for comments?

Here's the job I want to do: My project is a website where there will be a lot of comments. Those comments will be moderated: a moderator will connect, see comments and accept or refuse them. I need ...
5
votes
5answers
654 views

Should I use a relational datamodel or Cassandra with ColumnFamily based indexes?

Currently we are working on some storage problems for log data from various servers and communication message logs (HTTP(S), XMPP). There will be many write operations and for read operations we will ...
2
votes
2answers
538 views

Simplifying Database Schema for Extensibility by non-expert Users [duplicate]

Possible Duplicate: Is it necessary to create a database with as few tables as possible I've recently started to develop a database for a ~10 person office (it's an internship for me), and I'...
3
votes
1answer
5k views

Database design for tagging system

I'm creating a system where items will have one or more tags. Now there will be an item table that will store each item, and a tags table, that will store all tags used in the system. My question is:...
5
votes
2answers
264 views

Storing stop/start points in a database

Let's say I am storing start and stop points per user into a database table. For example... let's say in a chat system, a user only needs to see lines 24-293, and 500-512. (Let's say he logged off ...
4
votes
3answers
502 views

How should I implement Transaction database EJB 3.0

In the CustomerTransactions entity, I have the following field to record what the customer bought: @ManyToMany private List<Item> listOfItemsBought; When I think more about this field, there'...
12
votes
2answers
7k 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 ...
5
votes
1answer
1k views

Search multiple tables

I have developed a web application that is used mainly for archiving all sorts of textual material (documents, references to articles, books, magazines etc.). There can be any given number of archive ...
21
votes
8answers
6k views

Favoring Immutability in Database Design

One of the items in Joshua Bloch's Effective Java is the notion that classes should allow mutation of instances as little as possible, and preferably not at all. Oftentimes, the data of an object is ...