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

learn more… | top users | synonyms (4)

2
votes
0answers
27 views

Creating UNF for multiple actions

I am creating a database design for manufacture company for my coursework. The database design should be able to do these tasks: Buy items from suppliers. (M - N) Items are used for making products. ...
7
votes
6answers
1k views

Possible benefits of storing multiple values in one field of one row instead of as separate rows

During our last weekly meeting, a person that has no background experience in Database Administration brought up this question: "Would there be a scenario that justifies storing data in-line ...
0
votes
1answer
40 views

Best to isolate the database in multiple instances?

I read on a forum about the use of database isolated environments are neccessary for any large project that handles "big" data. For instance I will be using the following table for ALL blogs: ...
1
vote
1answer
85 views

Database Design and structure

I want to make colleges search system where a user enter any keywords in a search box and we can provide them some institutes with their details like institutes names, belonging hotspots, cities, ...
2
votes
1answer
47 views

For a software application, does every workstation need database drivers?

really a just a newb question. In the case of something like an enterprise system for a company, where end-users are executing continuous reads and writes to a database, it does not appear that in ...
0
votes
0answers
46 views

DB table design for attributes with multiple entries (eg: Person -> Social Media)

I am trying to decide the structure of a DB table to store Employee data. I am bit confused on how to design attributes such as Address, Social Media etc. There should be provision for multiple Social ...
0
votes
0answers
29 views

What is the db-structure behind sites with multiple similar subdomains, like Stack exchange?

own db for every subdomain 1 big db used by all of the subs own and shared db-s used by the subs When to use the 1st/2nd/3rd approach? Thank You!
1
vote
2answers
37 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
150 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
27 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
38 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
125 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. ...
5
votes
1answer
87 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
69 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 ...
2
votes
2answers
44 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
53 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
317 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
129 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
165 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
592 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
34 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
64 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
74 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
48 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
52 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
111 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 ...
2
votes
2answers
132 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
50 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 ...
4
votes
1answer
118 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
33 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
111 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 ...
4
votes
1answer
136 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
56 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 ...
2
votes
1answer
62 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
30 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 ...
0
votes
0answers
21 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
29 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
34 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 ...
3
votes
2answers
82 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
126 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
81 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
37 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,...... ) ...
0
votes
0answers
43 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 ...
1
vote
1answer
30 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
37 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
263 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
26 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 ...