For questions about structuring the data within a database. How to lay out tables, whether to use a relational DB or not, etc.
0
votes
0answers
18 views
Using fixtures to maintain static database assets
I have a pattern that's something like this:
Create a database model UsagePlan which stores how many usage tokens a paid customer receives in exchange for its respective monthly fee. Through my ORM (...
10
votes
2answers
1k views
Do multi-tenant DBs have multiple databases or shared tables?
Is a multi-tenant database:
A DB server that has a different (identical) database/schema for each customer/tenant?; or
A DB server that has a database/schema where customers/tenants share records ...
-2
votes
1answer
92 views
DB Constraints - Business rules or DB rules? [closed]
When we set up few tables we will probably have some relationship between these rules.
We can enforce these in the database, by adding constraints, or we can enforce them in the application.
I've ...
1
vote
1answer
91 views
Multiple databases or one single database
I've seen a few answers on this already but nothing that really applies to my particular situation.
I'm going to be building a mobile application, so primarily phones, tablets etc. Probably using ...
0
votes
2answers
173 views
Need advice on making my own custom URL shortener?
Due to some issues with other shorteners like goo.gl (disabling my links for example) I want to create my own URL shortener.
I am looking to have a single table that will contain the following ...
0
votes
1answer
56 views
Database Architecture for SaaS application [closed]
I am designing a SaaS application where thousands of users will be using this application. This application will do lots of data crunching and analytics. I am considering creating a main database ...
0
votes
0answers
55 views
DB design for a scheduling system
I am currently designing a system to support our employee staffing process. I collect weekly preferences from employees, and feed that into a system for staffing. Availabilities can be defined as ...
3
votes
1answer
89 views
What is an efficient design to store variables for different product lines in ER database?
This question is about refactoring existing database design.
My data flow is
User generates some data for product lines A, B, C
Data is saved into the database once
Data is later retrieved multiple ...
3
votes
2answers
96 views
Work with user content edits history: storing differences vs data duplication
It may not be clear so I'll develop the idea:
The point is to develop a web interface to write down notes (and anything you like), and track its evolution. My question is then: how can I store such ...
0
votes
1answer
40 views
Store active item in an array
In a NoSQL database (MongoDB), I need to store a list of creditcards for a user and to keep track of which one is active.
I came across 3 patterns:
#1 Keep the active property in the Creditcard ...
3
votes
3answers
83 views
SQL reverse filtering system
Sorry for the non-descriptive title, I don't know how to explain what I'm trying to achieve succinctly.
In most SQL use-cases, we have records in the database that have properties, and we are trying ...
10
votes
1answer
201 views
Database Structure for 2v2 game
I regularly play a 2v2 game with 12 friends and I want a database to keep track of players, teams, scores and games, with the intent of creating a ranking system.
Since we regularly change teams I've ...
0
votes
2answers
34 views
Any drawbacks to having very descriptive table and column names in DB tables for Java web app?
I would like to know if there are any drawbacks to having descriptive names for database tables and columns. This would be for a Java based web application, and most of the times I have seen database ...
0
votes
0answers
47 views
Building a set of APIs on top of a legacy application that can also write into the source database?
OK, so please tell me if this is the worst idea ever, or if it might have some merit. Here it goes:
I have a customer who uses a legacy application that stores data in an Omnis database file (*.DF1)....
4
votes
3answers
66 views
Is it better to store usage logs feature wise or centralized?
I have to develop a logging system for my project which has multiple features. I am stuck at a choice. I could either make a centralized database and front end for everything. Or I could have one log ...
5
votes
1answer
88 views
How to implement one up/downvote per one item per logged in user while keeping low performance costs in mind?
Same system this site uses for example. You can vote down or up, but your votes are persistent between logins so you cannot cast duplicate vote, only change your vote to the opposite.
How would one ...
1
vote
0answers
55 views
Is a topological sort required here?
Say you are writing an ORM like entity framework. You write basic POCO, and then let entity framework do the job of creating equivalent structures in the database according to those objects.
This ...
0
votes
1answer
38 views
How should multi-tenancy be implemented for child resources in a SaaS database design?
In SaaS multi-tenant database design, do you think it is sufficient to include the tenant id only in top-level resources, knowing that child resources will always be parent-scoped, or should every ...
6
votes
3answers
873 views
How to avoid incrementing ID's being exposed in an API?
Looking at posts such as this on StackOverflow, the consensus
seems to be that you should hide incrementing ID's from external API's for security.
However, I'm having trouble working out a good ...
1
vote
2answers
50 views
Best way to model “Composite Skus”?
I have a SKU record which keeps track of raw materials used, pricing, cost, etc. An example SKU would be a Hamburger (1 bun, 1 patty, 1 oz ketchup, costs $11 make, priced at $4, etc).
I need to add ...
4
votes
1answer
62 views
Suggested method for “simplifying” EAV
I am working on a GIS platform that basically handles roads with features. The system uses an EAV model to store all the data in a database. The system has been around for a while and there is nothing ...
2
votes
0answers
53 views
How to set up a sql database to cater for user records, group records and default records?
Outline
I have an application that loads data from a database. I'm not talking about client data here though, I'm talking about application configuration. The database will therefore come with some ...
40
votes
7answers
1k views
What happened to database constraints?
When I review database models for RDBMS, I'm usually surprised to find little to no constraints (aside PK/FK). For instance, percentage is often stored in a column of type int (while tinyint would be ...
2
votes
1answer
38 views
Django database modelling
I'm starting a project with Django, previously I've used Yii and Rails and they have tools that will take a DB structure and build models for you based off the DB structure. From what I've read Django ...
1
vote
2answers
174 views
Storing Local Filesystem Paths in Database
I'm developing a webapp where I have sets of data stored locally on my computer and I run a tool which transforms the data and uploads it to my webapp. However, I need to be able to rerun the tool on ...
4
votes
3answers
188 views
Best Practice regarding table schema
We are discussing in team regarding best practices.
For eg. there are three tables
Users
Groups
Teams
A user will create a request to join a Team or Group or to follow a Private User.
A request ...
1
vote
0answers
29 views
Database Constraint from another table column
Let's consider the following objects :
Article which can be of different types
Equipment which point to an Article, some fields on equipment are only used for some types.
I want to add constraint ...
3
votes
1answer
127 views
How to make an item unavailable for other users while a user is viewing it in a php website?
I am in the process of developing a website for a driving school that will also act as a management app.
One requisite is that at some point the student can choose a day for the driving lesson to ...
17
votes
4answers
776 views
Reason to prefer RIGHT JOIN over LEFT JOIN
If I understand correctly, every RIGHT JOIN:
SELECT Persons.*, Orders.*
FROM Orders
RIGHT JOIN Persons ON Orders.PersonID = Persons.ID
can be expressed as a LEFT JOIN:
SELECT Persons.*, Orders.*
...
1
vote
0answers
36 views
Hierarchical data structure: pull requests + pull request reviews + pull request review comments
GitHub has:
pull requests
pull request reviews
pull request review comments
BitBucket has only:
pull requests
pull request comments
So there are three kinds of objects.
I write code ...
0
votes
1answer
166 views
Database design with loop
The database I'm currently building is pretty complicated to me. So it would be much more complicate if i try to explain so i will try to generalize. The question is at the end of the post.
So I ...
3
votes
4answers
130 views
Hierarchical structure that needs to enforce availability
The project I am working on has deep parent/child relationships that needs to enforce availability.
Imagine we are a large Worldwide Electronic Seller (Best Buy) and selling mobile phones
We have ...
0
votes
3answers
60 views
How to split a table based on its child-types
We've a table which has a bunch of columns. Let's say it represents humans. Every human has a specific type: archer-human, car-human and so on. Everytime we create a row nearly all of the fields are ...
1
vote
3answers
48 views
Predefined city/country list vs manual entry for address table
I'm trying to design a database for my POS application and have stumbled when trying to create an address table. I'm confused whether to have city and country tables with predefined values and link ...
6
votes
3answers
112 views
How to enforce the mandatory participation constraint at the many end in a one-to-many relationship?
Say I have the following ER diagram:
Enforcing the mandatory participation constraint at the one end is easy, I simply make the foreign key (school_id) in Student NOT NULL.
But how can I enforce the ...
1
vote
2answers
53 views
Modify existing column or link to what the change is?
I am making a game. In this game people can modify a value of an object. Let's picture it like so.
House is the object
price is the value
Mold is the modifier
Should I reflect changes to the price ...
0
votes
2answers
81 views
Should MySQL database tables only have a single unit of information per row?
I run an automated data collection service at work, which records measurements (temperatures, voltages, etc) from about a dozen sensors at a rate of about once a second. The MySQL database table looks ...
3
votes
1answer
190 views
Dashed line in Crow's Foot notation
I am learning about the Crow's Foot notation, and there is something that I don't understand, some tutorials shows an Identifying Relationship as having a straight line, and a Non-Identifying ...
6
votes
6answers
306 views
Disadvantages of using a nullable foreign key instead of creating an intersection table
Say I have the following ER diagram:
Now if I represented the relationship using a foreign key of School in Student, I could have NULL values (because a Student
is not required to belong to a School),...
0
votes
0answers
105 views
Is my persistent storage strategy efficient for my project's objectives?
I am trying to build a key-value store (to learn, I am a student) that would serve as a persistence layer for software applications. I have looked into the database literature and dived a little bit ...
5
votes
3answers
74 views
Valid uses for DEFAULT clause in a table column definition, besides sequence nextvalue()
In a recent question, there's a debate whether or not an arbitrary default value in the definition of a column in a table is considered having business logic in the database. As we know, if an insert ...
0
votes
3answers
129 views
A-B-C relation vs A-B, B-C, A-C relations
I't s a Database related Entity Relationship (ER) doubt:
Better to have a single table storing A,B,C as a unique identifier or a Relationship.
O better to split it up to three tables having then A,B - ...
1
vote
2answers
70 views
Design question - storing data twice
I have a polyglot database solution that includes the following components:
a management interface that consists of a web application and a document based database (either rethinkdb or mongo)
a ...
1
vote
2answers
57 views
Suitable data store for storing comments in a social media site
I have been developing a blogging site, where people can post an article or blog and others can comments on them. The site is coded in Angular.js, ASP.NET and SQL is being used a data store.
For each ...
0
votes
2answers
46 views
Database Architecture - Comprehensive history of likes/shares/etc for all accounts?
The Setup
This is a membership-based site. Each account is capable of:
Making posts (unlimited, no restriction on frequency)
Liking, sharing and commenting (on) the other posts
The goal is to ...
0
votes
2answers
54 views
Designing Table To Store Hierarchy
I have a table Company and User like below...
Company Table:
CompanyID CompanyName etc...
User Table:
UserID Email UserType CompanyID etc...
First I must tell you that, ...
1
vote
1answer
65 views
Does it make sense to have a custom textual data format?
We have a database backing an iOS and Android app whose primary data is a simple text field. We have a build tool which builds the database - taking a raw txt file which is a custom data format, which ...
0
votes
0answers
28 views
Ideal Graph Database model for number-heavy data…
I am trying to create a data storage for a large aggregate of very heterogeneous data.
Because of the heterogeneous sources and lack of normalisation across sources, the flexibility of a Graph ...
1
vote
0answers
27 views
Should I implement an intermediate Profile table now, or wait until if/when I need it?
Traditionally the schema is very simple:
User <- Post
I have written the code for a Profile model to be an intermediate:
User <- ProfileUser -> Profile <- Post
When registering for the ...
2
votes
4answers
349 views
Data first or code first?
I have seen many questions on whether to design data first or code first when designing a new application. I wonder if some have the same conclusions/ideas as me. I come from painting/digital design/...
