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

learn more… | top users | synonyms (4)

1
vote
2answers
55 views

One table or duplicate table with same properties but different dependencies

Disclaimer: I'm new to database modeling so I apologize in advance if the question is unclear or causes any confusion. I am using .NET Entity Framework and have the following tables: An Order can ...
1
vote
0answers
106 views

Foreign key to a subset of rows?

I have a table of Courses and it has a foreign key to Class_ID. A class teaches several courses dividing into several terms. So, Terminal is another table, it has a foreign key Class_ID to class ...
0
votes
0answers
10 views

Using db40 .db file in another pc

I am new to db4o. We are working on a team project in java using db4o and have inserted some objects in the database (say Cricketdb) on one pc. When I try copying the .db file and use it on my pc, it ...
0
votes
0answers
13 views

Organizing Educational Material

I worked with MC Access almost 10 years ago and not once since. Right now, I teach English and don't know any database lingo. I need some help in plain English. I've read some Q&A's here and it is ...
0
votes
0answers
51 views

Time complexity of query optimizer

I'm reading this article "How does a relational database work"(http://coding-geek.com/how-databases-work/) I'm confused by the section: Dynamic programming, greedy algorithm and heuristic. Using ...
1
vote
1answer
53 views

IOPS needs of Root Volume vs Database/Log Files Volume

I'm setting up a database server via AWS EC2, using: Root EBS Volume, General-purpose SSD EBS Volume, Provisioned IOPS And intending to configure PostgreSQL to have the database and log files on ...
2
votes
1answer
187 views

Data Model for Online Examination - Matching Questions

Good day. Forgive me if this question is a bit too open ended. I'm designing a module for a client that allows them to administer certification examinations via their website to seminar attendees. ...
0
votes
0answers
47 views

Can we combine Bitmap index with B+ tree index?

If I have a database of employees and expected queries on (salary,gender). Would it be a good idea to have a B+ tree index on salary and Bitmap index on gender at every leaf node of a tree. What are ...
3
votes
1answer
87 views

How to remove unreferenced rows in a “Many to One” relation?

in my db i have the following entities: Cities, Persons, Houses. Each Person MUST live in a City. Each Person CAN live in a House. Each Person can change or leave their Houses. Persons living in ...
0
votes
1answer
77 views

How does lock works when delete rows through secondary index in InnoDB?

