Database design is the process of specifying the logical and/or physical parts of a database. The goal of database design is to make a representation of some "universe of discourse" - the types of facts, business rules and other requirements that the database is intended to model.

learn more… | top users | synonyms (2)

0
votes
1answer
104 views

Benefits of using Staging Database while designing Data Warehouse [closed]

I am in process of designing a Data Warehouse Architecture. While exploring various options to Extract data from Production and putting into Data Warehouse, I came across many articles which mainly ...
0
votes
1answer
155 views

Inventory Management DB Design

*strong text*I am putting together a small inventory management system and have some questions concerning the database design. What I was planning on doing was to have 3 tables, items, bins and ...
2
votes
3answers
84 views

Is it better to start with a smaller INT types and grow as needed?

I am designing a new SQL Database for my ASP.net web application and I can foresee that some of the ID columns will get very large in the feature need INT and BIGINT but that will not be for a few ...
0
votes
0answers
52 views

Same table for real persons and fictional one

In my application stores both real and fictional persons (from movies, etc.) as well as stage names and alter egos. These entities have almost the same properties and are often referenced via foreign ...
0
votes
0answers
18 views

Junction table for peer entities and uniqueness for a link [duplicate]

In my database I have two tables: for peer entities (e.g. Persons) and for links between them (e.g. Friendship). Obviously, the latter table contains at least two references to the former table (e.g. ...
0
votes
1answer
36 views

Simple yet multilingual flashcards - proper database design

I'm making a simple flashcards app for personal use. I've thought about having such table: CREATE TABLE flashcards ( translation VARCHAR(50), entry VARCHAR(50), lang ...
1
vote
1answer
98 views

How does this database design look?

This is my first official design after having read a coursebook in preparation for the upcoming semester. The goal is to have a user-based form creation/publication and submission web application. ...
0
votes
1answer
55 views

Archery League Database design [closed]

I'm fairly new with database desings (only worked on a couple of projects) and i kinda need some info on how to contruct the following. I need to keep stats for an archery league and the players. ...
0
votes
1answer
63 views

creating a historical database, need help making a decision

I'm working with 5 processes that take about 8 hours to run, during each 8 hour process, it changes its location at random times but in a specific order. There could also be all 5 processes running at ...
2
votes
2answers
60 views

Deciding when to extend to child tables

I have a table of entities. For example purposes, lets call them Vehicles: Car, Boat, Motercycle, etc. I'd ideally like to place all entities in a single Vehicles table because a lot of related ...
6
votes
1answer
118 views

Better to use a RANGE RIGHT or RANGE LEFT Partition Scheme

I'd like to know if it is better to use a RANGE RIGHT partition scheme or a RANGE LEFT partition scheme? Is one of these options by default a better choice, or is it heavily dependent on the actual ...
0
votes
1answer
112 views

tables for different sources of payments

Selling software products. I have a table "licences". Each software licences is created due to a payment. I am creating tables to accept payments from different sources (e.g. manual or PayPal.) I'm ...
3
votes
1answer
122 views

Best practices for handling language

I am a coder, not a database admin, so my question may be a little obvious to some. I just cannot find the correct search term to find the answer. I'm trying to create a database structure for both ...
1
vote
0answers
93 views

KPI Metrics Table Design [closed]

We are currently working on project to get KPI metrics from out account partners,We want to design a table and It will have 3 Major Business units 4 Business Verticals (Service Desk, Knowledge ...
0
votes
0answers
33 views

Best practices for ranking records in flexible way

Suppose we have 10000 records of any product. User search on our website with his given criteria. so we output search results based on given criteria. Now Admin want to rank products in search. so ...
1
vote
1answer
76 views

Should I save “document ID” as a String or Number in MongoDB

So I'm importing about a 100,000 documents into mondoDB. MongoDB already generates an Object hash but the data has a documentID that was used to identify records in the previous database. The IDs ...
0
votes
1answer
31 views

Choosing the correct database type for single type of entry

So I'm building an application which constantly gets data from an API, basically the API outputs data through JSON which shows a certain speed for different hosts. What I want to here is query the app ...
1
vote
1answer
61 views

Better way to manage data integrity, without having too many composite FK's

I have following tables: class_table (class_id = Auto Incremented Primary Key (AIPK) of this table) user_table (class_id, user_id (AIPK of this table)) course_table(class_id, course_id (AIPK of this ...
3
votes
1answer
63 views

Sql schema design advice

This question contains no concrete programming question, i would only like to hear some ideas, on how it would be appropriate to design my database. So recently I started designing an application ...
4
votes
3answers
80 views

StackExchange clone: where should I add my indexes?

I'm creating an open source stack exchange clone and the following is my schema. What should I add indexes on for it to be optimal? Here is the schema in Rails format (SQL format below as well): ...
0
votes
0answers
59 views

Advice on DB Changes

Requirement: I'm required to prefix a couple of strings to the existing one. I.e. The current way, the Accounts been recorded in AccountID field looks as "621230093VN00" Under the new format ...
2
votes
1answer
64 views

Database design for storing historical nmap data

I am designing a database to store nmap data. I want to store two basic types of data: Information about the nmap scans themselves (e.g. start and end times, nmap version used, command line ...
1
vote
1answer
39 views

Disaster Recovery for tools such as InfoSphere Change Data Capture [closed]

I really like the idea of tools such as IBM's CDC to update OLAP tables from OLTP database logs. My only concern is one of disaster recovery. Let's say the db log is corrupted and we need to somehow ...
3
votes
1answer
60 views

Geographic Synonyms

Let's say I have a table representing principal country divisions (ex States): create table principal_country_divisions ( id int primary key, name text not null, country_code char(2) ); insert ...
1
vote
1answer
61 views

Proper implementation of child (derived) table PK/FK during single and multiple inheritance hard coded generalization physical data model

Below is an image of hard coded generalization UML logical model of entities. I'd like to implement these classes as tables in a physical data model. Two derived classes/entities (Class2 and ...
5
votes
3answers
136 views

Tracking changes on a table

I have a users table. I want to keep track of all the changes I have done,name,phone etc. I've thought to create an extra table changes that will store the id of the previous and the next version of ...
3
votes
1answer
96 views

Temporal Validity and Primary/Foreign Key Relationships

I've read through several of the oracle tutorials that show temporal validity and time features. However, in the examples I'v read, there is no primary key used in the demo tables. ...
2
votes
2answers
103 views

Database design for excel-like data

The task I need to store the some values in sequence in the database. The data looks just like a sequence of numbers. Here is an example of the data: Val_1 Val_2 Val_3 ... ...
1
vote
0answers
58 views

Relation between lossy decomposition and Natural join?

I am confused with the lossy decomposition and natural join. Natural join(with common attribute): only respective common attribute for the resultant table is formed. Lossy: Natural join on tables ...
0
votes
1answer
52 views

Normalise an expenditure table in a budget database [closed]

We have a budget database that stores expenditure items per budget. There are over 100 expenditure items each having common attributes: Amount, Frequency, Comment. The table will have over 300 ...
2
votes
1answer
34 views

DB2 MQT not rewriting role playing columns

If in a MQT, if I have 2 referential constraints to the same table (role playing) , usage of one of the constraints doesnt allow the query to be optimized Example FACT_SALES DIM_BILL_TO_ID --> ...
1
vote
0answers
38 views

Schema for yearly data models

I am developing a school management system. Most data will not be reused after the end of the school year. I am contemplating using schemas to separate data in various school years, however I am ...
2
votes
3answers
125 views

Database design for bookmark system

I am creating a bookmark system where people can save their bookmarks. Each user has its own personal page with bookmarks. A bookmark should have atleast one tag and max five. Each user can create ...
0
votes
0answers
42 views

Inserting and updating on same table

I am currently working on a database that aims to provide information regarding the amount of time people stay in a company's car-park premises. This is done using a table called "tbl_stay" which has ...
1
vote
1answer
47 views

Is it more efficient to store 0 and 1 for a binary value or just empty and 1 in MySQL

Is it more efficient to store 0 and 1 for a binary value indicating whether a flag is up or down or just empty and then set 1 for rows where the flag is up. Using MySQL. E.g. Flag 0 1 1 0 0 Or ...
4
votes
2answers
159 views

Is it bad idea to create foreign keys on the tables in a different schemas in the same database for big applications?

I am working on the transfer a big pl/sql web-based application to the dedicated server. This application is located in one schema with 70 packages of programm code. This application was made ...
1
vote
3answers
127 views

Uniqueidentifier vs. IDENTITY vs. Material Code --which is the best choice for primary key? [closed]

Which one is the best choice for primary key in SQL Server?There are some example code: Uniqueidentifiers e.g. CREATE TABLE new_employees (employeeId UNIQUEIDENTIFIER DEFAULT NEWID(), ...
0
votes
2answers
46 views

Store extra, optional information about any entity [closed]

I've done a bit of reading but haven't found anything related. This must be widely discussed, so it's most likely my fault. I've got a relational database with a number of different tables, each ...
1
vote
1answer
83 views

Polymorphic Association - is it bad? [duplicate]

In the following schema: Collections Upvotes Reports Upvotes Reviews Upvotes I'm tempted to have a single Upvotes table with a single "entityId" column that stores either the ...
0
votes
1answer
44 views

Database design implementing support for different languages [closed]

Today I was practicing database design, for example I was trying to build a database design where I would like to store my favorite shows. I have normlaised this database to the third form (at least I ...
0
votes
2answers
252 views

What is the best way to model an invoicing system with different forms of invoice items? [closed]

I am doing some preliminary database modelling for a Line-Of-Business application that I am creating. The application will be a Practice Management System for a medical practice/clinic. I am looking ...
0
votes
0answers
22 views

Assignment - build an E/R diagram [duplicate]

I was given a homework assignment to build an E/R diagram. I did almost everything, but I don't know how to treat one part: "politician can be both a governor and a mayor" how to change my diagram ...
0
votes
1answer
467 views

Database partitioning - Horizontal and Vertical sharding - Difference between Normalization and Row Splitting?

I am trying to grasp the different concepts of Database Partitioning and this is what I understood of it: Horizontal Partitioning/Sharding: Splitting a table into different tables that will contain a ...
0
votes
1answer
85 views

facebook group alike database scheme

Here are my requirements: one user can have many tasks one group can have many task one group can have many users only invited user by a user can be added into a group Does this db design appear ...
2
votes
1answer
81 views

Two-column foreign key constraint only when third column is NOT NULL

Given the following tables: CREATE TABLE verified_name ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, UNIQUE (name, email) ); CREATE ...
0
votes
0answers
52 views

MySQL: Error 1118 using create table select and barracuda file format

We want to create the equivalent of some materialized views in a read-only slave for really performant reporting. The queries used to generate these tables span 19 tables with a fair number of ...
1
vote
1answer
54 views

Is deriving a value using multiple joins a good idea? [closed]

I am working on a university project. I need to create a database from conceptual to implementation. That allows customers to create an order with multiple products the specification says that we ...
2
votes
1answer
158 views

Relationships between subtypes of an entity

I have a very big (hundred of milions rows) table which represent an entity (event) having many subtypes (different kind of recorded events). Each row is identified by an id (obj_id) and has an ...
1
vote
1answer
109 views

Database design for threading messages

I am not sure whether this question is suitable for this forum, but since it says Database Administrator I hope I am right. So my question is, whats the database design or business logic for creating ...
0
votes
0answers
92 views

MySQL Workbench entity-entity relations and ENUM data type

So I'm creating an EER model diagram using MySQL Workbench. I have a table task_types with only one column task_type_id, where I store a list of different types type A, type B,... That may be updated ...