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

12
votes
3answers
2k views

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

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 ...
0
votes
1answer
116 views

What are some database designs that support lineage tracing?

Environment PostgreSQL 9.1 Java Problem I'll try to illustrate the problem here as simply as possible: I'm trying to record the following relationship: Node 1 => Node 2 => Node 3 Where Node 1 ...
1
vote
1answer
87 views

What common apporaches exist for database versioning for a feature driven development?

It's not clear how data consistency is provided for different branches. We have a large knowledgebase which is filled by the experts and delivered as updates to our clients. This data is also used ...
0
votes
4answers
231 views

Create a database for database tables

I'm working on a project where I have to give users the ability to build their own tables, each user can create more than one table. (the type of all data is string). The user can create a table and ...
0
votes
3answers
273 views

SQL Server + database design and non mandatory fields

I am developing an app which allows a user to upload a maximum of 20 photos, the straight forward way is of course adding 20 columns to my database table but if the user only uploads 1 image, the ...
4
votes
3answers
301 views

What is the best approach for overriding fields in a database?

I have a database that contains projects and each project contains many samples. I want a project level threshold value, to trigger highlighting when a field on the sample is above this threshold. I ...
7
votes
5answers
517 views

Storing a re-orderable list in a database

I'm working on a wishlist system, where users can add items to their various wishlists, and I plan to allow users to re-order the items later on. I am not really sure about the best way to go about ...
1
vote
1answer
470 views

Multi-user Configuration Settings design

I am designing a flexible and extensible way to store configuration settings for users. Database Table Design: ╔═════════════════════════════╗ ║ ConfigurationItemDefinition ║ ...
12
votes
6answers
1k views

In plain English, what is a database index most similar to?

Setup: Suppose you are teaching an introduction to Databases class, the students are CS students that have a working knowledge of tree structures, how they can speed up searches, and have probably ...
0
votes
3answers
180 views

How to maintain user relationship when user unfollows another user

I have a table of user relationship which stores who follows whom, the table structure is as follows -id -follower -following when someone follows any other user, I add a record with follower user ...
-1
votes
1answer
166 views

Storing User Form Data

I am trying to come up with a data schema/methodology for storing the data collected from various different user application forms. All forms contain the basic info like name, address, etc but then ...
2
votes
1answer
105 views

How do I manage multiple relationships from a software design perspective?

How do I go about managing projects when there are two fundamental relationships that need to be maintained? The most obvious example that I can think of would be a firm doing some kind of ...
3
votes
2answers
215 views

Bayes filtering and data storage with expansive data sets

I am looking to write a Bayes filter that will act as an indicator of topic for a number of topics with a variable number of sources. Given a really big number of RSS feeds and here really big might ...
1
vote
3answers
805 views

Should lookup tables enumerating strings have an integer primary key?

When I learned relational databases, the prof said that one would "almost always" want an artificial int as the primary key in a table, but did not specify what the exceptions are. At some time I ...
0
votes
1answer
127 views

What is a good design for a container, assignment and items

------------- 1 * -------------- * 1 -------- | Container |--------| Assignment |-----------| User | ------------- -------------- -------- ^ ...
2
votes
3answers
436 views

Using a database for each module in a system [duplicate]

I was reading this question: I was trying to standardize and modularize some functions (Email Management Module, CMS Module & etc) by implementing a 3-tier architecture concept where each ...
1
vote
2answers
468 views

Why OTServs have an item cloning problem if the server crashes? [closed]

OTServs are open source MMORPGs with a huge community. Mostly all of them have a serious problem: if the server crashes, people can clone items. This is a dirty trick that can be executed because the ...
1
vote
1answer
759 views

NoSQL (Azure Table Storage) RowKey design for ordered data

I store large quantities of GPS data in storage tables. Each partition can reach up to 1 million entities. Each GPS entity that is inserted into the table is actually sequential to the previous data ...
5
votes
6answers
4k views

Why is the usage of string keys generally considered to be a bad idea?

This has been bugging me for a while. Most of the time, when it comes to storing data in structures such as hashtables, programmers, books and articles insist that indexing elements in said structures ...
1
vote
1answer
118 views

Azure Storage Table design with multiple query points

I have the following Azure Storage Table. PositionData table: PartitionKey: ClientID + VehicleID RowKey: GUID Properties: ClientID, VehicleID, DriverID, Date, GPSPosition Each vehicle will log ...
6
votes
1answer
123 views

Ledger or log design for a point-based system?

Right now creating a new service that will integrate into our company's existing platform. The service will be responsible for tracking "points" that a user can generate over time through certain ...
1
vote
1answer
612 views

A multi tenant data architecture

We are at a planning & redesign stage of our loyalty card application. As it is understood it will be a multi-tenant application. And I need to take your thoughts. Here are the some key points we ...
1
vote
1answer
329 views

Table scaling with partitions or with separate databases?

Let's say I have a table (let's call it BigTable) which could experience 5,000,000 INSERTS per day (with possibly just as many SELECTs). Each row inserted is about 50kb. These daily INSERTs are ...
3
votes
2answers
138 views

Does this situation call for well specified associations, single table inheritance, or polymorphic association?

I'm building a web-app where users can keep track of who owes whom money. I have users and debts. Given a user I want to be able to find out all the debts to other users, and all the loans to other ...
6
votes
4answers
6k views

Better way of storing key-value pairs in the database?

I have a C#/SQL Server program that sometimes needs to store data. The data could be a response from a web service, a result of a database query, or any number of other things. There's no way of ...
0
votes
2answers
412 views

