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)

1
vote
1answer
50 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
1answer
25 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
79 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
80 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 ...
0
votes
1answer
78 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
63 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
43 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
74 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
21 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
1answer
22 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
42 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
31 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
32 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
490 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
54 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
76 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
196 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
111 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
28 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
27 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
74 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
58 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
36 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
59 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
93 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
252 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
51 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
44 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
385 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
96 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 ...
10
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
33 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
112 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
35 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
36 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
67 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
58 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
82 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
46 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 ...
0
votes
1answer
27 views

Does the creation of constraints affect the views that have been created earlier?

Does the creation of constraints(foreign key) affect the views that we have created earlier? create view allreservations (fno, customer) as SELECT f.flight_number, customer_number FROM flight f, ...
1
vote
1answer
42 views

Question about the workflow/design process/tools used by professional database designers

I hope this in the right place, as it's really more a question about database design software than the philosophy and practise of database administration. I'm a masters student doing an "introduction ...
0
votes
2answers
39 views

about giving same column name in every table of database

I have a database with 5 tables. In every table there is a column for checking status. If i give the name to that column which is same in all the 5 tables; What will happen? Is it correct according ...
0
votes
0answers
21 views

Database & Structure for SaaS reporting software

I'm building a SaaS application to allow users to upload data and perform reporting and analytics on it. The reporting and analytics would be quite simple like grouping and aggregating. This has ...
0
votes
0answers
97 views

How to select column names that contains specific value from other table's column

Suppose I have table items and logs. Both tables have col1, col2, col3, col4... col(n). col1.. col(n) in table items simply specifies key name (col1 = "beef", col2 = "chicken", col3 = "bacon") ...
1
vote
2answers
29 views

Suggestion for Tooth Chart Data Structure

Hello I'm not sure if this is an OK question here but here goes, I'm quite confused on how am I going to make the data structure of my tooth chart database. I had first this thought of making a data ...
0
votes
1answer
38 views

Postgres table contains 1 column, and that column is PK. How to insert new record?

In my application, I have an abstract base class, and 2 derived classes. The number of derived classed will probably not increase. If it does, it won't be more than a few. The class structure looks ...