0
votes
2answers
36 views

How to store user's language knowledge (150+ languages) in mysql DB

Users in my website must be able to select languages they know (English, German, etc), so I have a table languages with 150 languages (id, name) and table users (id and a lot of other fields). ...
1
vote
1answer
34 views

Should I drop this column or is there a query that can achieve a results table without duplicate columns?

I use a column named active in several of my database tables to adjust whether a particular entry/row/user/etc should be included in future query result tables instead of deleting the information. ...
0
votes
0answers
28 views

Storing DB credentials in an application source code?

I am making an app which connects to a DB, and the app will have plenty different userslogins. Is it a good design choice to have a specific DB login/password for the application, using which the ...
0
votes
2answers
29 views

Need Help maintaining multiple databases for a multi-tenant application?

i need some information about multiple databases for a multi-tenant application ? I am working on a project in which we are maintaining separate database for every company? The Companies Database ...
0
votes
2answers
52 views

How to break table into two without losing performance?

According to http://stackoverflow.com/a/174047/14731, splitting away infrequently-needed columns frees up the cache allowing faster retrieval of the commonly-used columns. I've got a table whose ...
3
votes
4answers
113 views

How do I prove that the act of removing foreign keys doesn't corrupt existing data?

TL;DR: Prove that in practice, the execution of the alter table table_name drop foreign key constraint_name statement does not corrupt existing data. The important consideration is the execution of ...
0
votes
1answer
34 views

Schema for multi-account database

I'm building an app which will have accounts for organisations leading to organisation.myapp.com subdomains for their account access. I'm putting together the schema for it and I have a few ...
0
votes
0answers
22 views

Correct Schema Design for form builder? [closed]

I'm attempting to build a form builder app that allows users to: signup build a form store the data from that form into this db. I have designed the following schema: ...
0
votes
0answers
19 views

Conditional value lists in MySQL

apologies in advance if the language I use here is insufficiently specific. Please let me know if there's anything that I can clarify. Does MySQL provide a means of creating conditional value lists a ...
0
votes
2answers
38 views

What's the use of assigning foreign keys on MySQL [duplicate]

Aside from indexing the value of the foreign key, what is/are the advantage of assigning foreign keys to databases? I've seen a lot of databases that does not assign foreign keys, but is relational by ...
1
vote
2answers
40 views

how to design a database's table to store mysql spatial data in a parent child relation?

I want to store locations and polygon data in a parent-child relationship. I am using spatial data in MySQL and Google maps for polygon. Examples: 1. World 1.1 US 1.2 China 1.3 Japan 1.4 South ...
1
vote
1answer
41 views

Should I add new column or new table

I have an existing table called exampletable1, now i have been given a new list of data called "medicines" it only have: male,female as its value. I'm having a problem whether to add another varchar ...
3
votes
4answers
196 views

Best practice for a Table with private columns

I have a table with user data, where most of the columns can be public or private (so it will be shown or not on the app) and every user can decide which fields to hide. So far I had a couple of ...
0
votes
2answers
25 views

Creating a database with only MySQL command line?

The linux server on which I'm attempting to create a MySQL database has many difficult to work with permissions. My supervisor has tried to install MySQL Workbench on the server, but has been unable ...
1
vote
2answers
120 views

Messaging Database - How to handle multiple recipients

So I am trying to develop a relational database (I'm using MySQL, planning on a Java front end) where I have 3 tables (People, where I store names and relevant personal information... and Messages ...
2
votes
2answers
52 views

Give every user 3 default 'bookmark' entries

I've got a table users and a table bookmarks. The table bookmarks obviously contains pages the user wants easy access too. But, there's a new requirement now: every user gets 3 unmutable bookmarks. ...
0
votes
1answer
55 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 ...
1
vote
0answers
45 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 ...
0
votes
2answers
79 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
82 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
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 ...
1
vote
2answers
27 views

getting one day timestamp in mysql

Please consider the following stored procedure I have: ORIGINAL Stored Procedure: -- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments ...
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 ...
1
vote
2answers
136 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 ...
0
votes
0answers
27 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
38 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 ...
0
votes
1answer
54 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
44 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
1answer
27 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
90 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
90 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
17 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
4answers
254 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 ...
1
vote
1answer
28 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 ...
1
vote
1answer
88 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
57 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
54 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
42 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: ...
2
votes
1answer
46 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
6answers
468 views

Database Normalization - Can one field point to another?

This may sound like a dumb question, but I am a software developer new to database design... So the concept makes sense to me, but maybe this doesn't carry-- Is it possible for a field in a record ...
2
votes
2answers
211 views

Design of an application log database

We are generating logs like following example(this is a table no actual pipes): 2014-06-10 09:00:03.457 | Channel1 | Operation3 | Function15 | 15ms 2014-06-10 09:00:08.245 | Channel2 | Operation5 | ...
0
votes
1answer
59 views

Need some expert eyes to look at my schema

I am trying to make an online site for local movies. Will be written in PHP and will be using MySQL as the database. What I want is to create a mini IMDB type site, with movies, people(casts/crew), ...
0
votes
1answer
92 views

Split MySql table into two tables with One to many Relationship

I have an old database table in MySql. It is used to store the orders from online ecommerce. Simplified diagram looks like this: +----+------+-----------------------+-------+--------------+ | id | ...
0
votes
0answers
31 views

Performance and maintainablity of a large MySQL table with hundreds of columns

For a project of mine I need to have a table with around 400 hundred columns of type nullable indexed VARCHAR(128), this table is going to store at least 50 million rows and has one column of type ...
2
votes
0answers
36 views

Proper design of routes in Database MySQL

I'am trying to find out the best database design and try to stick with data integrity by using Foreign Keys. But I'm stuck right at the beginning because of limit relations between tables with FK to ...
-1
votes
1answer
31 views

Lookup tables and performance

I am using 6 lookup tables in my mysql database. There will be frequent insert,update and delete queries in the db. Will that badly affect the performance. Do I need to save the data as it is without ...
2
votes
1answer
41 views

Peer review database design for various reports

I'm currently trying to redesign a poorly thought out database for a peer review system that we use internally at my workplace. Employees can log on and submit a review about another employee who ...
0
votes
1answer
80 views

desing the database for survey app with various types of answers

I have to create a survey app for which answers should be stored in the database. I created an html form containing some questions (about 30) with various types of answers (check boxes, radio buttons, ...
0
votes
1answer
166 views

Database design for mobile app

At first I would like to say hello to all of you, since it is my first post here on DB Administrators. I am writing an app which store drinks and their data. At the beginning it was storing all in ...
0
votes
1answer
110 views

Multiple product attribute inventory database design

i have a database to build based on inventory for my shop. this is the current data that i have put into tables this is the package: this is the product attribute this is the table to produce ...