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

learn more… | top users | synonyms (4)

1
vote
3answers
107 views

Make column default a query

Is there a way I can set up a column in PostgreSQL 9.2 such that the default value is a query? I am having to track down query statements from different sources that don't respect the "default" ...
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 ...
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. ...
1
vote
2answers
42 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 ...
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, -- ...
2
votes
0answers
31 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 ...
8
votes
4answers
504 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 ...
7
votes
2answers
3k views

nvarchar(max) converting to varchar and table optimization

I am working with a table that has all character types set to nvarchar some of them are nvarchar(max). We are converting all these to varchar and specifying a character width based upon the actual ...
0
votes
1answer
387 views

Social file sharing database schema

I have a project called social media file sharing. I have this database schema and I need your opinion about what I should remove or add or edit on my database schema. Every user can upload media ...
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
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 ...
3
votes
2answers
115 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
51 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 ...
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

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
79 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 ...
2
votes
1answer
97 views

Lost (and found?) on 4NF

I'm so extremely lost on 4NF it's not even funny. I've been reading on it for literally days and, except for about 15 minutes ago, I wasn't sure that I had even broken the surface. I think I might ...
1
vote
2answers
59 views

Better deffining a parent-child relationship on a table

Our team is developing a new system and it has a Warehouse table that must have a hierarchical relationship between entries since a customer can have 1st, 2nd, 3rd and Nth degree warehouses. We first ...
3
votes
3answers
2k views

Relational vs Non-Relational Database for Events Database

I'm trying to find out whether an SQL or no-SQL solution would be better for creating an events database. I'm creating a ticketing system, similar to ticket master. I know that for either ...
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
43 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 ...
0
votes
1answer
36 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
54 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 ...
1
vote
1answer
37 views

Multiple increments in table

Let's say I have a table which stores different bills. There are multiple departments using this database and every department needs to use their own incremental numbering for the bills. I do ...
-1
votes
1answer
132 views

Database design for timetable in mysql

I have to make a web application in java. The web application is like a online appointment book. Everyday of the week has 1 column with times from 9.00am to 10.00pm (only full hours). I already ...
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
57 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 ...
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.
3
votes
0answers
44 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
0answers
24 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 ...
1
vote
3answers
110 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 ...
5
votes
1answer
102 views

Social (Classmate like) database schema appreciation

This is a database schema I propose for a networking website like the Classmates.com web site. Do you think the database has all the tables needed for such a project? What would you rather do? The ...
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 ...
4
votes
1answer
371 views

Storing phone number in E.164

TLDR; If I store a phone number in MySQL(or any DB with equivalent constraints), would the correct format be E.164 values into a VARCHAR(32) field? Have been reading up on format/storage of ...
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
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 ...
3
votes
2answers
98 views

Modeling a Car Dealership context in an Entity-Relationship diagram

I am creating a conceptual database model for a car dealership. I have spoken to the client, gathered all the requirements, and thought I had my Entity-Relationship diagram all correct; however, I ...
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 ...
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
0answers
36 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 ...
3
votes
1answer
73 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 ...
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 ...
0
votes
0answers
22 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 ...
1
vote
1answer
92 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
1answer
38 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
4answers
225 views

standard solution for storing units of measure

INTRODUCTION AND RELEVANT INFORMATION: Let us say I have a table that stores height and weight of a person, something like this : PersonTable < # ID int, Name varchar(50), Height double, Weight ...