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)

1
vote
1answer
46 views

How to design a database that tracks sports player's positions

I'm building a database for a sports (ultimate) tracking program. Basically, the "raw data" for the program will be the positions and IDs for all the players on the field at one time. But the way I'm ...
1
vote
1answer
24 views

Relying on expected data

I'm relatively new to Database Design and I wanted to know the disadvantages to a particular decision I have made in the design of a database for a project. I don't think it will have an effect on the ...
0
votes
1answer
53 views

Database design suggestions for a data scraping/warehouse application?

I'm looking into the database design for a data warehouse kind of project which involves a large number of inserts daily. The data archives will be further used to generate reports. I will have a ...
0
votes
1answer
19 views

Optional oneToOne relations and criteria field

Introduction: Visitors can fill reports on my website. Those report can either be filled by a legalPerson or a naturalPerson. Constraint: I don't like empty fields and I would like to normalize as ...
1
vote
0answers
40 views

Conceptual model vs Logical model vs Physical model

I'm doing a work about database, and now I need to show three different images, one image with the conceptual model, other with logical model and other with physical model of a database. I'm looking ...
2
votes
1answer
50 views

Conditionally join table

I'm new to database design and have designed a database that seems fine for the application but looks like it has a major red flag to me. I have a master table which is then joined to another table ...
0
votes
1answer
40 views

Parent/Child table design?

Lets say I have a table to hold Tasks and then I have a table to hold Sub Tasks, is it better to break Tasks and Sub Tasks into separate tables or keep them both in the same table with a possible ...
1
vote
2answers
32 views

Store n:m data with most n:1 tuples

I have these tables: create table data ( id int identity not null, ref int not null ); create table reference ( refid int not null ); with ~2 million rows in data and ~200k rows in ...
-1
votes
1answer
44 views

Database Design for classifieds site [closed]

I'm creating a classifieds site where there are many categories and subcategories. 1) Each category has different fields. 2) Each listing has one or more images I need help with the database design. ...
0
votes
2answers
44 views

Auto increment char varying column prefixed with some characters

I have a table in PostgreSQL for hospital patients and I want to make a primary key for it as PAT0000001 (char varying) and to keep that field as auto-increment as well. Is there a way to achieve ...
0
votes
2answers
34 views

Mysql replication with c

I have huge database on MySql and It's become more and more huge. So, Now I am facing performance issues with my database. Some of tables has millions of records in it. like my login detail table ...
0
votes
2answers
69 views

Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases

We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL. In our application, we are planning to upload customer’s data using a Excel sheet and these data ...
0
votes
1answer
53 views

Need Advice on Normalizing Database

I have a group of database tables that lack normalization. They all have a similar primary key called 'doc_index', which is either a PK by itself or part a composite PK. As you can see below, there ...
3
votes
5answers
255 views

Using Timestamp plus Integer as Primary Key

In Sql Server, what would the drawbacks and disadvantages be of using a primary key that is: Two Character Table Identifier + YEAR + MONTH + DAY + HOUR + MINUTE + MILLISECOND + random integer between ...
0
votes
2answers
61 views

How to change schema so that account_id reference is unique among 3 tables

Original image: Updated to have more-correct terminology and an 'is_debit' column: I am designing a (PostgreSQL) schema for a lottery website that uses double-entry accounting. 'jackpots', ...
0
votes
2answers
28 views

Database design for tracking referral dollars and referred dollars

Dropbox has a great referral incentive program that gives a storage bonus based on whether a person is referred to Dropbox or refers others to Dropbox. What data base design is good for managing ...
1
vote
1answer
30 views

How to handle multiple hierarchies in a data model?

In Sql Server, there is a datatype called HIERARCHYID which helps with hierarchies. In our environment, we have patient data that needs to be classified under multiple hierarchies. Example 1: ...
1
vote
2answers
37 views

Dimension or Degenerate Dimension?

I am taking a course on data warehousing. Currently, I am reading through Ralph Kimball's Data Warehouse Toolkit. I understand that a degenerate dimension is a dimension key that is stored in a fact ...
1
vote
2answers
63 views

How to find usage of each object in other objects from a database?

I am with the task of taking an old SQL Server database and identify all of it's objects and their relationships, because the one who made it did not bother to use any foreign key, he just made all ...
0
votes
1answer
104 views

Database revision needed for sports events database

This is not a code review but more of a database schema review. I have a sports events website that I am doing in my spare time. I need assistance with the database design. I am about 80% completed. ...
0
votes
1answer
70 views

Inserting data into multiple tables

For my system, students are required to create a user account by completing a registration form (personal, contact and course details; also username and password). I need to collect registration data ...
0
votes
0answers
15 views

When there is a Type 1 and type 2 SCD in a dimension table, how are updates to the type 1 column handled after the type 2 column is updated?

Eg: ID Type1SCD Type2SCD FromDate todate 1000 100 ABC 01-JUL-2014 NULL Now, Type2SCD is updated ID Type1SCD Type2SCD FromDate todate 1000 100 ABC 01-JUL-2014 12-JUL-2014 ...
1
vote
1answer
36 views

pass data from deleted table to another

This is the situation: I have a table, with name TABLE_1 I would like delete TABLE_1 and then create another table with the same name and some of its columns. TABLE_1 has records, but I need to pass ...
0
votes
0answers
38 views

performance questions in database design

I need to describe a set of studies that are practiced to a patient, every patient have a "Clinical History", that are described in another table called "Clinical History Lines"(hclines) Every visit ...
1
vote
3answers
209 views

Heterogeneous ordered list of value

