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
114 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
32 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
64 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
291 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
93 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
20 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
31 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
114 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
52 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
57 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
73 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
86 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
61 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
160 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
87 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
84 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
40 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
91 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
1answer
26 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
65 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
votes
2answers
31 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
61 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
102 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
33 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
351 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, ...
0
votes
1answer
18 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?
0
votes
1answer
111 views

Duplicate Relationships in Rails/MySQL Healthcare Application

Overview of the Application I am building a Ruby on Rails application to organize medications, using MySQL. The goal of this application is to provide dosage specific treatment recommendations, and ...
0
votes
0answers
21 views

Redesign tables, multiple foreign key and related table to same table

Hello I have this database: Table Teams is used in each one table. Match table contains teamA and teamB and round contains team that win the SET and team that start the set. But I dont know how I ...
0
votes
1answer
62 views

How to model ski rental equipment

I've spent a good amount at time at looking at related posts regarding products for rent but still have a few unanswered questions. If we consider the just the requirements at hand which is how to ...
0
votes
4answers
285 views

Database/Storage engine suggestion for a project involving heavy inserts?

I am looking into a project which involves large number of inserts daily.I will have a list of user's (for example a user set of 500k ) , for which I need to monitor daily certain activities ...
2
votes
1answer
57 views

Table income and expense is better to have it together or apart

I am developing a small system where I have to record incomes and expenses of the company, and even I can not determine if the income and expense table should be in one table or in separate tables. ...
1
vote
1answer
38 views

How to create trigger with NOT FOR REPLICATION

I tried adding "NOT FOR REPLICATION " in many parts in the below code but ended up in the below code. I searched a lot for an example where I can see how its done with a trigger. Can any one show me ...
0
votes
2answers
54 views

One table with a flag or two tables

I have a problem with the design of a database: In my Android application i need to store the upload (the status, so if the file is in upload it will report the % of the upload) of a file and a list ...
0
votes
1answer
32 views

Need DDL for an Oracle Table that would be 1 row = 1 8K Block

For performance testing in our performance environment we want a means to turn over SGA the way production does. Our performance environment is a fraction of production and production's very nature ...
0
votes
1answer
114 views

Best relationship database design to manage types for different entities

Suppose we have to manage phone types for: Clients, Employees and Suppliers. Each of these can have multiple phone numbers, so for better management I would like to add phone type. Each entity can ...
1
vote
1answer
82 views

Difference between Relational database and time series database?

I have a bit stupid question maybe. I recently came across a term "time-series database". I would like to ask what is time series database and is it same as relational database? Thanks,
0
votes
2answers
33 views

Do I need foreign keys in ORMs?

Object Relational Mapping (take ActiveRecord and Rails, for example) allows a programmer to define relations between models, and callbacks on delete. So my question is: do I still need to define ...
0
votes
1answer
66 views

Different type of column

Maybe the title is misleading but I'll try to explain what I'm trying to do. I'm building an e-commerce site. I have my products and my order. I want add constrains on how the order will be deliver ...
1
vote
0answers
119 views

How do you think in NoSQL? [closed]

I'm from the pre-nosql era and am rather comfortable with SQL. However, I cringe whenever someone suggests using a NoSQL database for the "upcoming" project. There are some major problems that I'm ...
1
vote
1answer
167 views

What is a good/healthy mysql query execution time?

What is a good/healthy mysql query execution time? To my calculations: The site gets 1000 unique visitors per hr 10 page view for each unique visitor 5 minutes for each unique visitor ...
1
vote
1answer
74 views

Reviewing relational database tables

I am building a database, but I am a little bit confused with my table relationships. I wish someone could help review my table relationships below for me and tell me where I am going wrong. I have ...
0
votes
2answers
58 views

Help required to design a database [closed]

Firstly I would like to apologies if this is not the correct place to ask this question but I know how good community spirit is around here from my time in Stack Overflow. I am very new to the world ...
1
vote
0answers
57 views

Dealing with Multiple User Roles

I'm working on a project based on a CMS, where there is a built-in roles table. However, the CMS allows a user to have one role only using role_id And these are the requirements that struggling me: ...
1
vote
3answers
241 views

Many one-to-many relationships between two tables

In the process of creating a small database for a therapeutic boarding school with Access 2013, I have a problem when designing the relational database model. Two times at least, I ran into a ...
0
votes
0answers
15 views

Trying to get BCNF out of this relation

The table is: Employee ( EID, Ename, PID, ProjectName, MID, ManagerName ) For the functional dependencies I got: EID==>(Ename) PID==>(ProjectName) ...
3
votes
1answer
138 views

Row Level security or Multiple Tables

Im looking for documentation on best practices for the following scenario. A hosted application contains some "global" data and some "Per-tenant" data. A "Tenant" should have no access to another ...
2
votes
1answer
30 views

Save different account types in a database

I have the following issue. In one application (imagine a messaging/e-mail app on your smartphone), the user enters its accounts and gets information from the server displayed. I need to store the ...
2
votes
1answer
64 views

Optimal table design for a medical center rental service and recurrent shifts

I'm trying to figure out the optimal design for the following situation I'm being presented. I'm developing a medical web application with MySQL as db engine. A medical center has 14-16 offices/rooms ...
2
votes
3answers
109 views

Entity Relationship Model - double one to many

ERD A customer submits one or many leads. A lead belongs to one customer. A lead is a request or an inquiry. A lead belongs to one manufacturer. A manufacturer has one or many leads. Tables ...