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

learn more… | top users | synonyms (4)

1
vote
1answer
56 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
27 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
43 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
32 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
42 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
46 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
38 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 idetity(1,1)...
1
vote
0answers
40 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
52 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
53 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
82 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
45 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
29 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
48 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
117 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
40 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
64 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
58 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
46 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
22 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
32 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
47 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
122 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 entities?...
0
votes
0answers
29 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
34 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
41 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
23 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
86 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
52 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
40 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
75 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
131 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 ...
0
votes
0answers
79 views

How could I improve speed of fetch in Oracle?

I've got a table of taxi GPS trajectory records with approx 60 million rows, including date, time, ID, longitude, latitude, speed, bearing etc., which were imported from .csv files with setting each ...
3
votes
1answer
71 views

which way is better to design shopping Cart Table - SQL Server

I want to create a shopping cart in an online store. I have two ways to design shopping cart, but I don't know which one is better. Solution 1: create a table like below : in this way for each ...
0
votes
0answers
30 views

How to store general information about an entity

I am designing a database to store data relating to management of a school. I have tables representing the sorts of things you would imagine: Students Carers Invoices and so on. One feature I wish ...
2
votes
1answer
77 views

Is it bad practice to use separate columns for parts of date values?

I recently posted these questions in order to try and improve the performance for my queries. DBA Q&A 1 DBA Q&A 2 One of the suggestions was that I should use a DATE() column rather than ...
21
votes
8answers
2k views

What problems are solved by splitting street addresses into individual columns?

We have a team who designs the tables and relations for software developers. In our organization, they are pretty strict about enforcing 3NF normalization - which to be honest, I agree with given the ...
3
votes
0answers
51 views

Ranking items on a per user basis

I have a MySQL DB with 20k-50k items and I want various users (let's say 1k-10k) to rank these items for themselves, so this is not going to be a global ranking but a per user one. All items are ...
3
votes
2answers
84 views

Database model about libraries

I need to make database model about libraries. In that model I'm tracking more libraries and every library has it's own moderators. Every moderator can make categories of the books and than add books ...
0
votes
1answer
28 views

Best way to apply a “friend system”-like table? [duplicate]

I am making a website that has multiplayer games like chess, poker, etc. Users will be able to add friends. I was thinking about how could I make this in a DB, and I thought of two methods: 1 - ...
5
votes
2answers
178 views

Is it possible for a weak entity, already related to a strong entity, to get related to another weak entity in an ER diagram?

I have a problem statement (briefed), Database for a Fitness Club - Client's Attributes: Name, Address, Ph. No. Membership Plan's Attributes: Beg. date, Sub. Fee, Monthly Fee, Duration ...
1
vote
1answer
28 views

Is row based or column based raw input better?

I have a vendor that is starting to give me some rather large reports on course completions and usage. They have two formats for the reports: If a student is enrolled in a course, they will have an ...
-1
votes
1answer
87 views

how can I add multiple values in a single column?

I'm working on a project and I have a scenario We have a vendors table, some vendors have many contact no. how can i add multiple phone numbers on a single contact no. column?
1
vote
0answers
21 views

Efficient Provision of Multiple Versions of Data

We have a fairly unusual database use case: we have a number of large datasets used for research (the largest being a couple of billion rows). The datasets are used by potentially several hundred ...
0
votes
2answers
45 views

One connection vs multi connection

I'm redesigning my client's website in order to add about 16 mini sites for their web site. It's a website using PostgreSQL. And my question is: Performance wise, what is the better database design: ...