How to improve database schema with different n:m and 1:n associations between users/tasks/comments/attachments

I'm trying to build a database for an upcoming Ruby on Rails webproject. There will be 4 tables minium: tasks, users, attachments and comments. And 1 table to join the n:m relations (tasks <-> ...
7
votes
3answers
1k views

How should I represent an enumerated type in a relational database?

I am working on developing a relational database that tracks transactions that occur on a device I'm working on for my company. There are different types of transactions that could occur on the ...
2
votes
2answers
170 views

Should database-models (conceptual or physical) be reviewed by DBAs?

Where I work, new applications that are being developed that will use their own relational database, must have their database-models (conceptual, then physical ) reviewed and aproved by DBAs. Things ...
3
votes
6answers
557 views

Should a database table(s) structure match its intended data structure(s) in the logic?

This question branches out of this question, What are the differences between algorithms using data structures and algorithms using databases?. The General Question Should a database table(s) ...
3
votes
4answers
444 views

(Dis)advantages of datetime vs. long in globally used applications [closed]

I am currently designing a web/mobile application the bulk of whose users will be distributed across the different U.S. time zones with a potential of scaling up to other countries and time zones as ...
8
votes
7answers
732 views

Design Parts DB

I'm developing a tool that handles (electrical) parts. The parts can be created, viewed, modified, deleted, grouped and so on... In order to make this question useful for future visitors I like to ...
1
vote
2answers
1k views

Alternatives to sql like databases [closed]

These days computers usually have 2GB or 4GB memory. I like to use some secure client server model, and well an sql database is likely candidate. On the other hand I only have about 8000 records, ...
1
vote
1answer
362 views

Storing revisions of a document

This is a follow up question to my original question. I'm thinking of going with generating diffs and storing those diffs in the database 'History' table. I'm using diff-match-patch library to ...
4
votes
3answers
7k views

When should we use weak entities when modelling a database?

This is basically a question about what are weak entities? When should we use them? How should they be modeled? What is the main difference between normal entities and weak entities? Does weak ...
1
vote
1answer
692 views

NoSQL as file meta database

I am trying to implement a virtual file system structure in front of an object storage (Openstack). For availability reasons we initially chose Cassandra, however while designing file system data ...
3
votes
1answer
292 views

Database Schema Usage

I have a question regarding the appropriate use of SQL Server database schemas and was hoping that some database gurus might be able to offer some guidance around best practice. Just to give a bit of ...
21
votes
2answers
7k views

self referencing tables, good or bad?

Representing geographical locations within an application, the design of the underlying data model suggests two clear options (or maybe more?). One table with a self referencing parent_id column uk - ...
-1
votes
3answers
776 views

What makes Java so suitable for writing NoSQL Databases

Looking at this page that aggregates the current NoSQL landscape, one can see that the majority of these projects are written in Java. Databases are complex systems software dealing with the file ...
12
votes
8answers
962 views

Are there examples of non CRUD approaches?

I'm a programmer but also have worked as an archivist. As archivist it's a lot about keeping data. I often get into arguments with colleagues when it comes to operations on data. I don't like the U ...
4
votes
2answers
993 views

Which design better when use foreign key instead of a string to store a list of id

I'm building online examination system. I have designed to table, Question and GeneralExam. The table GeneralExam contains info about the exam like name, description, duration,... Now I would like to ...
2
votes
1answer
420 views

Designing persistence schema for BigTable on AppEngine

I have tried to design the datastore schema for a very small application. That schema would have been very simple, if not trivial, using a relational database with foreign keys, many-to-many ...
4
votes
3answers
720 views

Set modified date = created date or null on record creation?

I've been following the convention of adding created and modified columns to most of my database tables. I also have been leaving the modified column as null on record creation and only setting a ...
2
votes
2answers
242 views

how to model this relationship ? in ERD

I have three entities : Technician Vehicle Repair The question is how to model this in ERD? Knowing that a technician can repair multiple cars, and the same car can be repaired by one and only one ...
0
votes
4answers
404 views

Is data integrity possible without normalization?

I am working on an application that requires the storage of location information such as city, state, zip code, latitude, and longitude. I would like to ensure: Location data is accurate Detroit, ...
6
votes
4answers
272 views

Where ORMs blur the lines between code and data, how do you decide what logic should be a stored procedure, and what should be coded?

Take the following pseudocode: CreateInvoiceAndCalculate(ItemsAndQuantities, DispatchAddress, User); And say CreateInvoice does the following: Create a new entry in an Invoices table belonging to ...
1
vote
3answers
4k views

Storing Attendance Data in database

So i have to store daily attendance of employees of my organisation from my application . The part where I need some help is, the efficient way to store attendance data. After some research and brain ...
2
votes
2answers
175 views

Best approach for saving highlighted areas on geographical map

I am designing an application that allow users to highlight areas of a geographical map using a tool that is like brush or a pen. The tool basically draw a circle with a single click and continue ...
8
votes
1answer
776 views

Classless tables possible with Datamapper?

I have an Item class with the following attributes: itemId,name,weight,volume,price,required_skills,required_items. Since the last two attributes are going to be multivalued, I removed them and ...
7
votes
5answers
2k views

Predicting advantages of database denormalization

I was always taught to strive for the highest Normal Form of database normalization, and we were taught Bernstein's Synthesis algorithm to achieve 3NF. This is all very well and it feels nice to ...