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 (3)

0
votes
1answer
536 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
75 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
78 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
846 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
53 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
269 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
183 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
1k 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
165 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
147 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
160 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
196 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
96 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
262 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
7k 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 ...
1
vote
1answer
39 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 ...
1
vote
3answers
287 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
0answers
130 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 - ...
1
vote
1answer
151 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 ...
3
votes
1answer
208 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
1answer
323 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 ...
1
vote
2answers
202 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
219 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
43 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
130 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
758 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
220 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
35 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
24 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
44 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
617 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
86 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
141 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
394 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
116 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
199 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? ...
0
votes
0answers
175 views

Uploaded files corrupted except txt/csv

I have an ASP.NET MVC 4 Application with standard CRUD functionality plus a file Upload capability. Currently I am struggling with the files being corrupted after the download. I created a ...
0
votes
2answers
146 views

Are #tables and #procedures 'not best practice' or 'bad practice'?

I am working on a project and part of it is an interface tool that executes SQL code stored in a xml file to import/export files. In the file, temp procedures and temp tables are used quite heavily ...
0
votes
0answers
101 views

Having a Table to store information about “Feedback”/“Enquiry”/“Complain”

I am creating a database for an application and I want to be able to capture feedback information (Enquiry, complains or comments) from users using a form. My question is : Do I have to create a ...
2
votes
1answer
225 views

How do I add versioning to this CMS I'm designing?

As you can see, my CMS consists of several widgets (for example an Articles widget), of which you can create several instances, which I call blocks, and those blocks are linked to pages and the ...
0
votes
0answers
79 views

Store both a city and its region?

I store various events (such as a search on the site) into a table (in fact I have two tables: one for the event and other (linking to the first) with characteristics of the event). Each city belongs ...
0
votes
1answer
28 views

single table or different tables [closed]

I am very new to PHP & Mysql. I am currently working on database structure of website. I have been a keen follower of this website. I have found that many posts have discouraged use of multiple ...
0
votes
1answer
113 views

Optimization for 100 GB Real-time info table

I have a table of device information feed which is filled by data from remote device units. There will be like 1000 units each sending an update per minute which will be stored as a row in that ...
1
vote
1answer
814 views

Time series and gap filling [closed]

I have to choose a database server for storing big time series data. Currently I use PostgreSQL, but I've found that Vertica supports automatic gap filling for missing data and that's really nice. ...
-1
votes
2answers
67 views

query multiple unrelated tables

Just learning mysql and db so guess this is a general schema design question. Say i have 10 different types of widgets and each widget transaction has a date, amount and 8 other unique columns to that ...
1
vote
1answer
251 views

Personal and Global messaging database design

I am trying to implement a database design system that will be sending messages to a person(thus 1 to 1) or many persons (1 to many). table --users |u_id | Name | | ...
1
vote
3answers
269 views

Relational Database design best practice

I am a student and I am trying to design a simple database, so if the ideas seem bad please let me know how to improve. I have three tables in my mind, one for Professors and one for Students. Both ...
1
vote
1answer
43 views

Understanding how relationship sides works

So, I have this relations on my tables (see image) and I need to understand some things. I'll enumerate what I understand and what I need from yours is to say if I'm right or not and in case I'm not ...
0
votes
0answers
1k views

eCommerce product table design - MySQL

I am designing database for an web application, I have designed the product and specification details database as follows, create table pr_size ( size_id int not null auto_increment, ...
1
vote
1answer
44 views

compare file-based systems and a relational database

I don't know the exact difference of both can you please compare file-based systems and a relational database?