Tagged Questions
1
vote
4answers
21 views
Grounds for having a one to one relationship between tables
If I have two different types of user, Parent and Child. They have identical fields, however a Child has a one to many relationship with exams, a relationship that does not exist for Parents.
Would ...
1
vote
1answer
26 views
Exponential increase in query time?
For the last couple of days I have been working on normalizing our 600GB database.
I have broken out all redundant data into 4 separate tables plus a main entry table.
All is well so far, but the ...
0
votes
2answers
22 views
Proper way to update the parent key in a child table for multiple rows after an update in the parent
I have 2 tables that essentially have a parent-child relationship. So the child table has a column that references the primary key of the parent table.
In a nutshell the parent table is like an ...
0
votes
1answer
47 views
Database Design , Items in Category, Sub Category & Theme
CREATE TABLE Product (ProductID int, Description nvarchar(100))
CREATE TABLE CategoryID (CategoryID int, Description nvarchar(100),ProductID int)
CREATE TABLE SubCategoryID (SubCategoryID int, ...
1
vote
1answer
22 views
Creating relational tables combined or seperated? (in MySQL)
I'm creating a MySQL database and I'm not sure how to create relational tables for best performance.
I have ships and cars tables. Each car or ship will have a few pictures. I think I have two ...
0
votes
2answers
29 views
better performance on database design
I'm working on an web app that has a grading system. For this I have a fixed 10 different types of exam.
exam_1, exam_2, ... exam_10
that is per student. The case is the student will take a random ...
0
votes
0answers
27 views
Regularly Updating a Large Database
Evening,
I am building a web crawler, which will save URLs in a database, which will have three columns:
id (BINGINT,20) url (VARCHAR,21), indexed (INT,1)
1 fsdfsdfsdf 1
2 ...
0
votes
2answers
42 views
Using HAVING OR WHERE?
I have a list of "items" which all have a "title" and contain a group of "tags":
$query = mysql_query("SELECT i.id, i.title
FROM items AS i
LEFT JOIN tag_rel AS tr ...
1
vote
1answer
45 views
600GB Normalizing from CSV or from Database?
Hi I am just now starting to normalize a 600GB database. We are moving it of Amazone RDS using sqldump to our local server (64GB RAM and 1,5TB SSD) where we will host the new normalized database.
The ...
2
votes
2answers
33 views
Saving array data in normalized table
I am updating my user management system with custom user settings feature. In other words, I want to save custom user settings/options in database. Initially I considered serializing the data and ...
3
votes
1answer
72 views
Enhancing the Database Design
My CMS Application which lets users to post Classifieds, Articles, Events, Directories, Properties etc has its database designed as follows :
1st Approach:
Each Section (i.e 'classifieds','events' ...
0
votes
2answers
28 views
Normalizing Date(yy-mm-dd) or stick with datetime?
We are re-factoring our database, we will be adding about 100.000 rows to the db a day.
Each Row now contains a date field (yy-mm-dd), So today we entered the date "2013-11-29" 100.000 times into a ...
1
vote
2answers
51 views
Which query would be more optimal?
Currently we have have following combined different data i.e one with comma separated and one without comma separated in same table.
#1
id | values
------------
1 | 1,2,3
2 | 4,5,6
3 | ...
0
votes
1answer
53 views
+50
Incorporate additional requirements into a legacy database design
I am struggling with a database design, this is what I have so far.
Here are the problems.
I need to introduce a new user type (conglomeration manager), and they will have visibility of groups of ...
0
votes
0answers
8 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 ...
1
vote
3answers
53 views
SQL Version of CONCAT and LIMIT
Trying to follow Calendar Recurring/Repeating Events - Best Storage Method to design the database using SQL Server 2005.
When i run this below query
SELECT EV.*
FROM events EV
RIGHT JOIN events_meta ...
0
votes
0answers
13 views
Long read operation from my sql table after big data retrieval
I have the following setup in my mysql db:
Big wide table which has around 88M rows and has the size 83G with several indexes of size 22 one of which is a big covering index on several columns.
The ...
0
votes
1answer
45 views
how to configure/design a table with 30,000 insertion in every 15 minutes?
I am trying to design a database table which will be going to face 30,000 insertion in every 15 minutes. So this tables going to face millions of insertion.
A sample table is as follow-
CREATE ...
0
votes
1answer
20 views
Model a ISA relation in MySQL with Foreign Keys
How to model an ISA class?
A 'Persoon' ISA 'Student' or a 'Docent', how do you model this in MySQL?
I tried to make a Persoon class, with 2 field and then you can put the ID of the student / docent in ...
1
vote
1answer
55 views
How to design a database to achieve navigation effect in a website with php?
So I'm trying to build a website in which I will have different Youtube videos organized in playlists.I want a page to display the playlists I have grouped by categories, and a watch page to watch the ...
1
vote
2answers
26 views
SQL: product with multiple categories
Wich is the best database design and performance to store multiple categories (at least 3) per product? Products table could have milions of items.
To have two tables products and ...
0
votes
0answers
17 views
Is it better to delete foreign keys at the DB or application level? [closed]
Example: table Tasks has a column userId which references a userId from Users table. Which approach is better:
To fire a trigger in DB that deletes all tasks of the deleted user?
To not fire any ...
0
votes
0answers
18 views
Banking type website with PHP+SQL [DATABASE SCHEMA] [closed]
I am currently making a banking system. The way in which the banking type system for kicks. The way in which the banking system functions is as the following.
Basics
User owns many different types ...
0
votes
1answer
21 views
Additional query for each item?
I have a really long list of items. I.e:
$query = mysql_query("SELECT i.id
ORDER by i.id DESC LIMIT 0, 200");
while($row = mysql_fetch_array($query)){ $num_row++;
}
Outputs:
[ITEM 1] ...
1
vote
0answers
28 views
What's the best way to build a sparse MySQL table?
I'm building a big table, for statistic purposes, where I have a lot of different attributes with (in some cases) a lot of different values. I was wondering what is the best way of building such ...
2
votes
6answers
90 views
Should create surrogate key instead of composite key?
Structure:
Actor <=== ActorMovie ===> Movie
ActorMovie: ActorID (fk), MovieId (fk)... ===> pk: (ActorID, MovieID)
Should do I create a surrogate key for ActorMovie table like this?
...
0
votes
1answer
14 views
Combine or separate tables based on type
I'm confused on whether I should combine two tables or leave them separated.
Many tables:
Item: ............ ItemID (pk) ............. ItemName .......ItemDescription
ItemLike: ..... ...
0
votes
1answer
30 views
Database Design for Time Table Generation
I am doing a project using J2EE(servlet) for Time Table Generation of College.
There are Six Slots(6 Hours) in a Day
4 x 1 HR Lectures
1 x 2 HR Lab
There Are three batches ( 3IT, 5IT, 7IT)
...
0
votes
2answers
67 views
How Improve table database design
Im planning my database scheme design for my project website.
This is video table:
In "car" tag page, I retrieve from table in this way (PHP query):
sql = "SELECT * FROM `video` WHERE ...
0
votes
1answer
34 views
Doctrine Entity Design for a simple key-value-group table
I have a data table like this:
id|name|group
That hold simple hierarchical data, for list reference. For exemple I can have on this table a list of countries or cities or a list of product types.
...
-1
votes
0answers
30 views
Precautions when designing mysql database schema if tables are going to have billons of rows [closed]
I am about to start work on telephone dictionary kind of project. Its confirm that there are going to be billions of records in dictionary table, and each entry from this dictionary table may have ...
4
votes
1answer
87 views
How do I reduce repetitive code when inserting the same metadata into identical columns over multiple tables?
My goals:
Follow database-normalization.
Ability to track changes in my tables.
Ability to restore the state of my database from a given
point in time, e.g. last month.
Separate the code that is ...
1
vote
3answers
20 views
Create the query for multiple product property
Yesterday I had an issue:
High availability Search implementation in PHP+MySQL
after implementing melc's solution, I needed to change my query and i have this issue:
so I have:
product:
id int(10) ...
0
votes
1answer
53 views
Database design best practice – Survey Q&A
I currently have a database set-up to collect answers for questions (Rails 4.0 + MySQL). To account for what we call a "Grid Question" such as "How often do you shop at each of the following stores?" ...
0
votes
1answer
12 views
How do I set up a database schema for a shopping cart with promotions/discounts?
I'd like to set up some sort of schema that will allow me to implement sales/promotions on products, but can be a bit dynamic.
By that I mean: having sales that are not all of a specific type.
For ...
0
votes
1answer
52 views
Mysql (SQL RDB) vs MongoDB (NoSQL)for storing/querying url parameters from GET requests
Given billions of the following variable length URLs, where the number of parameters depends on the parameter "type":
test.com/req?type=x&a=1&b=test
test.com/req?type=x&a=2&b=test2
...
0
votes
1answer
24 views
mysql order database use 2 tables for current and history?
I have a question about performance. I have a table that contains orderheaders, whenever an order is shipped and invoiced the row is moved from orderheaders to orderheaders_history. This was designed ...
0
votes
1answer
39 views
Mysql vs MongoDB & DynamoDB - User tracking project
I'm working in a project of tracking user positions (latitude & longitude) all day long. Having more than a thousand users, means that every user is saving 1440 tracks per day (1 per minute) so ...
mongodb
0
votes
3answers
39 views
PHP: Adding dynamic number of values into a MySQL database
I've got a page with text fields where the user can input data (in our situation they are questions).
It would look look something like this:
___________________________________
| Enter your ...
0
votes
1answer
28 views
how to break mapping table in order to faster query result and database size
I have employee number table and one group mapping table.
desc employee_tab;
----------------
empno int;
emp_name varchar(50);
desc group_mapping_tab;
------------------------
empno int;
group_no ...
-1
votes
2answers
24 views
mysql database tables architecture
I have database having hundreds of tables with relations. I want to look a db design for understanding tables relations in a single place. Any idea how I can do this?
Regards
0
votes
1answer
25 views
Is storing stack traces in database recommended?
The current system I am working on goes through a set of documents & performs some logic using the metadata of the document. If the metadata of a document is fishy..it throws an exception. The ...
1
vote
1answer
56 views
Getting 2 related groups with 1 query?
I've got the following scenario:
1 item table
1 member table
1 page table
Each "item" belongs to a "member" and a "page"
$query = mysql_query("SELECT i.id, i.title, mem.realname, p.pagename
...
0
votes
0answers
25 views
Design a system for content delivery through api keys [closed]
I need help in the designing of a system to deliver content through web service.
I have a content table with fields as
id
title
body
also a api key table with fields as
id
apiKey
The content and ...
0
votes
2answers
42 views
Database Structure For coding [closed]
I am trying to code a school management web application for my project PHP/MySQL. I need some help with database structure.
Here are my needs, lets say there are 10 class, for each class 4 sections, ...
0
votes
2answers
48 views
SQL join - one column serving as ID for two columns in another table
Okay, maybe I've absolutely goofed on the thought process behind this and I need put in my place, or maybe I'm not far off.
I have one table called TEAMS with two columns: teamID and teamName. I then ...
0
votes
2answers
44 views
Is Database primary key 0 as fallback allowed or recommended?
I want to use a Primary Key = 0 as fallback for my Application.
Where id=0 means to use System Default, and all above are user defined Entrys. There is no user-access to id 0 and no possibilities for ...
1
vote
2answers
72 views
Data Model, alternative to EAV
I have a Product Database, and I want to attach text, images, videos to the products. I also want that each entity (text, images or videos) have a tag, for further organisation on application.
I ...
0
votes
2answers
30 views
Using LIKE operator without limiting results
I have the following query, the objective of which is to display a list of "items" which contain a series of "tags". Furthermore, I have chosen to display only "5" tags for each "item" as you can ...
0
votes
1answer
33 views
Performance of modelling inheritance in database using superclass table
My Question, is actually a question about the usability / performance of a concept / idea I had:
The Setup:
Troughout my Database, two (actually three) fields always re-appear constantly: title and ...
