This tag is for general database questions; if you question is specific to SQL use that tag instead.

learn more… | top users | synonyms (1)

1
vote
3answers
6k views

Using Autoincrement Primary keys as Foreign Keys

When creating a table, there is an option for us to set the numeric primary key as autoincrement where it's value increases whenever a new data in inserted. This primary number can be used for ...
1
vote
1answer
551 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
240 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. ...
0
votes
1answer
249 views

Geographically Distributed (Data & App) Architecture

Is there any design patterns (or best practices) for implementing a geographically distributed system (mostly a database)? Description: There is a network of warehouses and a central office. Now I ...
2
votes
3answers
290 views

How to insert a database layer in PHP

I maintain and add features on a freelance basis to a website written in PHP. The application was started in PHP4 by a junior programmer back in 2006, and features some very insecure and ...
1
vote
2answers
261 views

Optimize database interaction through VPN

I have to develop a batch procedure with VB.NET. This simple procedure is going to read some data from a MS SQL database, execute some elaborations and the write the results on another Oracle SQL ...
2
votes
1answer
216 views

Incremental seek on MySQL result

When outputting data in a large file, I try to make it a habit to incrementally output data in chunks, to prevent reading the entire large file into memory. As an example to show what I mean: $fp = ...
0
votes
2answers
1k views

Entity Framework designer not effective anymore

At my current workplace, Entity Framework 5 is used for all data access/manipulation. It is a database-first approach using the model designer to add/edit/delete/update entities. Problem We now ...
0
votes
2answers
327 views

Best practices for rotating large datasets?

[edit]Turns out there's 1.5 million records... The SQL database is taking about 17-20 seconds to run the query. Would it be best to have Java handle the joins and rearranging in that case? Since this ...
1
vote
3answers
545 views

Should you write automated tests to check that data is correct (the contents of config files, databases, etc.)?

Simply put: is there a best practice for whether developers should write automated tests to check that data is correct (the contents of config files, databases, etc.)? If so, what is it? Assume that ...
1
vote
1answer
258 views

What's the best approach to building a db schema for profile, but to track changes?

I'm working on a site where companies can create a profile then add locations for each profile. Those first two tables are simple enough. But any changes to their profiles or locations have to go ...
4
votes
2answers
1k views

Logic design question for SQL query

Was wondering if you could help me on a SQL problem I’m having. I have a set of records of events where each event has a start time and end time. No event has the same start time, and the end time ...
5
votes
5answers
4k views

When should use of database be preferred over parsing data from a text file?

I was making a Python program to measure the growth of codereview.SE. My approach was to get the "Site stats" shown on the front page and store them on my hard drive. I plan to do this once every day. ...
0
votes
2answers
348 views

How can I conceptually model a craigslist search?

I'm trying to understand how something like this works, but I'm inexperienced and I'm trying to understand how the process would work. Say you have ten categories, a thousand zip codes, and ten ...
-4
votes
3answers
1k views

Is it a row or a record (or a tuple)

If I look at a database table for editing - is it made up of rows or records? If I write some sql and then look at the result is this made up of the same elements as the answer to 1.? Is it the case ...
1
vote
2answers
399 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
111 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 ...
18
votes
7answers
2k views

Safely fixing production database data

Bugs happen and sometimes data has to be fixed in production. What is the safest way to go about this from a big company standpoint? Are there tools that can help? Here are some considerations driving ...
0
votes
2answers
1k views

Is my application vulnerable to SQL injection if I don't specify each type in Doctrine2?

I thought Doctrine 2 DBAL prepared statements were safe from SQLi. But I found this confusing bit in the docs: By default the Doctrine DBAL does no escaping. Escaping is a very tricky business ...
15
votes
4answers
1k views

Do I need IDs in my database if the records could be identified by the date?

I am writing my first application for Android and will use the SQLite database so will be trying to limit the size as much as possible, but I think the question applies in general to database design. ...
1
vote
1answer
197 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 this....
0
votes
2answers
119 views

Which component is responsible for updating the database schema?

