2
votes
1answer
48 views

Relational modeled design to store versioned files

The scenario I have images in a database table and some of these need to be altered then re-saved(not overwriting the original) whilst providing a reference back to the original. I also save the ...
0
votes
3answers
76 views
+100

MySQL schema for recurring events - Query by specific date

I've just asked this exact same question on stackoverflow so apologies for cross posting but I only just noticed dba so thought I'd ask across here as well. I'm just about to start putting together a ...
0
votes
0answers
26 views

Need help with data model

Schema does not list all fields, but only the relevant ones User adds credits/funds to his account using paypal or Internet Banking (which has to be verified manually, and approved, so the 'status' ...
4
votes
1answer
104 views

Searching 500 million records of binary data

I will inserting into a database the signatures of 500,000,000 pictures. The signatures will be generated using libpuzzle. Each signature is 338 bytes. (so 160 GB) plus a table for searching (read ...
0
votes
1answer
42 views

How can I Automate MongoDB schema design from existing MySQL Database and Logs?

I am working on the project which will automatically generate sample MongoDB schema analyzing existing MySQL database and logs. I am looking for help regarding the below questions. Please, share your ...
0
votes
0answers
28 views

Multi language where this feature never existed

I need to implement multi language system in an existing system that was not developed for this. The number of entries in the tables that will hold data that has to be translated will not exceed 5 ...
1
vote
1answer
41 views

Is there any way MySQL can list relationship cardinality between two entities?

I am working on the project where I have to analyse the MySQL schema. As a part of schema information, I want to list down relationship cardinality between different relations. Does MySQL stores ...
2
votes
2answers
76 views

Dynamically change schema to accommodate custom fields!

I am working on building a sort of a cms for a particular project. Nothing that will have a large volume of records in any table. Within 6 digits max. I was thinking to create the default schema for ...
1
vote
1answer
53 views

MySQL 5.5 to 5.6 Upgrade. Will data remain the same?

We are considering to upgrade to MySQL 5.6 this summer. I would like to know if our data will be changed in any way once we perform the upgrade? I've been reading and I found that mysql_upgrade ...
0
votes
1answer
44 views

MySQL DB structure with Main category and several nested sub categories

I am looking to create a set of fields on my webpage identical to what eBay uses on their category selection page when you are selling something. For my purposes I am doing motorcycles, power sports, ...
0
votes
0answers
43 views

Mysql profiler different duration information_schema.profiling

Hi! I have a problem and no idea how to resolve it. I'm trying to use mysql profiler. I'm using the command: set profiling=1; to turn it on. Later, i execute my query which is correlated ...
0
votes
1answer
57 views

Help With MySQL Computed Columns/Auto Updating Field Based On Average

I am currently building a social network type application on-top of MySQL and have run into a problem I can't wrap my head around. In my web application each user has a field called "weight" and this ...
0
votes
0answers
21 views

How should I restructure a table that contains multiple columns that contain similar, but not same data?

I currently inherited an InnoDB table that describes a product. In the table there are a few columns that describe the maximum height at which the product may be mounted. These are the columns: ...
1
vote
1answer
47 views

Designing database with Category and Tags for Posts - Seeking for guidance/advice

I'm not really a database novice but I'm not an expert either. Problem: I'm having this difficulty on designing my Post, Category and Tags relationship. I've read Normalization many times but could ...
8
votes
4answers
300 views

eCommerce orders table. Save prices, or use an audit/history table?

Im designing my first eCommerce schema. I've been reading around the subject for a little while, and am a bit confused about the relationship between an order_line_item and a product A product can ...
1
vote
0answers
43 views

MySQL Space efficient way of storing indexed key-value pairs associated with table

We're storing transactions in an accounting system using two tables, one contains the general description of the particular transaction (the other contains the entries, one for each account - which ...
1
vote
1answer
96 views

Scheme review/Normalization level/query optimization

So I have a relatively complex database I'm designing - basically full in-house accounting setup. In one section I have some concern that I'm over-normalizing; the 'directory' - I'll example the ...
0
votes
1answer
74 views

Relation between multiple hierarchical taxonomies linked to a single entity

I am working on an online test application which supports multiple choice questions. I have created schema for taxonomy (classification) system to be used with questions. taxonomy(id, name, slug, ...
0
votes
1answer
149 views

Shopping cart schema with multiple product tables

I have a shopping cart table used to store customer id and product id of their chosen items. However I have multiple product tables, for example cars, books, houses. I started out with one table but ...
1
vote
0answers
38 views

Schema for yearly data models

I am developing a school management system. Most data will not be reused after the end of the school year. I am contemplating using schemas to separate data in various school years, however I am ...
0
votes
0answers
48 views

Save output of MYSQL Schema Inspector

MySQL Workbench 6 has a Schema Inspector that shows all the tables in a DB and useful statistics such as number of rows and average row lengths. How can I get that in a form that I can email someone ...
0
votes
0answers
14 views

Services in a Catalog with Year/Week/Day period

I want to create a database structure for the Services in a Catalog with Offres for one or more services. Each Offre is available for one Year period ( example : 01 Janvier – 31 Mai ) , for a Week ...
0
votes
1answer
77 views

How to reduce table duplication, while ensuring integrity of the database?

For example, in my app I have some class called Contact: case class Contact(name: String, email: String, phone: String) And several other classes have one-to-many relation to that contact (for ...
1
vote
2answers
153 views

How can I integrate a new system to a design scheme I previously created?

This is a follow-up to a question I wrote a while ago, for reference, you can look it up here. I've developed a curriculum based database scheme that looks like this(Thanks Joel Brown) Now that a ...
2
votes
1answer
69 views

Constrains relationship

I have the following design. Each user has it's own contacts, it's own hosts and assign those to a domain which belongs only to him. I want to insure that the host you assign to the domain belongs ...
0
votes
2answers
189 views

Which schema is better for a shopping project? [closed]

I'm working on a business-to-customer project that has variety of product types. There are a few properties like name, description, brand_id that each product has but there are also many specialized ...
0
votes
1answer
81 views

Whether or not to create separate tables

I am working on a project, where I need to store the information related to A and B, the A and B both have identical columns.There might be thousands of As and Bs. Should I use "type" column to ...
0
votes
0answers
108 views

please help me connect workbench on 5 system having one server

We have computing software which has one server for 5 computers. I can work on server in MySQL workbench but when i try to work on second computer i get error failed to create ipc port : access is ...
2
votes
1answer
84 views

MySQL Performance Gains By Splitting Tables?

I am designing a database schema to be used with MySQL. The database needs to keep track of a users basket items and purchased items. I've thought about two ways I could do this but not sure which ...
1
vote
1answer
314 views

Creating schema for a shop that sells items from multiple tables

I am trying to create a table in mySQl that contains a list of all the items that a shops. For example: shops (shop_ID (PK), shop_name) shops_and_items (shop_ID (PK), item_ID (PK), price) The issue ...
2
votes
1answer
71 views

Column type unknown at creation time

I have a table in a MySQL database that stores survey results. CREATE TABLE survey_responses ( response_id INTEGER UNSIGNED AUTO_INCREMENT, survey_id INTEGER UNSIGNED NOT NULL, question_id ...
2
votes
1answer
3k views

How can extract the table schema from just the .frm file?

I have extracted the data directory of mysql from a backup and need to get the schema from an old table, however it's a backup from a different machine. I have read a fair number of tutorials today ...
1
vote
1answer
115 views

Mysql: Schema/Query Performance Approach for aggregated mailbox folders

I am about to code a messaging system where users can write messages to other users. User can create custom inbox folders for sorting the messages they receive, however, every user has 2 main inboxes: ...
0
votes
0answers
201 views

XML Data convert to SQL database,

I have an XML feed that i need to parse daily, i want to store all of the data from the XML feed in a SQL table so then that data can be queried and manipulated by my clients. I can currently parse ...
0
votes
1answer
198 views

Database schema design help needed

I am developing a PHP application expecting millions of records both parent and children. My goal is to design an optimized database design to achieve high speed and performance. This application ...
1
vote
1answer
449 views

Compare two MySQL dump files

I am trying to compare two MySQL dump files. One was made from a fresh install of a web application and the other is a dump from the same web application that has been upgraded from a previous version ...
1
vote
1answer
42 views

Multiligual database design

I assume that the best approach for many languages (5 and more) is concept based on: CREATE TABLE `language` ( `language_id` char(2) NOT NULL, `collation` varchar(64) NOT NULL, ...
2
votes
2answers
196 views

How to design future proof tables?

I am new to database design and have started developing my first schema. Some of my tables (e.g. user_profile) I am struggling to come up with an exhaustive list of fields for the table. A particular ...
2
votes
2answers
4k views

how to add trigger in mysql workbench, without UDF, using EER design

I have designed a simple database using EER in workbench. However, i want to have a minimum value for the attribute salary (compared with min_value attribute from artist table). Here is the schema: ...
2
votes
1answer
91 views

how to deal with foreign keys with fixed values (on a db-table-basis)

I have a control table that looks like this: CREATE TABLE `tbl_control` ( `tm_db_tbl` varchar(64) NOT NULL, `tm_key` int(11) unsigned NOT NULL, /* a bunch of other fields here */ PRIMARY KEY ...
2
votes
1answer
1k views

MySQL Explain: Using index, using temporary, using filesort. Can this query be improved?

I'm working on a event tracking system which uses a handful of lookup tables as well as the primary logging table. In a report I'm writing, an object can be selected to view statistics against. The ...
3
votes
1answer
121 views

Referential Integrity in schema, cascading the renaming

In phpMyAdmin, while renaming a column/table which is referenced by some foreign key, I get an error. I can not rename a column until I remove all its relations. There should be facilitated integrity ...
4
votes
2answers
700 views

Are there any tools to go from ERD to DB design and vice versa?

I'm learning DB design and looking for some tools, preferably open source software, that could help go from an ERD to a DATABASE and vice versa. For the record, the database i'm using is MySQL.
2
votes
1answer
112 views

Ranking System with Different Categories

I want to create a system where the user can be ranked by several different factors. For example, the user competes in the following categories Wins Points I need to know what the position of the ...
2
votes
1answer
193 views

MySQL: Show Unique key definition by name

I get the following error: ERROR 1062 (23000) at line 1901: Duplicate entry '3-3820-50-0' for key 'UNQ_CAT_CTGR_ENTT_DTIME_ENTT_TYPE_ID_ENTT_ID_ATTR_ID_STORE_ID' How can I find out which table ...
1
vote
3answers
136 views

Database schema - performance v correctness information

I have a database that stores information about the users of my website (username, password, etc). I also store information about their activity on the site. For example, I have a large number of ...
2
votes
1answer
661 views

shopping cart database schema

I need help creating the schema for a database to hold information relative to a web user's shopping cart/session. Each session will have a session ID, a time stamp holding time of creation, an expiry ...
1
vote
3answers
270 views

Mysql error while creating FK

here is structure of both tables category table CREATE TABLE category ( `catID` bigint(20) NOT NULL AUTO_INCREMENT, `title` varchar(60) NOT NULL, `created_at` timestamp NOT NULL DEFAULT ...
0
votes
1answer
895 views

Db design for a quiz with variable number of answer choices, and each worth different score in a different category?

I'm working on a question/answer project and having a hard time coming up with a suitable database structure. Each question can have from 1 - "n" answers, and each answer is associated with one of ...
0
votes
1answer
185 views

How to simplify schema for multi database / hundreds of millions of rows data set

We currently have a MySQL data set that was started about 10 years ago that aggregates a log of data, and accumulates millions of rows each month. The people that developed our system started ...