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

10
votes
2answers
597 views

Is there a design pattern for managing deep many-to-many relationships?

I'm having trouble defining this data pattern I've come across working on several applications. It consists of: An object type which is composed of many objects itself A second object type, where ...
4
votes
3answers
129 views

When to load related database data in web request?

I'm trying to determine when a web application should query a database for related data that may or may not be used in the current request. As an example, consider a database that tracks assets for a ...
0
votes
1answer
260 views

Best way to design a database interface [duplicate]

This is my situation I have a website, mobile client and desktop client. They can all pretty much do the same operations (website might be able to do more now but in the future they might have the ...
2
votes
2answers
137 views

Where to put the Country Table?

It might sounds like a silly question but here is my problem. We have 100s of databases and in each of those databases, there is a country table. It was ok this way but we ran into minor problems at ...
6
votes
3answers
651 views

Is using hashes for primary keys a good idea?

The Austrian electronic ID card relies on the so-called sector identifiers. For example a hospital gets to identify a person by getting a sectorId for that person, which is computed roughly as follows:...
1
vote
0answers
242 views

the right way to generate my Django model/db_table

I went through the Django tutorial with in mind to go through it again using it to set up my small app. So now I am making a simple app where users can search a large set of strings and get a subset ...
3
votes
1answer
826 views

Handling Many to Many Relationship using Restful Uri

I am having confusion while choosing a database relation and Restful URI for application I am developing. I am making a simple library application with following requirements. Book can belong to ...
1
vote
1answer
41 views

Creating a specific table for shared columns among other tables (models)?

I'm creating a webapp that has a variety of models: Product, Category, ProductOption, etc. All of these models have a set of shared attributes: name, description, image, etc. There are two options I ...
2
votes
1answer
153 views

Time attendance module design

I am working on time attendance module and I managed to read the data from the timing machine in the following format: Id CheckIn Type Status 0000142 5/15/2015 6:00 PM 2 OK ...
-2
votes
1answer
90 views

How to design database to keep list of special members among all members? [closed]

I want to design Database which works as below explained. We have some members and they are listed in many categories. Among members, some have special privileges. We have Memeber Table We have ...
1
vote
1answer
198 views

How to structure a reservations system in a document based database?

I really don't know if this is the right forum for this but I need help designing my database. Also I apologize for the horrible title: I don't know how to summarize the issue below in a single title. ...
2
votes
1answer
759 views

What is the best way to store a formula in a database for a table driven formula? [closed]

So I am changing my code over to a table driven formula system to make it sustainable in the long term. So basically I have 5 foreign keys that will get me to a single formula to use. However I'm ...
2
votes
2answers
212 views

Handling insertion to database with Repository pattern

I have a design question for working with databases in object-oriented PHP. I have a table in my database called products. Then I have 2 classes for this table in my PHP project. ProductsRepository....
2
votes
2answers
421 views

One table for all transaction VS. Table for each user

I'm making a website which has to do with money transactions. Everything was okay untill I came across the dilemma Should I have one table to store all transaction history or I should have a ...
2
votes
2answers
168 views

How to design a relational database for user following other users?

I want to design my relational schema to store information about users following other users. So if user1 is following user2 the schema will be as below user_follow ------------ |user1|user2| -> ...
2
votes
3answers
333 views

Table design for holidays

I'm creating a system to store holidays and events. I have a basic understanding of the schema for the static dates (every 23rd of march for instance). My trouble lies with the relative dates (i.e. :...
1
vote
3answers
384 views

DB Schema design : single table with more columns vs multiple tables with fewer colomns

What would be better DB design for a social network website. A single table with more columns and less rows or multiple table with fewer columns but more rows. For example: User can post an update on ...
0
votes
2answers
493 views

Why should I use lookup tables at all?

This question confirmed, what I was already aware of -- instead of directly storing strings in table, I should rather use integers or enums and join them with lookup table, to get, as a result, string,...
2
votes
3answers
197 views

At which point using string-based enums can become a real problem?

This is a direct follower to this question. An answer given to that questions says (my understanding), that nowadays using string-based enums instead of integers isn't a much problem, because hosting ...
1
vote
3answers
225 views

Using auto-increment clustered key and guid primary key together

My boss is the db administrator in a new software project and he came up with that design where every table will have both a auto-increment clustered key and a guid primary key (or possibly composite ...
1
vote
0answers
33 views

Adding fields a good use case for EAV? [duplicate]

Is being able to add fields quickly to a system a good use case for EAV? What would be the frequency or number that would justify using an EAV model?
2
votes
1answer
395 views

An approach to multilingual db design

My boss is planning on a new db and wants to support multilingual data in this manner: LocalizedDescs (Guid / LanguageGuid being the primary key) Cluster Guid LanguageGuid Desc ProductCategories ...
0
votes
1answer
188 views

Generation of ids in offline/online application

I'm a bit confused about how to identify the elements in my application. I have a rest backend with an sql database. I'm currently using numeric (autoincrement) ids as primary key and use these for ...
0
votes
1answer
302 views

Storing Multi-select and Single-select data together

I've got a database with some multi-select fields. To keep it simple, let's just say those fields are MatchGender and Interests. I want users to be able to find people who match (hence Gender is a ...
1
vote
1answer
166 views

CMS with unknown data structure [closed]

I am looking to build a sports small application, but I would like to build it so other people/organizations could use it as is. These would be separate apps, not one app that caters for all. For ...
1
vote
2answers
295 views

NoSQL or MySQL? [closed]

I'm developing an app that requires to save data that is not consistent across all rows. These database entries represent different kind of sport activities. Let's take an example: In football the ...
1
vote
1answer
199 views

How to handle rating? [closed]

I want to implement like/dislike system, but I don't know how to achieve best performance. I have to tables: blog posts and comments that need rating. Should I store all data in one rating table? Or ...
1
vote
2answers
224 views

One Enum vs Multiple Enums

I am developing an application where a user submits a mission and other users accept the mission. Pretty simple. I want to keep a track of the mission progress status and store it into a database. ...
0
votes
1answer
292 views

Database design for linking parent & child relationship

Currently i have requirement saying user from an UI creates there own forms which has types textbox,choicegroup etc.. Does anyone have a any idea on how to architect the database to store all data? ...
2
votes
1answer
117 views

Use of tenant_id in multi-tenant database

We are building a SASS web application where every company will be using its own database schema. Using postgresql, there is a table 'companies' in the public schema that holds the name of the company ...
2
votes
2answers
259 views

Is normalization all that's needed to ensure data integrity and quality reporting?

I've gotten out into the real world recently, and for the first time have had to really think about database design, as I've been developing across the entire Java stack. With that I've realized that ...
4
votes
1answer
365 views

How to store data that is recorded with different frequency?

I'm writing a metallurgical web application, that must store data (electrical parameters) recorded at different frequencies. For example: six parameters recorded with frequency of one measure per ...
12
votes
2answers
2k views

Why store flags/enums in a database as strings instead of integers?

I've been browsing SQL dumps of some famous CMSes, including Drupal 7, Wordpress (some quite very old version), and some custom application based on Python. All of these dumps contained data with ...
2
votes
1answer
146 views

Is there a valid symbol for “two or more” with Crow's Foot notation?

I have a database design for which one entity of Class A always has at least two entities of Class B. Can I express this with valid Crow's Foot notation? One possible idea I had was this: Excuse ...
1
vote
1answer
130 views

SQL - Designing a Threshold value table

I need to design an SQL table to be used for "threshold" or special values, which in general would have the following properties: - key - value - comparer (less than and greater than as a start, ...
0
votes
1answer
100 views

How to represent a Model with a 1:N relation?

I have a task to associate 1:N documents to a record in my hardware table. This file could be a warranty, or whatever. In SQLServer I set up a FileStream group and made a table for only the file and ...
-1
votes
3answers
813 views

Are there any good reasons to use ER Diagrams instead of UML Class Diagrams for data modeling? Is ERD the COBOL of data modeling? [closed]

Are there any good reasons to use ER Diagrams instead of UML Class Diagrams for data modeling, given the fact that class diagrams subsume ER diagrams? Or is it just for historical reasons because ...
3
votes
1answer
62 views

Architecting a SaaS for backwards-compatibility in regards to data and business logic

I have a SaaS platform where the user fills out a form and data entered into the form is saved to a database. The form UI has a large amount of config (originates from the DB but ends up in JavaScript)...
11
votes
5answers
833 views

Version controlling a database's contents

I'm working on a web project that involves user-editable content, and I'd like to be able to do version-tracking of the actual content, which lives in a database. Basically, I want to implement wiki-...
0
votes
0answers
79 views

MySQL Database normalization for a user model

I currently have a MySQL Relational Database with a users table with the following attributes: ID (Primary key) Name Username (Unique) Password Email (Unique) Description Profile Picture File Path ...
1
vote
1answer
40 views

Recording many data items and retrieving time-based snapshots

I have a .Net Windows application that continually receives data from a hardware device. Additionally, the user can change various settings and parameters from within the software. I would like to ...
5
votes
2answers
843 views

Database Design for Inventory

I am starting to design a typical product based MySQL database but I keep running in circles on how to design the tables. I have many types of products Jeans Tshirts Dresses Which have many of ...
8
votes
5answers
325 views

Fine grained search over a large dataset

I have roughly 4 million records per day and have to keep 7 years worth online, so we are looking at 10.2 billion records that I need to be able to search through. The users are expecting that the ...
0
votes
2answers
59 views

Groups relationships - Detailed or Direct?

Current design I have a Person model: PersonId Name ----------- ---------------- 1 Jessica 2 Jennifer Then I have a ShareModel which have the ...
47
votes
7answers
5k views

How can I argue convincingly against duplicating database columns?

I've started working at a new organization and one of the patterns I've been seeing in the database is duplicating fields to make writing queries easier for the business analysts. We're using Django ...
1
vote
1answer
320 views

Likes / Stars / Following - same or separate data structures? [duplicate]

I am building a social network, which will have standard features of 'likes' (upvotes) for posts, 'following' between users and 'stars' for favorite posts. These might reasonably expand in the future ...
1
vote
1answer
366 views

Am I planning my database/backend correctly?

I'm an iOS developer and to be honest I'm not really looking to become a database expert right at this moment, but I do need to know how to properly plan out what I believe to be a pretty cookie ...
4
votes
1answer
70 views

Modular Database, Inheritance of Schema

Consider multiple relational database schemas that have an inheritance dependency structure as known from OOP systems: Tables are just a placeholder for arbitrary database objects such as Views, ...
2
votes
1answer
196 views

Is it good practise to have the class name as a prefix in Django model?

I wrote a model in this format in Django: class Block(models.Model): block_id = models.AutoField(primary_key=True) block_name = models.CharField(max_length=200) block_code = models....
2
votes
2answers
871 views

Notifications table design?

I'm trying to create a notification system for my social network. But I'm stuck on the database design. so when a user comments on another user I want to display "X commented on your post." or when ...