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
1answer
121 views

What is a good way to store geographical data for distance calculations

What would be the best way to store data in a database for geographical calculations? Say I have an ItemsForSale table paired to a Users table which holds their geo location. I know I can use latitude ...
1
vote
1answer
198 views

Database design: circular reference but dynamically created

I am trying to design database for this case: Assignments have vectors, relation is 1:N Assignments have submissions, relation is 1:N Submissions have executions, relation is 1:N Every execution ...
0
votes
2answers
214 views

Examples of designing a database that handles point tracking?

I'm attempting to expand my knowledge of how database designs and concepts work. I'm working on a hobby web/mobile app for practice that will challenge me. However, I am running into an issue. What ...
1
vote
3answers
70 views

What are the use cases for these relationship types?

Use Case What are the use cases for creating a one-to-one relationship where the parent points to the child compared to a child pointing to the parent? If there is no difference, is one preferred ...
1
vote
1answer
177 views

Database Design for Web based RSS Feed Agreegator

I am working on an open source application which can allow users to add RSS feeds. All users of the site can read the content of those RSS feeds. It's not just for user's own feeds. Using PHP and ...
3
votes
2answers
183 views

Is it right to persist a computed property to the DB?

I would like to know what do you think about persisting an "autocomputed property" to the database. For example I have these properties decimal Price {get;set;} decimal Tax {get;set;} decimal ...
1
vote
1answer
91 views

Design: Mixed XML / Relational or Pure Relational?

Is it acceptable to model a very complex object graph using XML in the database, but leave the rest of a system in relational tables? I'd like to gauge opinion on this as I've hit a bit of a ...
13
votes
4answers
2k views

Is it bad practice to allow user defined fields?

Generally speaking, is it considered bad practice to allow for user created fields in a database for a webapp? For example, I am making a home inventory webapp for my wife, and she is going to want ...
1
vote
1answer
236 views

Should I really need to implement event sourcing is this case?

An example of use case when event sourcing is applicable here whose excerpt is: Say, something went wrong in your app and as a result, a user gets blocked for abuse but that user claims to not ...
1
vote
2answers
515 views

in multi-tenant systems what is the correct practice for segregating each tenants data from each other?

I am a first-time azure developer and I am taking over a single-tenant cloud system and have been tasked with converting the system to multi-tenant. What is the correct practice to separate each ...
0
votes
2answers
141 views

Whats a good structure to save and retrieve locations of images?

I got a java-ee application, where I collect informations about movies. Im my backend I provide data like the name, description, genre and a random uuid. I also got lots of related files, which are ...
0
votes
0answers
48 views

Using a single table for identity and metadata

I'm in the early design phase of a project to provide an e-commerce platform that will require several entities to be modelled, products, customers, orders, CMS pages, etc. They will all have a few ...
2
votes
1answer
125 views

Is it substandard if I have multiple tables in my database yet there's no relationship at all? [closed]

I have multiple tables in my database. Some tables have relationship to others and some don't have. My friend told me that it is not advisable to have a table that is not related to others. Is it ...
0
votes
0answers
90 views

Avoiding pitfalls of a naturally recursive relationship between tables

I'm developing a system to manage "cases". There will eventually be many different "kinds" of cases, so I've started with a "CaseTemplate" table that can be used to define what a case looks like and, ...
3
votes
1answer
1k views

Ruby on Rails database using static lookup tables or constant strings

In some ruby on rails projects, I have seen instances where strings are used instead of a foreign key reference to a lookup table. I usually code in C#/SQL Server and use lookup tables, but am not ...
1
vote
1answer
44 views

How to represent one special related record

I have two tables, project and photo. photo has ID,projectID,path : each project has multiple photos. Now one of these photos is the one that has to be displayed. The question is how to represent ...
2
votes
2answers
259 views

Implementing bussiness logic with a large number of business rules and processes

We are currently working on a project that heavily relies on a database. Among many tables the main focus is on table "data" which is linked to another table "data_type" as many-to-one, which is then ...
0
votes
0answers
60 views

What's the best way to hold features in a database?

taking for example an application that holds Companies and as we move along time, more and more features are needed. I do tend to hold everything in the Companies table, but I rather get some input ...
0
votes
2answers
86 views

Store the result of a complex but frequent database query, in another temp persisting table

I have a slightly complex SELECT query which uses a number of JOINs and variables inside it and the query is run frequently throughout the application. The result varies when one column is updated. ...
0
votes
4answers
185 views

should I store common data between two tables in a third separate table in my database?

If I have 2 tables in my database, let's say for instance 'buyers' and 'sellers' but I collect a common set of data for both for instance telephone number, email address, address etc. Is it better ...
3
votes
3answers
441 views

Is it better to use a switch statement or database to look through 5,000 to 10,000 instances?

I have some JSON data in this format (5,000 instances for now): {"id":"123456","icon":"icon.png","caseName":"name of case"} I am allowing the user to search for the name of the case and then return ...
2
votes
1answer
450 views

Data Model for selling groups of products as one product

Say I have an e-commerce with a data model like the following, which allows a Sale to describe how a single Product is sold. This division allows a product to be sold multiple times in different ways ...
1
vote
2answers
217 views

How to store currency ranges in a Postgres table?

I am using Postgres 9.3. I have to store range data like <$250K, $250K-$4M, etc which we will display in a dropdown. now I am making a table for all possible options that can be configured from ...
2
votes
2answers
2k views

single table w/ extra columns vs multiple tables which duplicate schema

I am working on a project where at some point, I needed to make a decision on whether or not, in the database, I should have a single table with multiple columns that not every record uses, or ...
4
votes
3answers
897 views

Designing a fitness / weight lifiting routine database