I have the following object model: class A { List<Thing> orderedListOfThings; } interface Thing { } class X implements Thing { int attr; } class Y implements Thing { String ...
0
votes
1answer
29 views

DBF corrupted database

I'm in great trouble here. I'm using Microsoft Visual FoxPro 9.0 on my Windows 7 machine. I had some DBF files which was created in MS Visual FoxPro. The files were undoubtedly valuable since they ...
0
votes
0answers
14 views

What is the optimal way to get a tabular view from an Entity-Attribute-Model table?

Following up on my previous question, I have created a table for storing measurements of different physical quantities. The schema looks like this: CREATE TABLE sensorreadings ( time TIMESTAMP, ...
0
votes
0answers
13 views

A better database model for level of product-information in service and maintenance environment

This is a tough question and rewriting it now for a couple of times. I have a question about a good, more solid way to design my database model about service and maintenance. My main problem are the ...
0
votes
0answers
40 views

Database design for large incoming data

I've been tasked to design a database to handle a large volume of incoming traffic from sensor readings, possibly 1,000s an hour when it's at full strength. Each reading will be relatively short - ...
0
votes
1answer
41 views

How to model when you could have many (thousands of) attributes to a table (product)?

We have a main table, let's say, Product. It has several general attributes like 'Product Name', Manufacturer ID, etc. As of now, all these attributes are implemented in the data model as columns to ...
1
vote
1answer
65 views

Handling a chat room in a database

I have been searching all day to find a better answer to my question but those I found was useful but do not critically analyse my question. I am building a chat room using php, mysql, jquery and ...
0
votes
0answers
59 views

How to share Users table between Clients and Customers?

I have a rental system where the system admin can add clients and their users. Each client's user can manage products, categories, orders etc., as well as their own customers. However, while ...
0
votes
1answer
30 views

Mixed search in multiple columns

I have a table which has a particular column of type JSON, where some user's contact information are stored. I can alter the table and add new columns with cached values to increase search performance ...
0
votes
0answers
18 views

employees Evaluation System database

I am new to database designing I want to design an Employees Evaluation System for a company. Based on Competencies Rating Each Manager will rate his/her employees at the end of each year for 6 ...
1
vote
2answers
26 views

getting one day timestamp in mysql

Please consider the following stored procedure I have: ORIGINAL Stored Procedure: -- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments ...
1
vote
1answer
79 views

Enforce referential integrity between two groups of data

I have two sets of records: A collection of rules. A collection of actions to perform based on those rules. What I'm trying to model is something like this: Multiple rules can be grouped ...
0
votes
2answers
30 views

primary_key problem in MySql view

I have 3 tables. and I want to create a view. There is no problem about generating SQL qeury and creating a view. but when I want to use this view in my web app it says me (LIKE THIS): "your view's ...
0
votes
1answer
42 views

SQL or NoSQL for structured clinical data [closed]

I'm designing a database for a primary health center, the idea We basically have a Patient with many Encounters, also the Encounters have to be entered from different Users (Health Professionals). ...
1
vote
2answers
127 views

Optimizing large MySQL SELECT WHERE IN clauses

How can the following SELECT WHERE IN case be optimized? I have a table with over 100 million rows with only 3 columns. The primary key (col1) is a 127 varchar. I am performing a SELECT col1 WHERE ...
-1
votes
1answer
56 views

database normalization to 3NF [closed]

I am just at a complete lost. I need help with this. There should be 12 tables (or perhaps 11 since one table has just a single attribute) when you are finished with the normalization and merging of ...
2
votes
0answers
29 views

How do I design a database for different sets of measurements on different subjects?

I'm conducting a research project that consists in sampling different physical quantities on about 30 buildings, most of them single-family houses. Some of those physical quantities will be common to ...
1
vote
1answer
17 views

Datatype for different categories in a database

I have got the following categories for accounting and cash flow purposes: Equity Contribution Distribution Valuation Debt Issuance Payment Valuation Allowance Interest ...
0
votes
2answers
28 views

Design for table with multiple links to other table

I am designing a database to keep track of our network equipment. My db has 2 tables: sites & circuits (circuit = cable) & (site = property location) Each circuit runs between two sites, so ...
1
vote
1answer
55 views

Update new column with value from old columns for all the rows

I've got an odd db design in place that was here when I got on my new work space. We have a column named params which contains some string data which appears that it belongs in more than one column, ...
0
votes
0answers
23 views

set error message on out variable in mysql stored procedure

I have a stored procedure: fill_DATA (serial varchar(20), OUT oError) I am doing an Insert operation here. so what I want is to SET value of oError for any error message in case the query fails to ...
0
votes
0answers
37 views

MySQL Table with 568 columns: 1118, 'Row size too large…'

I'm currently migrating a database from Oracle to MySQL5.6. One table consists of 568 columns. When atempting to create the table I received this Error 1118, 'Row size too large..' I already ...
1
vote
1answer
60 views

Data modeling with many attributes where few apply to each record [closed]

I'm pretty new to data modeling, so please excuse me if I've missed something obvious. I'm trying to decide on a schema (or possibly a DBMS) to work with some data where there are quite a few possible ...
1
vote
1answer
44 views

CRM Address table and it relation

Hi I want to ask about I have this DBD for my CRM I am trying to have the addresss in one table(customer and Employee Address) and both customerID and Employee ID are guid I have in address table FK ...
0
votes
1answer
50 views

Pros and cons of master status table vs private status table?

I am the only DBA at our company and am relatively new in the profession (having 2.5 years of experience now). The developers and I are currently having a discussion about how to properly design the ...
4
votes
2answers
140 views

Database instance and database

I did some research and finally came to this conclusion: Database instance = processes + memory Database = (physical) log files + control files + data files. Is this understanding correct? ...