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

3
votes
2answers
6k views

How to correctly design NoSql database schema? [closed]

I would like to learn something more about NoSQL databases, so I've chosen to create a new project from scratch to handle football results. In my traditional relational database, I have tournaments, ...
3
votes
1answer
119 views

What relationship would this be and how to implement [closed]

I have a pet project where I am making a Grade Book Application to better teach several different topics in DB, OOP and Planning. Im running into some issues with the database planning of things. For ...
11
votes
3answers
513 views

When accessing/manipulating complex data, is it better to store it in many small pieces or one large chunk?

I am building a web app that manipulates fairly complex data: guitar tabs. As a reference, guitar tabs look like this: ...
1
vote
4answers
922 views

Web application - database design/infrastructure

I have just begun development of a web application (that will be deployed using Google app engine), without going into the specifics, the web app will allow users to deploy a small website of their ...
1
vote
4answers
877 views

At least one database view per database table-Is this good or bad design and why?

We are building a Windows Mobile based application to interface with a customer's existing web application. We are reading and writing data to the customer's SQL server 2008 database. The customer ...
4
votes
5answers
1k views

Structuring database for multi-object “activity” and “following” functionalities

I am working on a web application which operate with different types of objects such as user, profiles, pages etc. All objects have unique object_id. When objects interact it may produce "activity", ...
6
votes
2answers
761 views

How to separate sensitive data in database(MySql)

I need to design a database that will contains information about personal disease of users. What can be the approach in order to implement the columns of the DB's tables: encrypt the information, ...
5
votes
6answers
1k views

Database Table Prefixes

We're having a few discussions at work around the naming of our database tables. We're working on a large application with approx 100 database tables (ok, so it isn't that large), most of which can be ...
-2
votes
1answer
200 views

Adding a forum to an existing site

I've got a site with ~500 registered members, 300 of which are what you'd call "active". Site data is kept in a MySQL dbase. I'd like to add a myBB forum to the site, but this question applies to any ...
2
votes
5answers
346 views

Do any database “styles” use discrete files for their tables?

I've been talking to some people at work who believe some versions of a database store their data in discrete tables. That is to say you might open up a folder and see one file for each table in the ...
2
votes
3answers
578 views

Designing a large database with multiple sources

I have been tasked with redesigning, or at worst optimising the structure of a database for a data warehouse. Currently, the database has 4 other source databases (which is due to expand to X ...
0
votes
3answers
194 views

Optimal Database design regarding functionality of letting user share posts by other users

I want to implement functionality which let user share posts by other users similar to what Facebook and Google+ share button and twitter retweet. There are 2 choices: 1) I create duplicate copy of ...
2
votes
2answers
103 views

User input and automated input separation

I have a MySQL database and an automation script which modifies the data inside once a day. And these columns may have changed by an user manually. What is the best approach to make the system only ...
7
votes
3answers
5k 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 ...
1
vote
4answers
1k views

What are some good tips for a developer trying to design a scalable MySQL database?

As the question states, I am a developer, not a DBA. I have experience with designing good ER schemas and am fairly knowledgeable about normalization and good schema design. I have also worked with ...
0
votes
3answers
1k 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
575 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
2k 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
119 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
268 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
337 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
239 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
113 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
153 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 ...
5
votes
8answers
2k 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 ...
3
votes
1answer
350 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
214 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. ...
10
votes
3answers
2k 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 ...
6
votes
1answer
343 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
545 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 ...
0
votes
1answer
241 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
258 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
356 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 ...
10
votes
8answers
3k 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
310 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
498 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
288 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
156 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 ...
1
vote
1answer
1k views

Books that every software system architect/data modeller must read [closed]

I hope I'm not confusing the term system architecture with the data model/database. Is there a book out there that's the standard for describing best practices, design methodologies, and other ...
19
votes
4answers
2k 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
209 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
276 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 ...
1
vote
5answers
662 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
573 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
1k 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) ?
10
votes
5answers
2k 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. ...
1
vote
2answers
379 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
173 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 ...
3
votes
3answers
947 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 ...