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

learn more… | top users | synonyms (4)

1
vote
2answers
36 views

More tables or more columns (in my simple example)?

I'm modelling DB and wondering if I should create more very simple tables or just have more columns in tables. I mean, let's assume I have s table named User, each user can have status(ACTIVE, ...
0
votes
0answers
46 views

Database Design for group chat

I am creating a chat system as a RESTful API. This is what my design looks so far A request can be made to fetch the newest 50 messages that are part of a group id. Even if I do get a list of ...
4
votes
1answer
26 views

Enforcing that a user should have one or more email addresses

I'm trying to design an application where a user can authenticate oneself with one password and one or more email addresses. I'm not sure how to enforce this constraint. The tables so far are simple: ...
0
votes
0answers
36 views

Database calculations question

I am creating a MySQL database that is going to be used to store a lot of timestamps. There are currently 6 timestamp fields that data can be entered for one row. I am utilizing timestampdiff to ...
5
votes
1answer
111 views

Hard delete vs soft delete performance

I've got this table that is intended to have a lot of activity. Anything that's in it indicates that a user action is still pending. Once deleted, it would indicate that action is no longer pending. ...
4
votes
0answers
60 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
2answers
61 views

Is nvarchar 0-indexed? [closed]

Is the max length configuration for nvarchar 0-indexed, e.g. If I declare column x as type nvarchar and set the max length to (2): (X nvarchar(2)) does that mean that the column takes 3 nvarchars? or ...
3
votes
2answers
39 views

Mysql - Each mysql database with its own user?

I developed a web app in which each company that registers on the app gets its own database with its own mysql-user credentials. Is this approach fine OR can I use one master mysql-user account to ...
3
votes
0answers
42 views

Is this ER Diagram correct?

Requirements: • Each customer has a name, a permanent address, and a social security number. • Each customer can have multiple phone numbers, and the same phone number may be shared by multiple ...
1
vote
2answers
90 views

What are the advantages and disadvantages of Third normal form and Boyce-Codd normal form? [closed]

I know that 3NF makes a database more efficient and stores data according to relevance, which is more logical, but are there other advantages? and are there any disadvantages?
3
votes
1answer
80 views

Versioning data in a database [duplicate]

I have a database to hold data about projects. Projects have lots of data associated with them that is segmented into other tables such as project_contacts and project_images. Those are one-to-many ...
3
votes
3answers
146 views

Choosing a PK: VARCHAR(2) or SMALLINT(2)?

Today we were discussing the following: Brazil has 27 states and each one has its own abbreviation (just like USA). So we've got RJ for Rio de Janeiro, SP for São Paulo, MG for Minas Gerais and so ...
3
votes
2answers
188 views

Database design of Room Booking system in a Hotel

Intro and System Description I am currently designing a Database for a Room Booking system in a Hotel. Client fills a Form / Request with following information about Room: amount of people in ...
1
vote
0answers
33 views

Multiple columns VS one big-capacity-column?

I have a table like this: // table_A +--------+---------------+------------+ | id | short-text | long-text | +--------+---------------+------------+ int(11) varchar(200) text ...
1
vote
1answer
53 views

Store Mysql Index in separate .Ibd file?

Is there any option to store table index in separate .ibd file? something like file_per_table mode? Becuase my table is growing more than 200gb. Table .ibd file have index for that table also. so If ...
4
votes
1answer
66 views

Checking existence and syncing data with millions of rows

I am developing sms based service platform. In India, we have two different types of routes to send sms - promo & trans. Sms sent via promo route has to be checked against nationally maintained ...
1
vote
0answers
39 views

Review the ER diagram of a University

A database includes information pertaining to students, lecturers (scientists) and lectures. We store first names, last names and correspondence addresses for each person. A lecture is ...
1
vote
1answer
43 views

ERD to Relational schema

I'm new to DBM and I'm trying to learn it online. Honestly, I need some help with this. I don't know if I'm doing this right, and any help will be appreciated. This is my answer for i.
3
votes
2answers
82 views

Difference between an Entity and a Relation

I am currently reading about entities and relations and I can't understand the difference between them? According to Wikipedia we have an example of a relation like in the pic: This looks exactly ...
3
votes
2answers
117 views

Database of the website “eMovies”

Here is my exercise: Given database of the website "eMovies" that manage data about cinema movies. build the given database in Access and create the relationships between the tables. My ...
0
votes
1answer
42 views

Separating data to different tables relating to the same object

I am unsure what is the correct practice here. I have a collection of rows with fixed values in the columns....consider them fixed attributes for a particular object of a given ID. So i guess you ...
5
votes
1answer
96 views

Index not being used in SELECT query

I have a table of about 3.25M rows with the follow format in Postgres 9.4.1 CREATE TABLE stats ( id serial NOT NULL, type character varying(255) NOT NULL, "references" jsonb NOT NULL, ...
0
votes
1answer
30 views

What is the most efficient way to merge topics of a forum or blog?

Lets say we have a topic table, and we want the ability to merge topics, such that all posts from x topics about the same thing will appear in one single topic( the oldest one). So, when someone ...
3
votes
2answers
56 views

What's the best way to diagram this “relation” looking for performance and|or disk space savings

I am working in a DB diagram and I have a table called cm_identifier_type. Each record of this table can have or not a downloadable file (saved a file path). I have identified two possibles ways to ...
-1
votes
1answer
48 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 collum with times from 9.00am to 10.00pm (only full hours). So, i already ...
4
votes
1answer
54 views

Exchange rate table and conversion on the fly in query

I'm working on a small project and I'm have arrived to the point where I let a user chose in which currency to display items. I'm working with Java and my first thought was that I would use some kind ...
1
vote
2answers
54 views

Table design: how to store denormalized Excel data

I have following data stored in Excel which I would like to transfer to SQL DB. There will be a new value every year for every indicator. Indicator | 2001 | 2002 | 2003 ...
0
votes
1answer
37 views

Natural join question, I have the answer, but I don't know why it's correct. Help?

So I have a relatively simple database schema. I'm not sure why the where clause in the answer magically makes the dept_name duplication problem go away. I'm new to all this and I'm sure the ...
3
votes
1answer
52 views

Split Mysql Table internally

How to split the huge mysql table (size around 200GB) internally? Is there any mechanism other than partition? The table size is daily growing. Table created using innodb with indices. Thanks in ...
0
votes
1answer
23 views

Google datastore modeling help

I have a very simple requirement. I have an application where users log in, add/remove/view friends, and can send/receive (also view the message you sent and received) a message (message is more like ...
3
votes
2answers
72 views

Database normalization: Which is the best fit?

I have 3 different types of data set: Notification: start_date end_date notification_type country_id state_id Campaign: start_date end_date user_notification Transaction email ...
1
vote
0answers
15 views

Handling Login of SAAS application end users

I am designing a SAAS application with multi-tenant database per-user-per-database with unique sub-domain of subscribed users. The question arises when subscribed user of saas application add more ...
0
votes
0answers
15 views

Dealing with multiple user types, modules and requirements

I have the following database schema. Basically, it all comes down to this: - users can have multiple user types - some user types come with specific requirements for a complete profile - there are ...
0
votes
1answer
32 views

Database Design - I have a question regarding joining tables

Forgive me for my dumb question but I am new to Ruby on Rails and data design. We all have to start somewhere right? Well my question is that I want to build a database for a Truck driving company. It ...
4
votes
2answers
77 views

Represent time interval in mysql Database

How can represent a time in interval in mysql database in a way that I can index them later . For example: -------------------------------------------------------- id | name | ...
-1
votes
2answers
91 views

How to delete millions of records from table

I am using postgree 9.4 database and I have a table snapshots with 700+ million rows and I want to delete about 9 million rows. I have tried different queries to delete records, but no success. Query ...
3
votes
1answer
59 views

Entity Relationship Design; The best scenario

TLDR; What would be the best design choice for the scenario below and how would each design react at extremely large data volume? I have experience working with large government database systems ...
0
votes
0answers
36 views

Is this pattern named and how to normalize it?

I am given a data table : State ( StateId, StateNme, StateCounty1, Statecounty1Seat, StateCounty1Rep, StateCounty2, StateCounty2Seat, StateCounty2Rep, StateCounty3,...... ) ...
1
vote
0answers
36 views

database design for a filing system in educational website

i'm designing a database for a filing system in educational website. user can upload files ( files_table) user has a library (library_table) user can add a file to his/her library each library ...
0
votes
1answer
11 views

library data modeling

I'm going to implement a library system as a web application. And I decided to use this library schema. But there are some problems which I don't understand. The Books entity, is it possible to ...
3
votes
0answers
35 views

why some one will use approximate data types for sales

I was messing around with Kartris online shop and I was reading this definition for a product version(you have basic information of a product in one table and many versions of products wich can have ...
8
votes
3answers
141 views

How to avoid a cyclic dependency (circular reference) between 3 tables?

I have 3 tables: People Post Likes When I design the ER model it has a cyclic dependency: 1:N People --------< Post 1:N Post ----------< Likes 1:N People ...
0
votes
0answers
22 views

Help creating database schema to build database off

not sure if this is the right area for a question such as this but I am looking to create a database for an IS warehouse who deliver laptops, desktops, monitors and other peripherals to different ...
0
votes
0answers
22 views

When is it suitable to turn a table around [duplicate]

When I create tables to store data in, I have columns for each property of the model to store. IMHO this type of table design is easy to read and logical. Example below: uid | firstname | lastname | ...
4
votes
2answers
80 views

Where/how do I save extra data per column?

Next to the actual data I have in a table, I need to save some data about those fields. I'm hesitant to call it metadata, because while it is exactly that, metadata has a specific meaning within ...
1
vote
0answers
20 views

How to bucketize using Query optimal bucketization algorithm

I am trying to implement query optimal bucketization algorithm (QOB). The input of the algorithm is D=(V,F) where V is the value of the attribute and F is the frequency of occurrence. For EXAMPLE D ...
0
votes
1answer
36 views

Generate incrementing IDs without sequences

Suppose there's a multi-tenant application where users can create some kind of documents with basic structure like CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT); CREATE TABLE documents ( ...
3
votes
1answer
40 views

Seeking feedback on table relationships, general schema design

I'm designing a database for a business that sells tickets to a variety of events worldwide. The app will be built with Rails using PostgreSQL. Here's what I have so far, link to full size image ...
5
votes
2answers
462 views

Relation between first name and last name

I have some troubles understanding the relation between the a person's First name and Last name in a table. (In a Relational Model) I've read that the relation is of type: 1-1: They are linked ...
0
votes
1answer
23 views

Hibernate modelling a N:N Relationship

Lets say that we have two entities Entities: Bus (idBus*) Stop (idStop*,stopDescription) Where a Bus passes from many Stops and vice versa, therefore the cardinality is N:N. My ...