For questions about structuring the data within a database. How to lay out tables, whether to use a relational DB or not, etc.
-3
votes
0answers
18 views
Need recommendations of Data Dictionary enforcement tools [on hold]
We are currently building a data-dictionary at our company, and I am researching tools to accomplish that.
Ideally, we would be able to integrate it with our CI process (We use TeamCity) so that ...
1
vote
1answer
37 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 ...
3
votes
1answer
47 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 ...
1
vote
0answers
40 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 ...
30
votes
6answers
667 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
26 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 ...
0
votes
0answers
37 views
What are some open source database engine that one can go through to understand the internals of a database engine
My goal is to understand the internals of a database engine, specifically I would like to understand the internal data structure representation and the common engineering patterns for implementing ...
1
vote
1answer
111 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 ...
3
votes
3answers
163 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
24 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
113 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 ...
13
votes
4answers
579 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.*
...
0
votes
0answers
29 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
113 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
127 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
56 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 ...
0
votes
3answers
44 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 ...
5
votes
3answers
96 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 ...
0
votes
2answers
48 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
76 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 ...
1
vote
1answer
102 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 ...
5
votes
6answers
284 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
101 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
71 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
122 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
63 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
45 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
41 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
41 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
52 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
21 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
336 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/...
1
vote
2answers
111 views
Use (numeric) IDs over names as unique key?
I have a set of data (assume they are objects) with unique immutable names, like this:
class Datum {
final string name
// other fields
}
Considering that:
I don't need to support rename. (...
1
vote
2answers
73 views
General approach to an interface that will resolve a dependency to a database library
Background
I often write software for systems that are responsible for testing manufactured products. For every product that gets tested, we have to generate a report for the test-results. The way ...
0
votes
2answers
68 views
Using a class for a collection element, with methods to access other collections
I'm hoping for a sanity check in my design thinking.
I'm working with a small team on a website based on a MongoDB database. There are several collections in the DB -- for example, one representing ...
2
votes
3answers
108 views
Database design for a role based access system?
I'm considering different designs for a generic role library gem.
The first is based off the Rolify gem but with a has_many through: relationship instead of HABTM. HABTM tables are "headless" in ...
6
votes
1answer
239 views
How should I design an ordered list resource in a restful service?
I've run into this same problem over and over again and I haven't found a solution that I really felt was optimal.
Say in an app, you have an ordered list and you let the user change that order by ...
1
vote
1answer
99 views
How can I model IS-A relationship on a ledger, when having different classes of products?
This is a question on how to model database entities and relationships when it comes to storing persistent data and dealing with differently-handled types of products.
Use Case
Company sells various ...
2
votes
1answer
116 views
Given an entity that can have a parent, or can stand on its own, what is the best way to represent it in a relational database
Lets assume we have to model the following entities:
Application : Application submitted by users
Application Study: the study to determine if the Application should be accepted
Consultation : A ...
0
votes
0answers
26 views
Is mixed collation in a database a problem?
I have a database where some tables are Collation utf8_general_ci and some tables are Collation utf8_bin. Is this an issue that will cause unexpected behavior, or is it just a matter of performance?
1
vote
3answers
123 views
Is there a design pattern which describes separated join-only views and format-only views
When I'm building views in SQL, I tend to do one of the following:
Views that contain logic and criteria for selecting and joining records. I.e. "what records am I interested in and how do they fit ...
1
vote
0answers
96 views
How to design a NoSQL database solution for my application
Greetings to all.
I am creating an online directory for my company using NodeJS. Our account supports hundreds of applications of the client, and each application has certain parameters such as ...
1
vote
2answers
62 views
How to structure and connect my “price” table to existing relational database table structure?
I have several tables: product, category, addon in various relational relationships.
I structured it this way because: I have many products that belong to their own specific category. For each ...
3
votes
4answers
126 views
When keeping product price change history, what are the pros&cons of keeping track of price change events only?
When tracking something like pricing history, in my relational database, what is a recommended way to store pricing changes? For example I can:
keep price change events only (aka a single date ...
2
votes
1answer
34 views
Data architecture for digital marketplaces
I am building a digital marketplace app. I would like to know what the best practice design patterns are for the backend (No SQL) object store.
The primary characteristics of the app are as follows:
...
10
votes
2answers
2k views
Who designs databases in web development? [closed]
In the context of web development, who designs databases? Despite a whole host of information associating back-end web dev with server-side processing, data modeling, and the like, the database design ...
6
votes
3answers
173 views
What to name surrogate key when logical name is already taken by the business key
What name can you give the surrogate key column in a database table when the convention suggested name collides with that of an existing user field business key?
For example, if I am creating a ...
1
vote
1answer
112 views
Database design for a daily maintenance tool
I'm thinking about the right database design for this requirement.
It's a manual maintenance check. A company can have a maintenance contract comprising HW checks, Sys checks, both or none. The part ...
1
vote
2answers
102 views
Performance: One table vs multiple tables (generated programatically) for the same logical entity?
I want to ask for any suggestions for an architecture I can implement for a Java app I need to create (initial thoughts below).
It is supposed to be a local Swing application for tracking financial ...
