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

learn more… | top users | synonyms (4)

2
votes
2answers
110 views

Database design for web mail client

I am implementing a web-mail application.Only registered users can send, receive and view E-mails. When I start to design the database,I came up with following issue. Lets say this application has ...
1
vote
1answer
77 views

Should child comment be treated as separate table?

I'm building a commenting system, currently my table indicates which comment is child with parent id in 1 table, I would like to know what is the best way of doing this? Current Table Scheme (1 ...
2
votes
1answer
113 views

indexing strategies

I am confused by indexing strategies advocated by one of the database products. Database is "DB2 for i" (iSeries/AS400). Please see following SQL and index advised by the product for the SQL. There ...
0
votes
1answer
40 views

db table column design question

I have a database that will track "location" in a building. Location1 contains country data. Location2 contains building data. Location3 contains room data. Each location table has a FK to the ...
4
votes
2answers
102 views

Lineage tree database design [duplicate]

As the title says, I'm building a cannabis strain genetics/lineage database. If you didn't know; one cannabis strain may be an original strain (no parents) or it could be the result of many crosses ...
0
votes
2answers
57 views

Making database tables 1NF

I am creating a database for a DVD rental shop, I have various entities that are related to this question, such as Film, FilmStar. For each film, you record its unique number, title, the year in ...
0
votes
1answer
37 views

What is the best way to create tables for email campaigns?

I have table email_sent with fields: id user_id template_name sent_at opened clicks And now I need to save data which was send for each email. I have several campaigns with differnt data. For ...
0
votes
1answer
99 views

Is there a setting in MySQL that will allow you to see the columns of a table on a query, even if the table is empty?

When I am writing code to go with a certain MySQL query, I like to do a basic select statement to see the columns, and to perhaps see what data is available as a sample. Sometimes, the table I would ...
0
votes
0answers
19 views

Turn off Key constrain enforcement or change database structure?

I'm updating a POS system, it allow to make credits, orders, cuotes, and obviously direct sales, each of them have their own special properties, for example if the transaction its an order it must ...
1
vote
2answers
134 views

How to change dbid in oracle to specific value?

I want to change dbid of oracle 11g database. Current dbid = 2241883xxx And want to set dbid = 1380515xxx
1
vote
2answers
48 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 ...
0
votes
0answers
79 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
9 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
46 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
35 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
130 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
43 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
77 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
53 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
22 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
32 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
59 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
115 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] ( ...
4
votes
0answers
66 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
91 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
54 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 ...
0
votes
1answer
58 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
52 views

how to group transaction entries?

I have this transactions table on my dvd store app: Transactions +----+-----------+--------------------+---------+------------------------+-----------------------------------------+ | id | member_id ...
2
votes
3answers
137 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
43 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
62 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
26 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 ...
0
votes
0answers
45 views

Is it a good idea to disable lock escalation while adding new calculated columns to a table?

Having in mind that I can add computed columns to the subscription tables according to this link: SQL Server : Transactional Replication Computed Column Is it a good idea to disable lock escalation ...
4
votes
1answer
231 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 ...
1
vote
1answer
53 views

Address database design (subjective?)

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
103 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
41 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
141 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 ...
3
votes
1answer
119 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
44 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
187 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
708 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
44 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 ...
5
votes
1answer
514 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, ...
4
votes
1answer
224 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
105 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 ...