Database design is the process of specifying the logical and/or physical parts of a database. The goal of database design is to make a representation of some "universe of discourse" - the types of facts, business rules and other requirements that the database is intended to model.

learn more… | top users | synonyms (2)

0
votes
1answer
46 views

creating a multi table “identity” column (faux identity)

I am currently designing a job execution engine and have run into a problem I am looking for some advice on. I am converting some processes to a new system and need to define the table structure. ...
0
votes
0answers
25 views

Billion records mysql performance with large result sets

I'm currently developing an application which will have several mysql tables where 3 of them have very large amounts of records. The thing is that my app will have to sync each account on a hourly ...
-3
votes
1answer
95 views

Best design to handle multiple query keys [closed]

I am designing a postgresql database for an online card game. I want to provide users with the option to access their play history - users can see log of their games. The data I want to save has the ...
0
votes
1answer
32 views

modeling data dimensions as columns - some dimensions are highly variable while others have low variability

Please excuse my question if it is a bad one - I am not a DBA... I would like to model the following data: Let's say that I have one column that is the row id as generated by a sequence. Let's also ...
1
vote
1answer
29 views

A many-to-many relationship

I'm designing a simple database and have a question regarding a relationship. So far, I have users, books, highlights and bookmarks. The relationship rules are as follows: Each user may 'subscribe' ...
1
vote
1answer
40 views

Composite Foreign key to single primary key [duplicate]

