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

0
votes
2answers
217 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
1k 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
211 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 ...
0
votes
4answers
674 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
119 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
0answers
142 views

Social Network Wide ID System

So I have been developing a Social Network site that will allow all the normal stuff like Commenting, Posting, Messaging, Uploading (images, videos, music, files etc), Hash-tagging etc. However I was ...
0
votes
1answer
128 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 ...
8
votes
2answers
353 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
579 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 ...
9
votes
4answers
321 views

Do these specific tables need surrogate keys?

Background I have this tables +-------------------------+ +------------------------+ |Airport | |Country | |-------------------------| |------------------------| ...
1
vote
2answers
259 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 --------------------------------------- ...
3
votes
1answer
162 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 ...
0
votes
1answer
530 views

Banking Applications - Saving Account Balance

From what I know, most banking applications periodically store the account balance at a certain point in time as it would not be plausible to calculate it over the entire history of transactions every ...
4
votes
1answer
143 views

Is there an industry standard for systems registered user permissions in terms of database model?

I developed many applications with registered user access for my enterprise clients. In many years I have changed my way of doing it, specially because I used many programming languages and database ...
0
votes
2answers
146 views

Full Text Search Strategy For My Website

I have a website that allows users to search for items in various categories. Each category is a separate area (page) of my website. For example, some categories might be cars, bikes, books etc. At ...
3
votes
1answer
128 views

Database Design - Optimise Relationships

I am about to embark on a redesign of an application, one where querying the database is particularly annoying. I intend to redesign the database as much as possible but the data shape cannot change ...
1
vote
1answer
65 views

SQL Table Design Modification based on data roles or implementation convenience?

I've got an existing SQL table structure that looks like this: Orders - order_id - organisation_id - [all the rest of the order data] Organisations - organisation_id - [all the rest of the ...
0
votes
1answer
103 views

Google Datastore and deep records

I'm in the beginning of creating a website that uses Google Datastore to store information related to clients and I'm curious if the record is too deep for NoSQL or if I'm fine. Essentially I'll be ...
1
vote
1answer
284 views

Mongodb array vs object

New with Mongodb, and I'm not sure what implications or benefits there are between the use of an array vs an object. So.. As above what are the implications and benefits between the use of arrays ...
0
votes
2answers
156 views

How to salvage a business model from a mismatched database?

There is an application that involves billing enterprise customers for the services their customers utilize. The database schema does not properly reflect the business model. For instance, on a given ...
3
votes
4answers
417 views

In an online questionnaire, what is a best way to design a database for keeping track of users all attempts?

We have a web app where users can take online exams. Exam admin will create a questionnaire. A questionnaire can have many Questions. Each question is a multiple choice question (MCQ). Lets say an ...
3
votes
2answers
276 views

Single Responsibility principle VS KISS principle

Let's assume the modeling of User model in a context of a social network. User concept is composed of two notions: Authentication elements like userName/Password/Email etc... Extra data information ...
2
votes
4answers
321 views

How to genericize foreign key references in table?

I am trying to design a table to store references to multiple tables as a "target" of the item in each row. An example should clarify what I am trying to do: Imagine you have 4 tables (images, ...
2
votes
3answers
392 views

Is there a better way of handling access control logic instead of it being in the UI?

Through most of my dev experience, I've never had to deal with much variety of access control architectures. They've all been pretty straight forward: Group [Create, Update, Delete] - User 1 ...
0
votes
1answer
92 views

Keyword Collection, ManyToOne or OneToMany relationship?

I hope this is the right place for this type of problem. An Object of my class Movie can have several keywords ( like 'fight club', 'brad', 'fincher'), and be part of different categories (like ...
1
vote
1answer
1k views

How to model/structure data that contains parent/child relationships for the same entity?

I think it is just my thinking that is off here. Suppose I have two entities: question and choice. A question can be a parent or child, or both (in relation to other questions). It seems that this ...
2
votes
1answer
890 views

Representing a parent/child hierarchy in a Relational Database

I am working on an application that connects to another application (Quickbooks) using an API by sending/receiving XML requests/responses. I am able to work with the data in the XML response and load ...
3
votes
3answers
251 views

Design pattern to dynamically create patterns found in a list of links

Can anyone help me think of a way to dynamically generate the patterns section of a tool I am creating? I'm not sure how to store and generate these "patterns" dynamically. What the program does is ...
3
votes
2answers
927 views

Handling subscriptions, balances and pricing plan changes

Preamble My aim is to create reusable code for multiple projects (and also publish it on github) to manage subscriptions. I know about stripe and recurring billing providers, but that's not what this ...
2
votes
3answers
153 views

Referencing countries via Foreign Key Constraint

To make things simple to explain: I've got two tables: Table1 and Countries. The Countries table contains a nice flag and some information, such as DisplayName, the ISO3166 ALPHA3 code and the phone ...
11
votes
2answers
968 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
806 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
87 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
81 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
219 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
188 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
241 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 ...
6
votes
5answers
267 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
315 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
179 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
155 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
100 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
170 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
583 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
113 views

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

------------- 1 * -------------- * 1 -------- | Container |--------| Assignment |-----------| User | ------------- -------------- -------- ^ ...
2
votes
3answers
290 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
428 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
363 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
1k 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 ...