I'd like to create an app similar to Barbell Pro for Android, for practice / interest / educational purposes really. Or even as another example for database purposes, Fitocracy The problem is, I ...
2
votes
3answers
329 views

Tracking correct order details in commerce database when modifying 'products'

I am implementing an ecommerce database. This is slightly different than most as the 'products' that are for sale are for services provided. For example a user (vendor) of the system may define a ...
0
votes
1answer
172 views

Relationships in a Chen ERD

I am working on a Chen ERD to model our organizations merchandise. Our central entity is a Style. We have supplemental entities of Color and Season. I am defining our assortment as the ...
5
votes
4answers
1k views

When should a database table use timestamps?

First a note, I thought maybe this question belonged in the database exchange, but I think it is more broadly related to a programming solution as a whole than to databases. Will move to database ...
4
votes
1answer
544 views

State pattern vs Inheritance

In the following image for the State Pattern from Applying Domain-Driven Design and Patterns: With Examples in C# and .NET I'm trying to persist the SalesOrder entity into the database. Normally I ...
2
votes
3answers
143 views

Refactor an old model of a LastUser Modification on every Object?

I have a software requirement, that needs to store the user that made the last modification to another object, for every object (assume that all objects are already mapped and into a BD). So we have ...
0
votes
1answer
2k views

Practical mysql schema advice for eCommerce store - Products & Attributes

I am currently planning my first eCommerce application (mySQL & Laravel Framework). I have various products, which all have different attributes. Describing products very simply, Some will have a ...
-4
votes
2answers
2k views

Best way to keep large amounts of data (no relational database needed)

I would like to know what should be the best way to keep data on a server related to the following points: Chat logs Heavy text content User references like amounts of ids (1,4,14,524,23220,...) ...
3
votes
5answers
246 views

Violating SQL principles

I have to write a decent size database, 1GB more or less. I have only taken an introductory semester regarding SQL databases. During the course we learned that the relational model under SQL has no ...
0
votes
2answers
138 views

A hint to create a db schema

I need to realize a database in this mode: TABLE restaurant with "classic" fields of a place: address, street number, phone, website, etc etc etc. I need to indicate also the days of opening // ...
2
votes
2answers
120 views

How to represent compound elements within a database?

I need to design a database table that holds the data of programmers work hours. The problem is that I have a compound type - two pieces of data belong to a category. Specifically, the work hours ...
1
vote
1answer
2k views

What is the difference between UML Data profile and ER model?

I have been asked this question and even after a lot of research, I cannot find the answer. When I design a database using Entity relationship (ER) and then re-draw it as UML class model (extended ...
10
votes
1answer
359 views

Preferring Database Normalization vs. Schema Transparency?

A new requirement has surfaced on an old codebase, that basically enables direct (internal) communication between two formerly not-directly related classes of users (stored in different tables with ...
0
votes
1answer
103 views

Do we have any special design considerations for maintaining anonymity in an application?

This is an open-ended question which I am analysing while doing some fun projects in leisure. Websites like Google, Facebook do store user activities and perhaps sell them or use them for ...
2
votes
1answer
69 views

what's the proper way to organize code that straddles multiple objects?

Let's say I have the following classes: person, widget, foo_bars. A person can have multiple widgets and foo_bars. When an admin deletes a person, I want my code to do a cascading delete... and ...
3
votes
1answer
165 views

What is the standard practice for extracting an auto-incrementing primary key?

I have some objects that will be created an entered into the database. There is no guarantee that any of their fields will be unique except for the auto-incrementing primary key. I would like to be ...
4
votes
4answers
392 views

eCommerce use case: removing a product

I am starting to design the database schema for the eCommerce element of a web service I am creating. The thing I'm trying to get my head around is how to deal with the use case of a seller (user of ...
2
votes
2answers
607 views

MongoDB: Is this a good schema design?

I'm working on a Meteor app which lets users create events and assign them crew members. I've played around with MongoDB before and made some experiences along the way, where for instance my first ...
4
votes
4answers
352 views

Typed search over complex model. Possible CQRS / NoSQL solution

Given the following relational model: I need to implement a typed search for hotels, which should be able to query on name, city, country, category, hoteltype, roomprice, customtype, room custom ...
1
vote
2answers
163 views

Should a DR & CR entry both be created or a single-entry accounting is fine

A new project has an amount. It is always related to a client. One client can have many projects. And he may pay in steps for a particular project. In order to keep track of client payments, I've ...
2
votes
1answer
169 views

Design Application to “Actively” Invite Users (pretend they have privileges)

I am designing an application where users message one another privately, and may send messages to any Entity in the database (an Entity may not have a user account yet, it is a professional database). ...
2
votes
1answer
133 views

How to handle when one user deletes the object while the other has it opened in edit mode in a webapp?

I am developing a CRUD type web application. It is a project management system having projects, milestones, tasks, employees etc. Each employee has his/her own account to login and view the system. ...
0
votes
1answer
125 views

How to store Status for Reservation table

I have a reservation table (a user fills out a form to request a reservation). It has two parts that need to be confirmed. isReservationAccepted (decline,accept,waiting) and hasReservationBeenSent ...
3
votes
1answer
2k views

Database design/relationship for threading messages

What's the database design or business logic for creating an app for messaging between users? I am having difficulties with choosing how to approach the relationship between each Conversation of User ...
2
votes
3answers
473 views

db design: circular references for the-one-of-many case

There are many questions about circular references around (if they are bad or not) but this one will be concrete. Consider the following case. There are two models Image and User. Users have ...
2
votes
3answers
317 views

Which database schema should I pick?

I am planning a database scheme and I hesitate between two designs, which one should I choose? For the purpose of the question, assume we want to prepare a database scheme for an application managing ...