step1: InnoDB Transaction Isolation Level Repeated Read. step2: Table schema CREATE TABLE `follows` ( `id` int(11) NOT NULL AUTO_INCREMENT, `followable_type` varchar(255) DEFAULT NULL, `...
0
votes
1answer
25 views

Normalising a database for monitoring stations

I have data about monitoring stations. For now it is stored in Excel tabs. I have decided to try to make a database. It will be my first contact with databases. Here is an example how it looks now in ...
0
votes
0answers
34 views

Are multiple paths between tables bad?

I have a system that builds online tests for school students. I have the following tables: SUBJECT (e.g. mathematic, science) INNER_SUBJECT (e.g. functions, matrix, multiplication) ...
2
votes
2answers
62 views

Table “normalization” and tradeoffs

It's been a while since my databases course in university, so bear with me as I try to be unambiguous here. In the domain I'm working in, there are Sources and Destinations. Both have 2D coordinates ...
3
votes
2answers
116 views

Design encouraging FK to non-unique/non-PK

My current design is makes me want to create a FK to a non-unique/non-PK. Is this a code smell and/or does my overall design make sense? CREATE TABLE [User].[ConnectionRequest] ( [...
3
votes
0answers
124 views

transaction replication causing too many deadlocks after adding a indexed view - what to monitor in order to avoid this?

I have created the following indexed view and its clustered index: This view was created on a subscriber database and all the tables used in the view are constantly being written to through the ...
0
votes
0answers
26 views

Fact Table with day grain that has many records that don't change

So I have a daily snapshot fact table that stores financial account balances. Normally, this would be pretty easy, just have a record for every day for every account. My problem is that table would ...
-1
votes
1answer
180 views

Database Schema for Maintaining Phone Contacts of users

I was building an Application where I need to sync user's phone contacts. Now since a phone contact in user's phonebook have N different fields, such as name, contact, work contact, email, email2, ...
0
votes
1answer
57 views

Flexibility vs Performance question

I'm in the middle of designing a database, and have a question for the veterans in regards to scalability. Currently, I have table A, which represents some object, and table B, which contains key-...
0
votes
1answer
74 views

Tables for two type of users in database. How to solve this problem?

This is the Entity Relationship Diagram I have created for my web application (based on Oracle APEX project). Two type of users can sign up and login and each user has his/her own profile and ...
1
vote
0answers
55 views

how to group transaction entries?

I have this transactions table on my dvd store app: Transactions +----+-----------+--------------------+---------+------------------------+-----------------------------------------+ | id | member_id ...
2
votes
3answers
238 views

Table naming and Database normalization

I'm creating a database of simulation results, and am trying to do so the right way. I've attempted to show relationships between data to make sure nothing redundant is thrown in. My current ...
1
vote
1answer
67 views

Interpreting Decision Time (DT) in temporal databases

Could anyone give me an insight for the need to model decision time in temporal databases and specifically how and why decision time differs from valid time and transaction time? What would be the ...
3
votes
1answer
92 views

What's the best way to store and search through many product specifications

Let's say I have a lot of products and all of those products have a lot of specifications. These specifications can change at any time (e.g. "colour" could suddenly be available for new products) and ...
1
vote
1answer
29 views

Model Entity Representation of an Organigram

I'm new to database and I'm trying to represent and organigram of a family (Great Grand father, Grand father, father, etc) using MER (Model Entity Relationship), and I'm quite confused on if I should ...
4
votes
1answer
240 views

Re-designing a Database cost and advice [closed]

The web application I am working with has a huge Database that is a real mess, no foreign keys, no constraints, no indexes, no optimization, no normalization, nothing. Until now, the logic has been: ...
2
votes
1answer
55 views

Should I backup a database with only temporary data?

I have a database, in SQL Server 2008, with only temporary data, but it has tables with a certain structure which I need. Should I back it up? And if I should, should I make one copy of it or should ...
2
votes
1answer
73 views

Address database design (subjective?) [duplicate]

I've been gathering information about how addresses in different countries are written. While doing that I've also been working on a database design to hold this world wide information most probably ...
1
vote
1answer
106 views

What is the optimum way to store, update, and analyze integer data for 100+ attributes?

I need to store the count of all individual characters (A-Z, a-z, 0-9, spaces, punctuation marks, all symbols...) that occur in different texts which will be frequently changing. I will need to ...
0
votes
0answers
44 views

MySQL Multi-source - Single Slave Setup. Adding Unique Master ID on Master's tables definition

this is a design question. I understand that Replication, is like mirroring. So this would mean, whats on the master, should be mirrored on the slave. (Is this always the case, like strictly same ...
3
votes
2answers
314 views

Use composite type to create new table

In ORDBMS databases I have seen that complex types can be used: create type name as( ...) And I can references those types when creating a new table, like: create table example (row_name ref(name))...
3
votes
1answer
177 views

Prevent explicit insert into serial columns

I want to prevent explicit inserts into serial columns. I have come up to the following trigger: drop table test_table; create table test_table( id bigserial primary key, foobar text ); create or ...
0
votes
0answers
57 views

Merge two data tables with different time preferably in access

I appreciate if I could help on this issue. I am relatively beginner to database designs. I have two tables (see link below) which I would like to combine: Tbl_1: Date, Product, Sold Tbl_2: Date, ...
1
vote
2answers
375 views

MySQL database column to Store Phone numberss

What is the best approach to store Phone Numbers (Mobile Number and Office Number) in MySQL table? Users may have MobileNumber (CountryCode and MobileNumber) and Office Number (CountryCode, ...
10
votes
6answers
1k views

Explaining 2NF vs 3NF with an example

I have a problem with second normal form (2NF) and I have been unable to solve it by using Google. It is making me crazy because I am a teacher and I don't want to teach wrong stuff to my students. ...
1
vote
0answers
23 views

Second normal form does not produce the desired result [duplicate]

I have a problem with 2NF and I have been unable to solve it by using Google. It is making me crazy because I am a teacher and I don't want to teach wrong stuff to my students. Let's have a table ...
2
votes
1answer
59 views

Design for user profile information and track changes

I have a web page with a login functionality. Logging in is based on email addresses. Also, I have to keep basic user profile information like: street city zip code mobile phone country contact name ...
10
votes
1answer
2k views

JSONB with indexing vs. hstore

I am trying to decide on the database design, with as few assumptions (regarding how the web app actually evolves) as possible at this stage. As a first step, understanding that JOINS are expensive, ...
5
votes
1answer
764 views

Should I use UUID as well as ID

I've been using UUIDs in my systems for a while now for a variety of reasons ranging from logging to delayed correlation. The formats I used changed as I became less naive from: VARCHAR(255) ...
3
votes
2answers
260 views

Many-to-many relationship when one entity is without attributes

I'm interesting in mapping many-to-many relationship to relational model. I understand that the basic approach is to create a third table (associative entity) that connects the entities in many-to-...
4
votes
1answer
197 views

Why do we need weak entity

As the title said, since we can convert any weak entity set to a strong one by adding the appropriate attributes, then why do we need weak entity sets?
0
votes
0answers
44 views

To Get Possible Conditions for provided Symptom and set of symptoms

Please look into my Schenario for symptom checker I am providing you sample table structure with sample data CREATE TABLE Symptoms ( ID INT, Title VARCHAR(100) ); INSERT INTO Symptoms VALUES (...
1
vote
0answers
303 views

Quotation and invoice mysql table

I am trying to develop a small business web application for my friends business. I do have some php,html experience so I have done the interface and all. Now I am trying to work on the database. The ...
1
vote
1answer
61 views

modelling grocery orders, that can be split into multiple short orders

I'm trying to figure out the best way of modelling a basic grocery picking system and something has stumped me. In short: how do I represent a single order that can be split into multiple parts / ...
5
votes
3answers
158 views

Tables with FK's of each other

I'm working on a database that stores address information. (Related to this question I posted) I have 2 tables: CREATE TABLE [dbo].[Country] ( [Code] CHAR (2) NOT NULL, [Code3] ...
9
votes
1answer
2k views

Could a null column be part of a primary key?

I'm developing a SQL Server 2012 database and I have a question about a One-to-Zero-Or-One relationship. I have two tables, Codes and HelperCodes. A code could have zero or one helper code. This is ...
0
votes
1answer
226 views

How to manage very large amount of data in mysql

I have a table in MySQL in which I write logs of users in a specified format. It have just 2 columns:id (auto-increment), log. Currently data is very less, so I'm using this. Now there is a ...
5
votes
1answer
148 views

Design Questions

I have a list of "suppliers" and "stores". Each supplier sell "items" to stores, but have special pricing depending on, say, geography or special agreements between supplier and stores. This is ...
0
votes
0answers
68 views

Design database table for property listing

I want to design a database for a listing website, this is the first time I design the database, so I am not sure if I in the correct path. Here is the fields that needed in a listing, title - ...
14
votes
3answers
1k views

What is the optimal data type for an MD5 field?

We are designing a system that is known to be read-heavy (on the order of tens of thousands of reads per minute). There is a table names that serves as a sort of central registry. Each row has a ...
2
votes
1answer
31 views

Associative property inside functional dependancy

I have a simple question regarding functional dependancy.. is AB --> C the same as BA --> C? thanks.