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

learn more… | top users | synonyms (4)

7
votes
1answer
527 views

CouchDB and document versioning

I'm currently working on a wiki-esque application using CouchDB and am trying to implement a document versioning scheme. The way I see it there are two ways of doing this: Store each version as a ...
1
vote
0answers
7 views

Using schema + Filegroups or entirely separte 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
9 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
10 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 ...
6
votes
3answers
876 views

Modeling a scenario in which each Music Artist is either a Group or a Solo Performer

I have to design an entity-relationship diagram (ERD) for a business context that involves the delineation of music artists, as I will detail bellow. Scenario description An Artist has a Name, and ...
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 ...
2
votes
1answer
124 views

Storing Friendships: Should I use a multivalued column to retain Friend Identifiers?

Say I have a table called UserFriendList, which has the columns: Id (set as Primary Key) UserId FriendListIds And let us suppose that said table holds the following data: ...
1
vote
0answers
13 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 ...
-1
votes
1answer
38 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 ...
1
vote
2answers
458 views

Optimize large table design

First, I am a complete novice when it comes to databases, but have been given the task to speed up queries on a large set of data. (Hundreds of millions of records) The current implementation is a ...
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 ...
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
11 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 ...
0
votes
1answer
131 views

How to/Where to find MySQL templates for Toad Data Modeler

I have recently install Toad Data Modeler 5.3.4 and it only has Oracle data model templates. Where can I find and import the MySQL data templates so that I can create MySQL database models. I have ...
2
votes
1answer
21 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
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, ...
1
vote
3answers
282 views

Working NoSQL scheme for a betting app

I'm building an app where a user can bet on the result of a sports game. Basically, we feed the database with competitions and games, and the users can place their bet in the form of a score ...
-1
votes
0answers
31 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 ...
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 ...
2
votes
0answers
22 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, ...
-1
votes
0answers
47 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
1answer
24 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
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 ...
1
vote
3answers
112 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
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
3k views

Storing multidimensional array in MongoDB

I am attempting to convert a large MySQL database of product information (dimensions, specifications, etc) to MongoDB for flexibility and to move away from the restriction of a column based table. I ...
0
votes
1answer
117 views

MySQL Circular Replication

I have this scenario where I have 4 MySQL servers (A,B,C,D), as this: Server (A) is always online, and serves as the main website. Servers (B,C and D) are online on random periods of the day, and ...
16
votes
4answers
18k views

What is an audit table?

What are audit tables? How are they useful? I came across them reading this article.
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 ...
10
votes
2answers
10k 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
121 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
117 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
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 ...
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 ...
1
vote
1answer
46 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. ...
0
votes
2answers
152 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 ...
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) ...
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
68 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, ...
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 ...
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 ...
76
votes
7answers
19k views

Why shouldn't we allow NULLs?

I remember reading this one article about database design and I also remember it said you should have field properties of NOT NULL. I don't remember why this was the case though. All I can seem to ...
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 ...
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 ...
0
votes
1answer
198 views

How to manage very large amount of data in mysql

I have a table in MySQL in which I write logs of users in a specified format. It have just 2 columns:id (auto-increment), log. Currently data is very less, so I'm using this. Now there is a ...
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 ...