The logical model and/or physical design of a database.
1
vote
0answers
13 views
What is the difference between (Superclass/Subclass) Total and Partial Specialization?
What is the difference between total and partial specialization of superclass/subclass (or supertype-subtype) structures in enhanced entity-relationship modeling?
0
votes
0answers
22 views
Get rank of users based on dynamic points
I have this db structure for my ranking system.
users table
id username
1 alex
2 john
points table
id name description value
1 foo bar 100
2 bar foo ...
0
votes
0answers
18 views
Table structure for appstore like web service
I am trying to build a database for a appstore like web service. It has platforms, users bases and applications related to platforms that can be installed into base.
It's a hobby game for my self ...
0
votes
1answer
34 views
Tables not displaying after creating database (by script)in SQL Server 2012. Do I need to insert?
I successfully created (scripted) a database in SQL Server . My problem is that while the database itself is listed in the object explorer , the tables from the database are not. The database I ...
0
votes
0answers
17 views
Find_in_set mysql function internal process
I have read one question on stackoverflow about mysql
http://stackoverflow.com/questions/37613488/how-to-make-join-or-subquery-perform-on-this-table
User not using joins, he save the data in ...
0
votes
1answer
31 views
Data Relationship between Provinces and Cities
Does anybody have experience or know how to correctly design database relationships for where
Provinces ->hasmany cities
City ->hasOne Province
the problem is different cities (based on geography ...
0
votes
0answers
21 views
Design schema for booking appointment/time slots
I'm trying to figure out the best way to design the database. I already have a Client table which consists of id, name and email. A client can book many dates for appointment and each date has only 3 ...
1
vote
1answer
21 views
Insert column name as value
Consider the following, in PostgreSQL:
drop table test ;
create table test (result1 text, red smallint, green smallint, blue smallint, results2 text) ;
insert into test values ('red',1,2,3) ;
I ...
0
votes
1answer
34 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
64 views
Placing a uniqueness constraint on a date range
I have a table reservation with columns roomno(INTEGER), startdate(DATE), enddate(DATE) with a primary key on (roomno, startdate).
How do I place a constraint on the table such that bookings are not ...
5
votes
2answers
65 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
13 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
13 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
19 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
15 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
42 views
Singe database account for all users [closed]
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
34 views
Any pointers on a good concise and clear text or article that describes normalization theory? [closed]
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
58 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
25 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
75 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
30 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
120 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 ...
0
votes
0answers
55 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
39 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
34 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
73 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
158 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
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
58 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
49 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
108 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
62 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
73 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
41 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
135 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, ...
