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

learn more… | top users | synonyms (4)

0
votes
0answers
13 views

MySQL - Database Design : Encrypting user id and creating a mask for further internal references

For a food order delivery system that I am working on, I want to make sure my user's private information is well protected; from system admins too. So I thought of coming up with this concept of ...
0
votes
0answers
5 views

Mongo Schema for large time series data sets

I'm not a DBA but a C# developer, my experience so far is with MS SQL, so please keep that in mind. I am trying to design a scheme to store sensor data and have it organized. Each TestRecord would ...
-1
votes
1answer
34 views

Singe database account for all users [on hold]

Welcome everyone, I'm wondering if one common database account for all users of web application is enough or I should create database account for each user in order to carry out some querying? Can ...
2
votes
1answer
20 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, ...
-1
votes
0answers
29 views

Any pointers on a good concise and clear text or article that describes normalization theory? [on hold]

I am looking for a text that I could pass on to a friend. Something that's short, concise and that very clearly explains the benefits of database normalization, the different forms of normalization ...
1
vote
2answers
53 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, ...
2
votes
0answers
18 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, ...
0
votes
1answer
70 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 ...
1
vote
1answer
19 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 ...
0
votes
1answer
22 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 ...
1
vote
3answers
105 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 ...
-1
votes
0answers
38 views

What are the general practices considered to constitute a good user id? [on hold]

I could not settle on a good pattern to generate my user ids for an upcoming project that could scale up in a few months. The project is about ordering food online and I need to store user ids and ...
1
vote
2answers
30 views

Dynamic script to Drop Indices of a specific column

I´m currently writing an SQL script to update our database and for that I need a script to drop indices that rely on a specific column (because I also want to drop the column). In my script I have ...
0
votes
1answer
29 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 ...
3
votes
1answer
70 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 ...
8
votes
1answer
149 views

Overcoming the SQL Server Express Edition limitation

Microsoft SQL Server 2014 Express edition has a database size limit to 10GB. Now, is that just for a single instance or the overall size that the edition will allow? Or does that means that I can have ...
0
votes
0answers
40 views

Store and work with current state of database [closed]

I have a database with product, resource and planning data. The idea is to create a copy of the current data (just a part of the whole database) and then do some kind of planning-poker with the ...
0
votes
0answers
32 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) ...
3
votes
0answers
55 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 ...
4
votes
1answer
48 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 ...
0
votes
0answers
47 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 ...
4
votes
3answers
207 views

Maintaining integrity between 3 tables when reference could be through or direct

Say we have the following 3 tables: tUsers (ID, name, countryID, regionID) ------ 1 | Joe | 1 | 1 2 | Sue | NULL | 2 3 | Bob | 2 | NULL 4 | Amy | NULL | NULL tCountries (ID, ...
0
votes
1answer
31 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 ...
1
vote
2answers
30 views

Scalable way to track who's viewed a post? [closed]

I'm building an app that needs to check who, out of millions of users, has not received a post. If a post hasn't been seen by many people, this task will be easy. But if a post has been seen by nearly ...
0
votes
3answers
106 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 ...
1
vote
1answer
59 views

Composite Primary keys and auto increment?

i'm developing SaaS app with multi-tenancy, and i've decide to use single DB (MySQL Innodb for now) for client's data. I chose to use composite primary keys like PK(client_id, id). I have 2 ways ...
-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 ...
0
votes
0answers
35 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
2answers
98 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 ...
1
vote
1answer
35 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 ...
9
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
71 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. ...
0
votes
0answers
24 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
0answers
40 views

Online text based game design - Am I linking the databases correctly?

I'm trying to recreate an old game that I used to play that went offline quite some time ago. I'm doing this as a hobby project for myself with the hope that maybe a few hundred people might enjoy it ...
3
votes
2answers
75 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. ...
1
vote
0answers
19 views

Database Design for Sports Stats

I am designing tables for tracking statistics for multiple sports like basketball and football. So my question now is - would it make more sense to design a table when both data for basketball and ...
5
votes
1answer
134 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, ...
1
vote
3answers
55 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 ...
0
votes
0answers
27 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 ...
0
votes
2answers
36 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 ...
1
vote
1answer
28 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 ...
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 ...
1
vote
1answer
42 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 ...
3
votes
0answers
45 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
44 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. ...
2
votes
0answers
49 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
67 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
19 views

Access Web App query 3 Tables with aggregatios - Need a workaround

I'm designing an Access 2016 Web App for inventoring our computers. The problem I have is with a query where I want to get the CPUCore-amount and RAM-Amount for each PC. The computer-value in RAM ...
2
votes
0answers
34 views

How should I use DynamoDB table

I have a list of items. Each item is attached to several urls and each url has a score number. So I have something like this : How should I organise this in dynamoDB? How should I format my Json ...
0
votes
0answers
20 views

Modeling question - Meta and instance

In a relational DB I'd like to model this: every customer_type has one module_set (1:1) a module_set has several modules (1:n) every module is different, and the user can activate them & reorder ...