The logical model and/or physical design of a database.

learn more… | top users | synonyms (4)

2
votes
1answer
30 views

Optional Parent-Child relationship

I have a problem modeling a relationship whereby the child may or may not be related to a parent. For example. You have a Band database with a Tour(parent) table, and a Gig/Concert(child) table. Now, ...
2
votes
2answers
63 views

How can I represent a list of words and related words in a relational structure?

I have a list of header words and words as below and I am trying to understand how best to represent these in a relational table: Header Other Words Words abandon abandoned, ...
0
votes
1answer
77 views

Products with different prices in an order

I have a "classic" database schema for order and products: Orders ------ id etc. Products -------- id name etc. Order Products --> Pivot table -------------- id --> some people don't like ...
2
votes
0answers
31 views

Could I use both UUID and Sequential ID or is it bad design

Sequential IDs have pros and cons. UUIDs have pros and cons too. Why not use both for each table? (Let's say I have -and will have- no problem about storage space) I could use: UUID for exposition, ...
2
votes
1answer
46 views

Conditional relationships? One-to-many relationship using a pivot table for only a type of entity

I have the following tables: users: - id - name - user_type_id (references id on user_types) neighborhoods: - id - name homes: - id - house_number - neighborhood_id (references id ...
1
vote
3answers
62 views

Relational tables with same content, custom solution

So let`s say that I am building an app where I can add comments for FOO. Normally I would have something like this: Table foo and table foo_comments, where foo_comments contains id, foo_id and other ...
1
vote
3answers
131 views

Avoid repeated traversing of hierarchical data when parsing a big tree frequently

I have a hierarchical/tree data structure in Oracle/RDBMS where I need to traverse the tree frequently. The tree is huge and therefore parsing it every time will not be efficient. A better way could ...
16
votes
4answers
19k views

What is an audit table?

What are audit tables? How are they useful? I came across them reading this article.
0
votes
1answer
26 views

Checking for BCNF in a relational database through FDs?

Okay so I know that for BCNF, when listing all non-trivial FD's that everything to the left has to be a candidate key. So I have this relation: Person(Id, TFN, Name, Phone) Where Id and TFN(Like ...
10
votes
2answers
11k views

How to choose a collation for international database?

I'm designing a database which will store data in different languages (using UTF-8), so I think the best way to display the query's results is ordering it according to the user's language during the ...
0
votes
1answer
132 views

Zerofill Auto Increment - mySql

Ok, So I'm working on an app and I can't remember, for the life of me, why I set a zerofill on an Auto_Increment column when I created the database. And I couldn't exactly find any compelling info ...
1
vote
1answer
125 views

What Is the Next Best Ingredient to Add?

I am building a program that allows users to optimize their grocery shopping so they can make the most recipes using the fewest ingredients. One of the features of this program is a function I’m ...
3
votes
1answer
74 views

Basic question: Simplest way to share company databases

My company is looking to access large quantities of data from another company we have good relations with. Currently we are using a web interface to interrogate their information, however it is quite ...
0
votes
1answer
65 views

Raise Multiple Exceptions Postgres

I am using Postgres 9.5, and I am trying to create exception handling in a stored procedure/function. I can't quite figure out how to raise the error should any of the 'INSERT' statements fail. If ...
1
vote
1answer
112 views

Setup MySQL Master to Master replication/automatic failover when each Master also has a Slave

Thanks in advance. I have always worked with SQL Server and never really had any exposure to MySQL. I now have to determine the preferred setup for MySQL when there are four servers involved. Ideally,...
1
vote
2answers
217 views

PostgreSQL surrogate keys: use sequence/bigint or OID?

I'm looking at a need for surrogate keys in tables I'm designing on a PostgreSQL-derived DBMS (Citus). Would OIDs suffice? Is there a downside to using them instead of creating a bigint field and a ...
0
votes
0answers
33 views

DB design: Store data VS more complex DB changes

This is my current DB structure. -- -- Table structure for table `prods` -- CREATE TABLE `prods` ( `prod_id` int(11) NOT NULL, `prod_name` varchar(255) NOT NULL, `prod_aaa_id` varchar(255) ...
0
votes
2answers
1k views

Which is the redundant relationship in this ER Diagram and why?

Which is the redundant relationship in this ER Diagram and why? A few of my friends and I have been trying to figure it out but keep going in circles. where the attributes of each entity are as ...
5
votes
2answers
78 views

Postgres Update Limit

I'm curious if Postgres has anything in place where I could limit the amount of rows kept for a ID. For example, say I have a users table and login_coordinates table. Every time the user logs in, ...
6
votes
1answer
89 views

Is my implementation of type/subtype design pattern (for mutually exclusive subclasses) correct?

Introduction In order for this question to be useful for future readers I will use the generic data model to illustrate the problems I face. Our data model consists of two entities, which shall be ...
3
votes
0answers
68 views

Personal Finance database review

G'Day, I found that someone had started on this topic on personal finance, I like to go further using a good personal finance plan I found on the internet which I would model. In advance please ...
0
votes
0answers
75 views

Using the same tables for multiple many to many relationships

I currently have a project where I need multiple Many to Many relationships some of them are roughly and I started to wonder if using an extra column on the table to specify the table it's related to (...
0
votes
1answer
33 views

PostgreSQL values to unique dictionary

I have table with one billion rows and more than 50 columns. I need to reduce size and speed up queries, backup, exports, etc. Some columns contain f.e. only hundreds of distinct values which are long ...
0
votes
3answers
109 views

Best name for link table [closed]

I was wondering what the best name for a linked table in this situation (or any) would be. I've searched for this, but could not find any concrete examples. For example, I have the following tables (...
14
votes
3answers
1k views

What is the optimal data type for an MD5 field?

We are designing a system that is known to be read-heavy (on the order of tens of thousands of reads per minute). There is a table names that serves as a sort of central registry. Each row has a ...
1
vote
2answers
107 views

Need advice on database normalization

I am working on a basic blood bank database on Oracle. I would like to create a completely basic normalized table and then I will add more details to it. I have done it to the 2NF and I will further ...
-3
votes
1answer
30 views

Database engine disk access [closed]

Do you know what is the difference between the database engine get rows in a table without and with use of an index in a disk prespective. They way the engine access the table in disk is the same when ...
10
votes
5answers
1k views

How to hide/disable tables without dropping them to check redundancy?

I have to maintain and extend an old legacy system which contains webservice methods and database tables that are no longer used. Since I’m not entirely sure that the tables are really redundant, I’m ...
2
votes
0answers
101 views

High Performance Relational DB for Fast Read and Fast Write [closed]

I am looking for the best database solution for a high availability, high performance solution that meets the following requirements. Data being read, written and updated is very simple, and small. ...
20
votes
4answers
11k views

Is nested view a good database design?

I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect ...
0
votes
0answers
37 views

Should I break up this MySQL table?

Existing table (more or less). It has about 1 million rows: id article title stage 1 zipfile name stage 1 delivery date stage 1 checked date stage 2 zipfile name stage 2 delivery date stage 2 checked ...
1
vote
1answer
37 views

Database design for surveys and experiments

I have only a bit of experience in working with databases, but rather theoretically during my studies and not so much practically. I was wondering if someone could help me figuring out how to design a ...
5
votes
1answer
154 views

Data inconsistency prohibition if a table refers to another via two many-to-many relationships

I have following database design (by => foreign key constraints are depicted): Company[id] CompanyRealm[id, company_id=>Company.id]many2many Project[id, company_id=>Company.id] ProjectRealm[id, ...
7
votes
3answers
553 views

Implementing versioning system with MySQL

I know this has been asked here and here, but I have the same idea with a different possible implementation and I need some help. Initially I had my blogstories table with this structure: | Column ...
2
votes
1answer
236 views

How to model an “is a” relationship in an ERD using Crow’s foot notation?

I have to create an entity-relationship diagram (ERD) for an assignment. I am doing this in a particular drawing tool with the Crow’s Foot notation. There is however one thing I cannot figure out: ...
0
votes
0answers
25 views

improve performance with schema design, a lot join

I'm making a custom cms system for my client, with postgresql, nodejs ... have problem with slow performance issue, when doing select data . (and it is still on localhost/mac nginx and just few data, ...
3
votes
2answers
79 views

Enforce uniqueness across optional attributes for an entity

I have an entity that has multiple attributes, and these attributes are themselves entities. No two entities may have the same attributes, so there needs to be a uniqueness constraint across them. ...
3
votes
0answers
50 views

ERD for a Contract between a Writer and a Publisher: Is it correct, can it be simpler?

I created an entity-relationship diagram (ERD) for a book publishing business context that I will detail bellow. Entity types The entity types of interest are: Writer Work (as a book) Publisher ...
1
vote
1answer
34 views

One to one relationship naming conventions

I'm designing an ER diagram with MySQL Workbench. I have two tables with a one-to-one relationship. Both tables have one column as primary key, but they don't have the same name. Take a look: But ...
6
votes
2answers
230 views

Best data modelling approach to handle redundant foreign keys in relational model

I am looking for advice on the best relational modelling approach to store surveys, questions, and responses. I am looking for which of the two approaches below looks best, or an alternative approach ...
0
votes
2answers
41 views

Is one-to-one relationship required?

I have these three tables on my database: There is a one-to-one relationship between Code and Aggregation table (an AggregationId is always a CodeId. And of course, a CodeId is unique). Note: A Code....
0
votes
0answers
31 views

Intergrated or seprated ? One row with multiple data or multiple rows with one data [duplicate]

I'd like to create a Friend system like facebook What type of DB design do you suggest ? 1. id | userid | friends_id ---------------------------------------------------- 1 ...
22
votes
3answers
9k views

How Does Table Partitioning Help?

I am having difficulty to grab the idea of pros and cons of table partitioning. I am about to start work on a project which would have 8 tables and one of them will be the main data table which will ...
2
votes
0answers
60 views

Database design - reusable elements (templates)

Suppose I have a table that stores items of a very specific type. Items of this type are stored in a bank for future use, and they can be changed specifically for a situation without being changed in ...
5
votes
2answers
77 views

Stable triangle relationship?

I have a table that represents a specific type of action. Let's call it [ACTION]. An action can be performed by a user ([USER]), so I have the relation [USER] 1:N [ACTION]. The user can have ...
0
votes
1answer
47 views

Foreign key as part of composite primary key or not?

I'm designing a relational database. This is what I'm trying to design: I will have production orders and products. A production order will have one product, but a product could be on zero or more ...
0
votes
0answers
24 views

MySQL Design for bulk inserts

Consider the following table structures as below, these tables can go beyond millions records received_goods rg_id int(10) PK (auto_increment), name varchar(128), ... storage_details rg_id int(10)...
10
votes
3answers
817 views

Blockchain (Bitcoin) as a database?

I was reading this BBC News article and the following excerpt, caught my attention. It sounds like Always On Availability Groups or High Availability Mirroring, maybe with security automatically ...
1
vote
1answer
50 views

Create a stored-procedure to insert multiple rows on a junction table and other tables

I am new to Databases and Design and needs some help on a INSERT issue. I want to create a procedure that will insert multiple values to different tables, I need to make sure it stays atomic so I need ...
16
votes
7answers
4k views

Is polling the only way for updating app's data from a database?

An application needs to have data as more freshly updated from a database as possible. In such a case, is there any other way for getting the data, besides of a timer based requesting (polling) the ...