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

learn more… | top users | synonyms (4)

1
vote
2answers
61 views

Bus timetable Database structure

I am creating a database for bus timetable in android app. I have created four tables: stops, route, detail_route and schedule. columns in each table are as follows. Database structure route (id, ...
0
votes
1answer
43 views

Convert OLTP database to OLAP Datawarehouse

(http://imgur.com/p8Gy67E). Basically I need help to convert that OLTP Model to Data Warehouse in order to be used for further OLAP Cubes. I understand well the difference between the Star Schema and ...
5
votes
4answers
209 views

How can I represent discrete ages in a relational database?

I am using PostgreSQL to store data on a Person. I need to store whether each Person is capable of teaching a Child of each age year, from 0-17. The ages are discrete values, and a Person can have any ...
5
votes
2answers
101 views

Creating relationships between tables that where not specified in the given UML diagram

Good evening! I'm working on designing my very first actual database from the following UML class diagram (in French unfortunately): I'm at the point of creating the relational sketch of it which ...
1
vote
1answer
24 views

Keep files in mongo as part of the data

I want to create a news app that works in the following way: The site will contain articles, and each article with have multiple versions. Each version is a docx file and the article itself does not ...
0
votes
2answers
42 views

How can I create a join on three tables, each from a different database?

Here is a simplified example of what I'd like to do: SELECT a.create_time, b.create_time FROM DOB.dob_state AS a JOIN numID.numID_history AS b ON a.create_time = b.create_time; I'd like to join ...
3
votes
3answers
54 views

Create unique sequence per set of rows sharing the same foreign key value

In my system, I want each user to have an own id sequence for his articles. So, I have created a database that has a table that depicts a correspondence between users and their articles. Here is the ...
0
votes
0answers
12 views

Restrict Front Ended User access to Form entry only

I have a front end database that I want to restrict user access to only enter data into a form. I do not want them to be able to scroll through other entries via the form. How can I restrict this in ...
1
vote
0answers
24 views

How to avoid repetition of columns of same type in database

I am building a database that will save entities such as: orders, cart, customers etc. All these tables share some common attributes such as: a date, the user agent that was used to create the new ...
1
vote
1answer
14 views

Many-to-many tables tracking participation at multiple events

I am working on a side project to track observations of different birds, and the time spent during each observation. Right now I have a table called Observations: obs_id | start_time | end_time ...
0
votes
2answers
40 views

Multiple one-to-one relations

I'm trying to design database relations for archaeological places in a country. There are around 9000 places registered. An archaeological place can have multiple types. Types will be like Cave, ...
3
votes
2answers
112 views

Two names for a column

I have a column named foo in a table. I have constructed queries in scripts, which use this name. I would now like to rename the column in the table to bar. This will break the scripts (there are ...
0
votes
1answer
20 views

Records creates double entry, should only be one

Need an assist on a database design. I'm creating a system to track students in a course. Currently, I have courses. Courses are made up of modules. A given module can be in multiple courses (eg 2 ...
1
vote
1answer
46 views

Standard naming for a credential systems

Is there a standard naming convention in regards to credential oriented systems? (Logins) The only one I could really come up with (in a shorthand form) is using the word "Gateway". Representing a ...
0
votes
0answers
19 views

Help choosing between one and two tables for manually entered and auto generated items

I'm designing a database that merges a legacy system with a new system, all for the same customer. For the new design I need some help whether to use the same table or two different tables. ...
3
votes
1answer
26 views

enforcing data integrity for sequential relations

These are my tables: create table trips ( trip_id serial primary key, trip_nm text ); create table trip_segments ( segment_id serial primary key, departure_ts timestamp with time zone, -- ...
1
vote
2answers
41 views

Parent -> Child -> Grandchild with a foreign key back to Parent, Bad Practice?

Basically the foreign key on options is there to make it so that if you want to know what product an option belongs to you don't have to go through variants. A variant is like Color, Size Etc.. An ...
2
votes
0answers
30 views

Design university schedule system

I'm not sure if this is the right place to ask for tips and improvements in a database design but I don't know about another one. I want to design a simple University system. Below I'll have included ...
1
vote
1answer
40 views

PostgreSQL Foreign Key

Very basic questions. I have 3 tables, very obvious whats the use of them: -------- CONTACT -------- ID (SERIAL) - PK NAME (VARCHAR) --------------- CONTACT_DETAILS ---------------- ID (SERIAL) - PK ...
-3
votes
1answer
44 views

Why different connections in database.? [closed]

I know we can use a single connection for different databases but why an option of multiple connection is provided, in other words, when is the correct situation to use multiple connections when we ...
0
votes
1answer
37 views

Creating a pseudo email database

I am creating a database for Job offers website between Employer and Applicants that required sending Email between the two in MSSQL. Let's say I have 3 tables like these: Employer ID int ...
1
vote
0answers
39 views

How can I partition records vertically across machines?

I recently asked two questions: (1) how to store and connect partial records in two or more machines, with the record halves correctly associated, and (2) how to determine the increase in time/space ...
0
votes
1answer
48 views

Split data across databases/machines

I have two questions about how to partition data across servers. Can I create and connect two or more databases, with the records correctly associated, using only a mapping table stored in a third ...
0
votes
1answer
49 views

Explaining how to design a database to model the stocks of a firm to my Grand'ma

My teacher always asked us to create simple but precise databases. Being able to explain it simply but not without leaving the theory "to your wise but aged loved Grand'ma"... I have to create a ...
2
votes
1answer
75 views

Minimal 3NF normalization issues

I'm trying to learn how to normalize to 3NF but the example that my teacher gave us doesn't really help. The algorithm I always used is this one: Find a canonical cover F_c for F For each FD X->Y ...
0
votes
2answers
44 views

Design problem with placing proper constrains

I have been trying to solve this problem but in every solution there seems to be something missing. So we have organization for collecting donations from donors for people that need help. Member can ...
1
vote
1answer
26 views

finding out the metadata and joins

Suppose I have a database with a large number of tables. Is there any command that I can type in PL/SQL which display only the field names of a given table? Given any table is there any command ...
1
vote
1answer
33 views

relation between customer and product with review

I draw this ERD but I'm not sure if the relation in customer and product with review is correct. also, is it right that I made Rate, Date and time this way ? this is my Description for review Each ...
3
votes
2answers
108 views

Table with only a single constant value?

I use C# entity framework code first. My knowledge regarding databases is almost nothing. I don't know if it's common or good practice so I wanted to ask you guys how I should do it. Say I have a ...
0
votes
1answer
32 views

Creating an exam center database

I am making an app than has an exam center... I am aware of the database normalization concepts...but in my case it looks like I need to store non atomic values in a column. Actually the app will have ...
0
votes
1answer
48 views

Database design for a lot of rows with data archive

i'm new to database design on larger scale. I have some knowledge and experience with MySQL but now i have some performance issues. Currently i'm using MySQL with 3 databases: History Snapshot ...
0
votes
1answer
53 views

Searching from Database having billion records, which database should i use [duplicate]

We are having 1 billion phone numbers and we have to search them against numbers which we send for promotion to filter out which are not promotional registered. That is we have lets say 1 million ...
1
vote
1answer
45 views

Relationships mess

Say I have a table ads, and each ad has several medias - held in a medias table. Each media can be one of several types. Types are in a media_types table. So, for example, an ad has two medias: one ...
0
votes
1answer
19 views

Database Schema for a User and his variable number of items

I planning to create tables which hold data related to my users. These tables contain the items that each user owns, and there is no exhaustive list of all the item. The number of items owned by a ...
-1
votes
2answers
42 views

Good learning source for learning normalization [closed]

Can anyone recommend a good learning source to learn about database normalization from scratch please? I referred to many youtube videos but they weren't fit to me.
1
vote
0answers
18 views

Database schema design for dynamic CRM with inter-account interaction

In a traditional CRM, we have some basic objects such as users, organisations and contacts. The users belong to an organisation's CRM account. A very basic schema might look like this: User ---- id ...
3
votes
0answers
39 views

Class-table inheritance without flag column

I am trying to implement proper Class Table Inheritance (aka Table Per Type Inheritance) in mySQL. AFAIK it's not offered out of the box so some hacking around is needed to make it tick. Current ...
1
vote
3answers
96 views

Mysql - How number of columns effect the database performance

I have a table to store social media information for users; I have 42 columns to store and will have 100000 entries per day to store. I am planning to have MyIsam engine for it and will use horizontal ...
0
votes
0answers
15 views

What is a fast way to calculate counts and sums?

I have the following data: A journey which contains this info: - a geohash (to identify the location) - a journey_id - whether this was the start or end of the journey - the cost of the journey I ...
1
vote
1answer
25 views

Does the existence of a college course depend on the department in which it is studied?

I have a Department entity along with a Course entity, does the course entity depend on the department or is it a strong entity by itself since it has an ID that can distinguish it from other ...
0
votes
0answers
26 views

Does an exam entity in a student information system depend on the department or the student?

I have made an ER Diagram for a student information system where I have the following entities: Student Department Course Administrator Exam Attendance I'm having trouble determining whether Exam ...
0
votes
1answer
32 views

Which isolation level do I need?

I've read a lot of articles about Isolation levels, but I'm confused which one to choose for my MySQL 5.6 database. This is my situation: Let's say I have two group of users: Group A ...
0
votes
0answers
34 views

Registration Schema Implementation

I'm building an online app that is going to handle reservations. Individuals making reservations can make one for a single day or multiple days. I have come up with two schema and cannot see much of a ...
0
votes
0answers
20 views

Address database scheme - multiple links

I am currently building a databse scheme for a small company. But at the point doing it I figured out that my attept will be unacceptable due to the ability that one action will affect multiple ...
3
votes
2answers
83 views

One-to-many to multiple tables

What's the normal way to design a relationship where every entry in table A is connected(1:n) to either an entry in table B or table C? Or specifically: There are a tables for characters, movies, ...
0
votes
1answer
30 views

How to store Alternate Names for Places or City in Table

What is best way to store Alternate Names of the Places (City or Locality) in MySQL database columns which user will be searching? Examples: In India user may search for "Chennai" or "Madras" and ...
2
votes
0answers
39 views

insert/update from web server or database

I have a data-model on the front-end that I want to insert/update in the database which of the following approaches (or neither!) is considered best practice: # in webserver some_model = {json} ...
3
votes
1answer
71 views

Database design decision help

I currently have a database design that I have been looking at and I am wondering if its the best one. I have the following tables: Each request has a current status. I need to keep track of all ...
0
votes
0answers
23 views

Database design for efficient read queries given lots of writes

I have a PostgreSQL database, and an application which receives around 200 requests per second. Each of those requests contains a message with an identifier representing a journey id, a timestamp, ...
1
vote
1answer
70 views

How to efficiently design MongoDB database for Uber-like application?

I'm designing a MongoDB database for an Uber-like application, but since I'm new to NoSQL concept, I have several doubts. I have three main collections - users, requests and messages. User can post a ...