The logical model and/or physical design of a database.
0
votes
1answer
9 views
can i have password for my mysql table
is there a way i can set password on a table i am creating in mysql. i am working with rather sensitive data , and i have to use mysql on a server on which i am obviously not the admin. so people ...
2
votes
1answer
33 views
Placing a uniqueness constraint on a date range
I have a table reservation with columns like roomno, startdate, enddate with a primary key on (roomno, startdate).
How do I place a constraint on the table such that bookings are not allowed to ...
5
votes
2answers
52 views
Using schema + Filegroups or entirely separate database(same instance) to partition some big tables?
I have the following challange:
I have a staging database which holds a few big tables (on SSD) but I start to run out of space on the SSDs. I use SQL server 2014 BI-Edition (= Standard Edition for ...
1
vote
0answers
11 views
Database Designs Alternative to Polymorphism in Establishing Relationships
I'm working on a database which handles replacement of parts in a machine.
Rules
A machine has many components
A machine has a set of assembly instructions
Many components have many part (screws,
...
0
votes
0answers
12 views
Tag system for multiple Item types and groups Schema
I have a groups table and users table, and a many to many relationship between them.
In my app, there are different modules, like question, product,suggestion , etc.
Users can create ...
1
vote
0answers
15 views
pub/sub database schema
I am having a hard time creating a pub/sub database schema with stateful metrics.
Saying it aloud it sounds easy enough:
"There are many organizations that will subscribe to checklists that
are ...
0
votes
0answers
39 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
13 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
40 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
23 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
32 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
57 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
24 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
74 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
26 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
116 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
48 views
What are the general practices considered to constitute a good user id? [closed]
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
32 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
32 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
71 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
152 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
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) ...
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
49 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
48 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
31 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
107 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
60 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
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
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
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
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
1answer
29 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
28 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
38 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
47 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
...