How do I create a composite foreign key to a table with a single primary key? For example suppose I have the following tables: CREATE TABLE Branches ( BranchId UniqueIdentifier DEFAULT ...
0
votes
0answers
11 views

MongoDB object pointing [migrated]

I am doing an web app and i have a doubt. I need to get a value of a MongoDb JSON? object. I post my code.. import pymongo try: conn =pymongo.MongoClient('localhost') print('Conexion ...
0
votes
1answer
20 views

Time Series data for controllers connected to other devices [closed]

We have a relatively large number of controllers deployed to the field (4500). Each of these controllers have multiple other devices connected to them. These controllers are now going to be able to ...
2
votes
2answers
70 views

PostgreSQL and storing configs

The problem is to store and use global configs such as currency/date format etc. Since this functions are commonly called I'm concerned with the performance impact. Lets take currency as an example. ...
0
votes
3answers
72 views

Database Design for Paper Submission System

I am learner of Database design, I want to design a database for a journal paper submission system in which one paper can be submitted by one or more author. I have initially two tables Author and ...
-1
votes
0answers
43 views

NoSql vs Sqlite in terms of crossplatform application

I've been planning development of simple application mimicking facebook wall, but private to user, unless decided to share it. Something like facebook offline, to post something on somebody's wall ...
0
votes
1answer
36 views

How to insert values in junction table for many to many relationships?

I have three tables in database: trips(trip_id(pk), trip_name(unique), user_id(fk)) places(place_id(pk), place_name(unique)) trips_places_asc(trip_id(fk), place_id(fk)) Since, many trips can ...
0
votes
1answer
53 views

How to create a relationship between one table and multiple other entities

(PLEASE NOTE: I am aware that in a few cases there are one-to-one relationships where one-to-many should be present. It's not intentional, it's just a mistake on my side. Take no notice.) I am ...
0
votes
2answers
37 views

Need help about giving relationships in database design

I am developing a simple blog application in which users can write posts and others can like, comment, share etc. i am facing difficulty in designing database. What I have tried is: user ...
0
votes
2answers
62 views

How does one know when to stop normalizing?

Alright, so basically I feel that I tend to over-normalize things, and maybe I am doing it at the cost of performance. So, to illuminate on the problem, I have created the following schema to use as ...
0
votes
0answers
16 views

Is database sharding done on a table level or database level?

I'm new to sharding databases, so I'm trying to wrap my head around it. Let's say we have a database that has 3 tables: Customer, Suppliers and Orders. Each table has 10 million records. I want to ...
0
votes
2answers
21 views

Effects of monolothic databases on performance and security?

Please note: although I'm specifically calling out MySQL here, I would also appreciate/entertain generic answers that apply to all/most relational systems. As a developer I don't see the design ...
1
vote
2answers
38 views

Preventing duplicate data when usual normalization only makes sense for some of the records

Background I have two tables, one called issues and the other called status_checks, which both contain (among others) the field comment (VARCHAR). An issue is raised by a user when something needs ...
1
vote
0answers
23 views

What does a high level overview of the logic backing database queries look like? [closed]

explanation i.e. At the database driver level of abstraction you may invoke API methods for compiling and executing some kind of query language, which can be expressed as a string sometimes (SQL ...
0
votes
0answers
29 views

Last synchronization time between master and slave (replication)

During replication process where a Master is connected with slave. Some times connection between these two goes down. Any options to get the details of the last synchronization time?
0
votes
0answers
23 views

How to store multiple raster files in postgres / postgis efficiently

This question is partly linked with another question on GIS:stackexchange, but I believe it is has more to do with clever data management. I have a Postgresql server with Postgis extension running ...
3
votes
3answers
476 views

How to achieve many to many relationship in this database design?

I am currently working on small travel application in which users can add other users' trips in their wishlist. I am facing difficulty in designing database for wishlist. What I have tried so far is: ...
1
vote
1answer
52 views

Different kinds of pricing PostgreSQL

I would like to hear your opinion about handling different kinds of pricing. We have a product table which looks almost like this: +----+------+-----------+-----------+ | ID | Name | price_old | ...
1
vote
2answers
67 views

Need help about normalizing database to 3NF

I am currently working on a city travel guide application which let users explore places and plan trip. In the account module, the following functions are to be considered: User can register by ...
3
votes
3answers
146 views

What exactly is overlapping candidate key?

Can some one please explain to me in simple terms what is overlapping candidate key ? What is overlapping as the name suggests? Consider the below relation R(L,M,N,O,P) { M -> O NO -> ...
1
vote
3answers
78 views

Database design: 4 types of users but have different functionality , separate or one table?

I have 4 types of users: Admins , normal user , company , service provider Admins and normal user share some attributes (id .first name ,last name ,phone ,mail) company and service provider share ...
0
votes
0answers
23 views

database design - upvotes for various types

In the early stages of an application I'm developing, I had two relatively simple tables - one for content and one for upvotes. The content table's schema is as roughly: data | text id | integer ...
0
votes
0answers
25 views

Entity Schema Design

I'm working on an application that has an "entities" system (built before my time) that models users, customers, contractors, vendors, etc. These are all types of people, but have different needs in ...
0
votes
3answers
73 views

Confused about Primary Keys, and Foreign Keys

Im brand new to database design and, I'm slightly confused about Primary Keys, and Foreign Keys and my database design. Context: I'm working on a database of museums and Categories will be one way ...
0
votes
1answer
57 views

Trying to map postal codes to their respective cities, but running into some issues

The Problem I am creating a database of Countries, Regions, Cities and Postal Codes. Here's an example to give an idea of the database structure as it is now: Now, let's take Tuskegee University ...
0
votes
1answer
31 views

Database design: Which will perform better, auto incremented column or a composite key for appointments

The schema of table is : appointment(date,time,patientID,...) pateint(patientID,PName,age,...) PS: two or more patient can came at the same time . The number of appointments per day up to 500 . ...
0
votes
1answer
54 views

Copy database from one mysql sever to another mysql server

Following are the details of the database and server: Server1: Host Name: abc Port Num: 01 database: test Server2: Host Name: xyz Port Num 02 database: spareparts userid: spts password: abcdefgh@123 ...
0
votes
1answer
76 views

No-SQL Database Design for restaurant app

I've been working with SQL databases all my life and I am now trying to get into the NoSQL world. I am a looking for a piece of advice on whats the best practice in order to achieve this. Suppose I ...
5
votes
4answers
237 views

Don't know how to transform variable entity into relational table

INTRODUCTION AND RELEVANT INFORMATION: The following example illustrates the problem I face: Animal has a race, which can be a cat or a dog. Cat can be either Siamese or Persian. Dog can be a German ...
0
votes
2answers
48 views

One field with multiple values?

I know that what I'm asking can't be done or shouldn't be done, but I really don't know how to do it. This is the problem: 2 tables: - Drivers (id_driver, name, etc...) - Trucks (id_truck, model, ...
1
vote
1answer
39 views

How to Choose an SQL Database for Simple Data Preparation & Analytics

In the interests of not making this a shopping list: please advise on "how" to choose a the right tool, as opposed to which is the best. E.g: Which questions should I be asking myself / client? What ...
1
vote
3answers
381 views

Which one is better performance and (approach): store as rows or as a varchar column

OptionA: TableA: TableA_ID (PK,int) .. TableB: TableB_ID (PK,int) .. TableC: TableC_ID (PK,int) .. TableD: TableA_ID (FK, int) TableB_ID (FK, int) TableC_ID (FK, int) (Nullable) ...
0
votes
5answers
92 views

Database with only text fields

I started working on an existing SQL Server Database System in which most of the fields are stored as text. Except some IDs all fields are varchar (phone numbers, zip codes, Dates, addresses, monetary ...
11
votes
5answers
1k views

Why does a UNIQUE constraint allow only one NULL?

Technically, NULL = NULL is False, by that logic no NULL is equal to any NULL and all NULLs are distinct. Shouldnt this imply that all NULLs are unique and a unique index should allow any number of ...
0
votes
0answers
30 views

DB Model for Small requiremnt [closed]

I have created below DB model. Could you please validate if this is proper. This is restaurant app where waiter takes order and gives it to chef.Before placing order she does have information of ...
2
votes
4answers
96 views

Can't finish my ER diagram

I am trying to create a database for a small book library. This is not a professional project, just an exercise of my own. This is my first time to try this ( I am self-taught ) and I am encountering ...
1
vote
3answers
32 views

Study material for Advanced Databases module inc SQL, Relational Model, ERM & TM [closed]

For my next module in my masters, I am planning on studying Advanced Databases. (Long story short, the masters is designed for people with NON-CS degrees, so I have never studied databases, therefore ...
-2
votes
1answer
35 views

Oracle Concurrent Insertion

We have simple case like Create table t1 (c1 number primary); session1: insert into t1 values (1); //session1 do not commit transaction session2: insert into t1 values (2); Will Session2 ...
0
votes
1answer
61 views

A question regarding the performance of normalization

I am sorry this is such a newb question, but it is one thing I am having a difficult time wrapping my mind around, and after an evening spent reading I finally just decided to ask for some ...
1
vote
0answers
37 views

triggers vs views [closed]

I have a mysql database with the following tables Penalties Scores player_instance When penalties is updated/deleted then it will update scores (via a trigger). Also when a player-instance row is ...
1
vote
2answers
45 views

Multidimensional aggregates

Suppose one wanted to cache simple aggregate data over a table or grouped parts of a table, one could simply make a table with the aggregated results. If the original table is only appended to, it is ...
0
votes
1answer
34 views

Should there be a temporary table to hold Order and OrderItems? [closed]

After trying out a few open-source restaurant POS, I still couldn't decide if there should be a temporary to hold Orders and OrderItems? I was initially thinking to have temporary tables and ...
1
vote
1answer
57 views

How to design tables such that a person can work for many companies? [closed]

Database design such that a person can work for many companies?
2
votes
2answers
64 views

Best practice design when expecting large data in a table

I have come across a way that kind of works for me, and was just wondering if it would have any side effects, or, if I'm just thinking about this the wrong way. I first had this idea when I was ...
1
vote
1answer
38 views

Saas MySQL Multiple Databases or One Database [closed]

I am using MYSQL so Database is the Schema, sorry if not using right terminology. I currently have a Saas product which has multiple databases one for each client with a main databases which we ...