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
0answers
34 views

database architecture for ecommerce app

I’m creating an app that allows users to post, sell, and purchase items. Every item has a count integer attribute that is set by the initial user and which is reduced by n with each purchase. When ...
2
votes
1answer
67 views

Building an abstraction database

The Scenario I have a Classic ASP project with a SQL Server backend that I need to port to ASP.NET MVC. The legacy site makes liberal use of schema definition analysis in the application itself. ...
4
votes
2answers
175 views

Is it okay to use a non-ID column for foreign key reference?

I have my tables as below Role(Role ID, Name, Other_Columns) Command(Command ID, Name, Other_Columns) I have association table RoleCommand. Is it okay to have RoleCommand(RoleName, CommandName) ...
0
votes
4answers
84 views

Should database tables be separated by concern?

My question is related to when should a database table be broken into multiple tables with relations? However, I have a clearer one-to-one relationship than in that question. If I was designing the ...
2
votes
3answers
122 views

How to simplify this Role and Permission management design?

I'm working on a web application that has users with multiple roles and each user can perform multiple operations, based on user's role, and the permission level the role has on the operation. I came ...
2
votes
2answers
58 views

Use one query that fetches redundant data from linked tables or multiple queries and join them in code?

I have three tables with a chain of two one-to-many relationships. There are two ways I can think of of fetching the data based on the primary key of the first table. JOIN each table (or LEFT JOIN ...
2
votes
2answers
96 views

Is a one-to-none relationship one-to-one or one-to-many?

I am working on an existing database design. There is one table in which each row is created with only half the columns populated (the remainder are initially all NULL, except for an is_populated ...
0
votes
1answer
86 views

Row level security with multiple rules

I need some assistance with designing a system which implements row-level security that could have multiple conditions that need to be satisfied. Context Suppose I have a user. A user can belong to ...
4
votes
2answers
103 views

Is my database supposed to contain the links to the files, or would it contain the files themselves?

I'm trying to better understand how to use a database in a personal website. I have a set of lists, like my shopping list, to-do list etc. Currently, they are all stored in text files with hard-...
4
votes
1answer
50 views

Reproducible uniqueness

So, I have a bunch of calls that are all being generated with a UUID1 throughout the day. At the end of the call the call is processed and some metrics around that call are generated and stored in ...
0
votes
1answer
47 views

Use redundant data in database design when have multiple relation

In my web application users can submit their advertises and buy package for advertise (packages are used for priority of showing advertises) Database schema is like this: **Package: id, title, ...
5
votes
2answers
69 views

How to add users to a mapping table?

In the accepted answer to "Encrypting stored data for multiple unique users to access information", @lserni describes a mapping table for users. But among the operations for the table, they didn't ...
0
votes
1answer
35 views

DB Data structure for .Net sequenced list with insert, delete, move

The problem is maintaining in .net a list of rows that have a specific sequence where rows can be inserted, moved and deleted, and being able to save and restore that sequence from disk. In memory ...
0
votes
2answers
63 views

Should we sanitise and clean data or only display it in the expected format?

So my problem is, I have users entering their data(names, address) in a mixed form- some with first letter in caps and some without, some inserting more than necessary spaces and so forth. Also, ...
6
votes
4answers
239 views

Is it too verbose to always have 'modified' time stamps on all entities within a database?

The question can be summarized as: In a database (regardless of type), would it be considered a good practice to always include updated, created (and possibly deleted) properties, for all entities,...
0
votes
1answer
43 views

designing a data warehouse: action or status based

I am designing a data warehouse for a sales platform. There exists a group of users that offers and group of users that demands entities. The production data base holds two sources of information: ...
0
votes
4answers
49 views

Including record data in the ID number

I'm working on a project that requires assigning ID numbers to records about people in a database. Each person may have many records associated with their ID number. There was a discussion about ...
6
votes
5answers
130 views

DB design: what is a good way to handle properties for game?

I must persist handful of properties for several games in relational DB. No other type of DB can be used. Number of games will be around 4-5, and number of properties is (for now) kind of small, but ...
3
votes
2answers
91 views

Micro-service database management?

Most people say when building micro-services, that it's best to have seperate databases for each of the microservices. E.g: User-microservice: DB - User userId name Etc... Review-microservice:...
0
votes
1answer
44 views

User, user Role and user Info, data base design problem

I am creating an application that will manage job interviews and help the employers to choose the job seekers based on a ranking system that's based on some user info. I've created a database that has ...
1
vote
1answer
104 views

A large Oracle database with no foreign keys [closed]

I am struggling to understand the design of an Oracle database that I have been working with for the past 9 months. The database is for our business management system and I work with it mostly for ...
0
votes
0answers
36 views

Design a System to save a data that will be missed from another system

I'm building a middleware system where I'm saving the data from VERY slow API, The system behind this API works like this It produce a data blocks, lets denote a datablock by A. While A is used by ...
0
votes
0answers
24 views

What is good practice when saving textual data which contains meta information?

This is bugging me for quite a while and even though I decided to do it in a certain way I am still no convinced that I decided well. Here is the background to it: One of the core features of my ...
1
vote
1answer
95 views

What Graph Theory approaches or algorithms are useful for designing a House?

I have a set of data about a family, and I want the software to find a house design that fits the data (which includes stuff like preferences and whether they want a pool. The current way I am going ...
2
votes
1answer
71 views

Designing Storage for Different Time Systems

I've been considering doing some kind of application that allows users to track events in mythical settings. However, not all mythical settings allow for normal times. What if one user wants star ...
0
votes
2answers
122 views

Create a separate table for employees work hours?

I have a table with employees information: id, active, first_name, last_name, gender, id_number, address, picture, password, comment Every employee has an entrance time and an exit time for each ...
1
vote
1answer
47 views

Database Object That Reads From Two Tables and Writes to One

I am updating a database design that must be compatible with old and new applications. My goal is to make a design change which minimizes impact on the existing code. The Design Change Currently our ...
2
votes
1answer
63 views

Best approach to store huge amount of denormalized/dynamic data with relational database

Here I have a very basic ILS (Integrated Library System) done a long time ago (made with C#/SQL Server), in production for years. But now I have a demand of make this system MARC 21 standard compliant,...
1
vote
2answers
67 views

Conditionally show questions based on previous answers in a database driven survey

I'm trying to design survey-type application that uses user responses to determine what the next question should be. For simplicity's sake, question types can be either combo boxes (they have a set ...
1
vote
2answers
51 views

Creating a web application with full text search on dynamic data

Even after thorough requirements engineering we end up with users wanting to attach 'notes' to their otherwise well-structured data records, in other words: arbitrary key-value pairs. Their primary ...
0
votes
1answer
56 views

The best approach to make a report based in dynamic data

I have an Asp.Net MVC application where I need to create a report. This report will show some data about an entity (activities), but only activities of ONE specific type that will be inserted in the ...
2
votes
0answers
81 views

Logging Database Changes

What is the proper way of storing a changelog, similar to git, for data in a database. Currently, I have it set up so that every line in a table is given a unique string id that is used to ...
1
vote
0answers
60 views

Saving tabular, spreadsheet-like data in a relational database?

I'm hoping this question isn't too broad or too prone to opinionated answers, since I could really use some pointers. I'm trying to come up with a way to persist tabular, spreadsheet-like1, data in a ...
4
votes
2answers
105 views

Database design, processor vs inconsistency

I was wondering, if I have a table that stores, lets say, a user's transactions, then when I need to fetch the user's current balance I would have to go and use SUM() like SELECT SUM(value) FROM ...
2
votes
2answers
48 views

How should I structure a DB schema to include multiple market segmentation data?

A client has recently expressed a wish to assign their market segmentation labels to their customers, so that they can sort by market segmentation in their dashboards ("show me all the VIP customers ...
1
vote
1answer
59 views

How to associate similar data with different tables in a database?

My application has users and ads. A user has a unique email. An ad belongs to a user (so, the ads table has a user_id FK, that is simple). Every ad can also have multiple emails and phone numbers ...
1
vote
1answer
61 views

How to model the database tables for calculating on peak and off peak usage data

I am using MS Sql Server and my system contains power usage data in one table as timestamp and values for KW(two columns). This data is collected at 1 hour interval. At the end of the month I need to ...
9
votes
3answers
323 views

How to share a code base among 10+ projects and minimize pain?

I have a number of applications that share the same data in the same database. To try and minimize redundancy of code the Data Access Layer is a shared project. This prevents each project from needing ...
5
votes
6answers
182 views

Should I add a “boolean meaning” to another column?

Is it a good/bad practice to add a "boolean meaning" to other fields? For instance, let's say I'm modeling events, that might be recurring or not. I can have: A boolean column that holds if the ...
3
votes
1answer
99 views

Getting posts from users that are being followed: two different implementations

I have two different implementations for retrieving posts from the users someone is following and I want to know which one you would recommend using. First I'l show you the tables, and then the two ...
0
votes
1answer
111 views

Entity Framework Code First, C# class separation and EAV

I'm in the process of redesigning a portion of my ASP.NET MVC application. I'm currently using Entity Framework 6.1 code first approach. I've been reading as of late that (Correct me if I'm wrong, I ...
1
vote
3answers
105 views

Are crow feet notation and uml notation same?

Does crow feet lies under the sub category of the UML notation or it is a whole other approaches. As far i have seen only difference between two of them is that crow feet uses crow symbol |------<-...
0
votes
5answers
131 views

Database design - When should a table column actually be a table?

Compare Database #1 and Database #2 below DATABASE #1 POSTS--------------------------------- |___Id________Category_Id______Body__| | 1 "For Sale" "...." | | 2 "Requests" ...
3
votes
2answers
123 views

Proper design for models

I have a class called ReplaceRule this is used by 2 classes DocumentRuleViewModel and SectionRuleViewModel as a List<ReplaceRule> in both Question 1: In my database implementation I have 2 ...
1
vote
3answers
47 views

Archiving data by semester

The problem is similar to this question, but not a duplicate. What I'm looking for is a better solution than my current one. I'm building a web application for a department in my university, and due ...
1
vote
1answer
63 views

My web app uses a number of photo hosting APIs. What would be an effective database design to store their limitations?

For the sake of an example, let's say I'm using Picasa, Flickr and 500px APIs, and that each of the services has limitations on these simplified parameters: overall account space individual file ...
0
votes
1answer
42 views

Full data history, current values can be rebuilt ignoring provisional or invalidated changes

I would like to create a website for a local school and then offer it to others. The school's main headache at the moment is capturing student marks and comments at the end of each school term. The ...
5
votes
3answers
403 views

Is it ok to have a class that only has boolean properties?

Scenario We have a class Vehicle, this class contains some properties to define the object such as a CarBrand, TransmissionType, Color, etc.. A car (vehicle) also has options, these days a lot of ...
1
vote
0answers
34 views

Creating a new table referencing older one vs just creating a column

I'me developing an iOS chat application and working on the db design for the contacts feature. There are 2 kinds of contacts- Unsaved Contacts - Contacts with who you've conversed with, but not ...
1
vote
0answers
26 views

Offsetting high usage in a multi-tenant database environment?

I'm writing a research essay over distributed cloud-based systems, and one of the topics that I'm trying to touch on is potential solutions to high database utilization in a single-database multi-...