We have what can be called a distributed application: One full-blown web app and a small REST service. Both must be separately deployable. Both access the same database schema (they actually share ...
1
vote
1answer
201 views

how to create database in which new columns have to be added periodically?

I want to create a database that tracks different projects and their finances. As finances are tracked monthly, a new column has to be created for each month. I could create all tables together at the ...
1
vote
2answers
1k views

Data Aggregation of CSV files java

I have k csv files (5 csv files for example), each file has m fields which produce a key and n values. I need to produce a single csv file with aggregated data. I'm looking for the most efficient ...
4
votes
1answer
214 views

Class instance clustering in object reference graph for multi-entries serialization

My question is on the best way to cluster a graph of class instances around specifically marked objects (objects are the graph nodes and the references to each other are the directed edges of the ...
20
votes
6answers
11k views

Unit-Tests and databases: At which point do I actually connect to the database?

There are answers to the question on how test classes that connect to a database, e.g. "Should service test classes connect ..." and "Unit testing - Database coupled app". So, in short let's assume ...
13
votes
3answers
2k views

How to create better OO code in a relational database driven application where the database is poorly designed

I am writing a Java web application that consists mainly of a bunch of similar pages in which every page has several tables and a filter that applies to those tables. The data on these tables comes ...
3
votes
3answers
2k views

Is the database the bottleneck or not? High Scalability talk

I found an interesting article on high scalability web site where it talks about EBay scalability and especially a specific passage struck me: "(Ebay strategies)...Move work out of the database ...
1
vote
1answer
451 views

how do design the database tables for file download statistics

I have the web application where i am serving file downloads in php. All is working fine and i have the download column in file field where i increment it. But i want to have full statictics of ...
24
votes
2answers
14k views

Data Synchronization in mobile apps - multiple devices, multiple users

I'm looking into building my first mobile app. One of the core features of the application is that multiple devices/users will have access to the same data -- and all of them will have CRUD rights. I ...
1
vote
4answers
3k 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
151 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 ...
2
votes
0answers
117 views

data stored in dbase (application result dd.mm.yyyy)

I have a .dbf file (dbase) with data of type "date" to read directly via php. I have the application result (format: dd.mm.yyyy) and the stored hex code. I can't interpret the hex data to get the same ...
7
votes
3answers
1k views

Using a Directory Service instead of a Database

I've just started working on an existing application that uses an LDAP directory service as its object store instead of a database. Many of my coworkers have been commenting how an application should ...
2
votes
2answers
768 views

NHibernate and complex domain model against a legacy database

I'm trying to refactor (actually, rewrite) an application which currently uses only stored procedures to access the database (hundreds of SPs). My goal is to use Nhibernate and follow a good design as ...
2
votes
3answers
941 views

Good Practice : Storing variables and classes names in database for direct call

I would like to know if this is a good practice to store variables and classes names in database to use let users accessing them. For instance users may create entries in database's table in which ...
4
votes
5answers
5k views

Building a web app with encrypted MySQL database entries?

I have some experience in building PHP based websites with MySQL access, storing encrypted user details, but all other fields being plain text. My most recent project will require sensitive data to be ...
3
votes
1answer
242 views

Processing every leaf under a node in a tree efficiently

Short version: In a tree (non-binary) with many levels of children, where each node can have multiple leaves, what is the best way to tally leaves that meet a certain condition given a node? Long, ...
0
votes
2answers
9k views

Drop table if it already exists and then re-create it?

I was just learning about sqlite in android programming from the New Boston Travis videos and I have come across something strange: It is said that if the database does not already exists, onCreate() ...
0
votes
1answer
418 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 ...
0
votes
1answer
465 views

Advantage of Solr over other DBs?

As far as I know, Apache Solr Lucene project is a web-app which is used for rich text searching. I just wanted to know: Why can't I use MySQL for that and just for rich text searching, why we use ...
1
vote
2answers
824 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 --------------------------------------- ...
11
votes
1answer
9k views

Dynamic form builder forms and database design? [closed]

Say your users can create their own web-based forms (textboxes, selects, etc) and publish them on the web for their users to fill out. Does anyone have a resource or any advice on how to architect ...
0
votes
2answers
426 views

Python/Database Website development [closed]

I want to create my own website that would allow me to acess a database in the server and do inserts and lookups in a user friendly way. I am a seasoned user of linux and C/C++, and also have ...
1
vote
1answer
215 views

DB table with column for each datatype

I need to design a DB for storing key-value data. List of Key names isn't fixed and should expand at later point. I don't want to create a table with a column for each key, because I'll need to add ...
5
votes
1answer
2k 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 info:...
0
votes
2answers
1k views

Multiple Object Instantiation

I am trying to get my head around object oriented programming as it pertains to web development (more specifically PHP). I understand inheritance and abstraction etc, and know all the "buzz-words" ...
2
votes
5answers
686 views

Why is TDD not working here?

I want to write a class A that has a method calculate(<params>). That method should calculate a value using database data. So I wrote a class Test_A for unit testing (TDD). The database access ...
8
votes
4answers
10k views

C# Minimal SQL Database

I'm working on a small project (not production) where I'm needing to store enough data that something like a CSV would be inefficient to process and an SQL/MySQL server would be too much. Does .Net ...
3
votes
1answer
106 views

Assessing Relative Maintainability

We (a contractor, actually) are implementing an off the shelf system to replace a legacy homegrown system for the core domain of the company (designing widgets). Unfortunately both systems will have ...