MySQL Forums - Database Design and Data Modelling

Error 1005 - ToyExample (1 reply)

Stefan König — Thu, 23 May 2013 22:03:24 +0000

Hey,

while using a toy example i'm getting an error 1005 without any idea how to fix it, or concrete where the problem is located..

maybe someone can give me a hint?

http://codepad.org/i2gkHbZF

thanks for your help :)

Using a primary key as account number (1 reply)

Mark Stopkey — Wed, 08 May 2013 17:46:53 +0000

Hello all, we are building a large application on MYSQl and I have a db design question. This app will house accounts. Is it a no no to use the account table's auto incrementing primary key as the account number?

slow mysql database (1 reply)

Torsten Mueller — Tue, 23 Apr 2013 16:15:11 +0000

Hi,
i have a data base wich will have 500000 lines.
At the moment it have 190000 lines and it's so very slow...
Here is the structure:
CREATE TABLE `produkte` (
`nummer` int(11) NOT NULL AUTO_INCREMENT,
`anbieter` text NOT NULL,
`ueberschrift` text NOT NULL,
`beschreibung` text NOT NULL,
`bildlink` text NOT NULL,
`shoplink` text NOT NULL,
`preis` float NOT NULL,
`marke` text NOT NULL,
`kat1` text NOT NULL,
`kat2` text NOT NULL,
`kat3` text NOT NULL,
`kat4` text NOT NULL,
PRIMARY KEY (`nummer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=193560 ;

I have to search the kat elements. So i made the following PHP order:

$sql="SELECT distinct kat4 FROM produkte WHERE kat1='".$_SESSION['kat1']."' AND kat2='".$_SESSION['kat2']."' AND kat3='".$_SESSION['kat3']."'";
$res=mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($res)!=0){
$sql="SELECT distinct kat4 FROM produkte WHERE kat1='".$_SESSION['kat1']."' AND kat2='".$_SESSION['kat2']."' AND kat3='".$_SESSION['kat3']."'";
$res=mysql_query($sql) or die(mysql_error());
...
And here i fill the option element in a while loop.

This i make for 4 select elements.
So the script needs more than 12 seconds. It's to slow.
How can i make it's faster?

Thanx for help.

Best regards.
Torsten

How do I model the tables for storing data from a multi-select combo box? (1 reply)

Srini Devara — Tue, 23 Apr 2013 05:57:38 +0000

I have a basic data model question.

What is the most common/recommended way to store the values from a multi-select combo box? Is it best to normalize and create a seperate child table for storing multiple values or is it better to create a column in the same table and store value like a some comma delimited list or using XML elements?

What are the pros and cons of either approaches from the perspective of "searching" for relavant data.

I have a related question as well. Say, we have a large # of features that we want to store for a given product/service. Is it better to store the data in the form of an XML in a table column and if so how easy is it to use xml related functions with mysql to be able to retrieve data quickly.

Any help is greatly appreciated.

Thanks,
Srini

Table structure for multiple names (2 replies)

J. Holmberg — Wed, 10 Apr 2013 03:58:57 +0000

I'm creating a database that will contain information about artists. like this:

artists
• id
• year_born
• month_born
• day_born
• city_born
• country_born
• year_died
• month_died
• day_died
• city_died
• country_died
• contemporary
• unknown
• views
• publish
• time_created
• time_modified

Who can have multiple first names, surnames and aliases. The question is whether I should create separate tables for first name, last name and alias. like this:

artist_surname
• id
• artist_id
• name
• sorting
• publish
• time_created
• time_modified

artist_forename
• id
• artist_id
• name
• sorting
• publish
• time_created
• time_modified

artist_alias
• id
• artist_id
• name
• sorting
• publish
• time_created
• time_modified

Or if I should have it all in one table and there determine what type it is. like this:

artist_name
• id
• artist_id
• name
• name_type (surname, forename, alias)
• sorting
• publish
• time_created
• time_modified

Would really appreciate if the experts could give their opinion!

Process Colission Issues on production tables (1 reply)

Mike Jonsson — Fri, 05 Apr 2013 04:06:11 +0000

With the risk of sounding like a complete newbie I thought best to first check this with the forum to find out by others experience if my consideration is simply unfounded or not.

While running a set of fairly large insert queries I keep tabs of the process in the MySQL Administrator tool >> Server Connections >> Threads window...

I just noted that when I use three different process id's simultanously I now happened upon a collision in the process que.

Example:

PID 8 --> INSERT INTO sometable001
PID 9 --> INSERT INTO sometable002
PID 10 --> locked INSERT INTO sometable001

Apparently the PID 8 got there before PID 10 did.

Now, hypothetically I would run in to this at some point on a production server as well. Greatest risk being while running a lot through PHP with queries which may run up loads of results if range restrictions cannot be imposed properly.

How would one best go about mitigating this risk occuring on heavy loaded web sites?

Or is this a non issue by the server (usually a standard well configured LAMP machine) being able to redirect such issues.

After all, it is quite common a smaller site with less users only having a limited set of user names to access the DB through.

Database with people, addresses, companies and telephone numbers (3 replies)

Andrea Croci — Wed, 03 Apr 2013 23:51:11 +0000

Hello Everybody,

I'm pretty sure this question has been asked several times and it should actually be a textbook question. However I haven't found it anywhere and so, sorry for posting again.

The problem I'm having is to design a database to store data for people, companies, addresses and telephone numbers.

Almost all of the relationships would be M:N (a person can work at several companies and a company can have several people working for it, a person can have several addresses and so can a company, whereas at one address can live several people and be located several companies, etc.). The only exception I can come up with is that a telephone number normally is located fisically at only one address, but one address may have more than one telephone numbers (or none, obviously).

Now, I can come up with two possible solutions:

1) Make a connection table for each M:N relationship (like Person and Address, where person_id and address_id are the foreign keys in this table and the combination is unique). In this case for the person/address/telephone number relationships I guess I would need all three tables: person_address, person_telephone and address_telephone, otherwise I loose information on which telephone number is at which address of, say, a certain person. Same for the other relationships, or am I missing something?

2) Make one big table with person_id, address_id, company_id and telephone_id (and maybe some additional information which is not that relevant for my question). This table would not be normalized, it would have some columns set to NULL for some rows and some redundant data. I'm also assuming in this case I would have to create an index on all foreign keys. Probably it would also be better to give it an extra primary key like "person_company_address_telephone_id".

The questions are:

- Is there any other way that I'm not seeing?

- Which solution is better from a maintenance, data consistency and performance point of view?

Thank you very much if anyone can shed some light on this (or point to an existing solution).

Andrea.

How to update the table on auto-disconnect after a period of inactivity from user? (1 reply)

Srini Devara — Thu, 14 Mar 2013 15:40:02 +0000

I have a "user" table which has a column "account_logout_time". If the user clicks on "Logout" link, I know the logic of updating the user table for that user with the logout time. But, say, the user steps out and does not return in time before the user session expires in say 15 minutes. Now, how do I write the logic to update the database table with the logout time for that user.

Thanks
Srini

How to limit # of users per account (4 replies)

Srini Devara — Fri, 15 Mar 2013 15:14:20 +0000

I am creating a datamodel for a website that I am building. I need to limit the # of users who can simultaneously login to an account to either 1 or 2 depending on the package the customer chooses.

How do I datamodel the tables to achieve this functionality?

Thanks,
Srini

Car Parts Catalogue Database design (3 replies)

Des Govender — Fri, 15 Mar 2013 13:38:51 +0000

Can anyone point me to a good source for database desgin related to a car parts catolgue. I'm having difficulty with a proper layout.

Multiple Tables Split By Date (6 replies)

Billy Simons — Tue, 26 Feb 2013 08:26:13 +0000

Hi Guys,

I have a question on table structures...

I have a massive table (60M+ rows) that i want to split up. This table contains transaction records that are queried quite often. The table is MyISAM replicated between a couple of different MySQL servers.

My though is to split the table in to multiple tables based on date, with their table name YYYYMM. This would be great and keep the tables manageable.

My question is how to query multiple tables to look for some of the data. Lets say, the time span for the records is 5 years, I would have 60 tables as they are broken down by month. One of the columns in all the tables has an account_id, and I want to know how many total records there are so I can show pagination.

I know I can union all the tables together etc, but my concern is that by unioning everything together to create one big table, whether this will hit the performance problems I am having at present as it will still be one big table.

Does anyone have any ideas on a good way to do this? I had though summary tables etc etc, but wanted to see if anyone else has done something similar.

Thanks in advance!

dynamic querying (1 reply)

Stefan Maric — Wed, 20 Feb 2013 23:17:06 +0000

I have a Table containing product info

we then use a product_ID as a prefix on other tables in which we collect stocking levels/buying activities etc

I now need a way of iterating over these abc_ProdType1 tables so that I can extract data for marketing reports etc

I am thinking along the lines of a function/procedure which should iterate across the list of product_ID (I can get this from a simple select query)

2nd query needs to construct the select query abc_ProdType1 tables by building the table name dynamically incorporating the product_ID

The results of this query then get Insert into my mktg_Table

Does this sound like a reasonable way of tackling my problem
If so can you give some hints as to how I construct this

any help greatly appreciated

How to store footnotes or comments per field (3 replies)

Brent Bigler — Sun, 24 Feb 2013 01:11:12 +0000

The tables I've designed are usually straightforward, something like this:

CREATE TABLE `account_trans` (
`at_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`at_recorded` date DEFAULT NULL,
`at_description` varchar(65) DEFAULT NULL,
`at_amount` decimal(22,4) DEFAULT NULL,
`at_price` decimal(22,4) DEFAULT NULL,
PRIMARY KEY (`at_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

For a new project, I need a provision to store footnotes or comments on all of these fields; for instance, comments as to how the price was calculated. (The comments will be stored in a separate table.) I was wondering how others might have solved a similar pattern. I had two ideas, neither of which is all that attractive to me:

1) Add a footnote ID field for each field in the primary table. The problem with this solution is that most of the time, most of the footnote ID fields will be blank or zero, as footnotes will probably be added to only one or two items per record.

2) Change the primary table to something like a "name-value" table, that is, each field in the example above would become a separate record in the table tied together by a single transaction ID, something like this:

CREATE TABLE `account_trans` (
`at_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`at_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `trans_items` (
`ti_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ti_at_id` int DEFAULT '0',
`ti_comment_id` int DEFAULT '0',
`ti_trans_type` varchar(10) DEFAULT null,/*This is the 'name' field*/
`ti_trans_value` decimal(22,4) DEFAULT null,/*This is the 'value' field*/
PRIMARY KEY (`ti_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This solution has a couple of problems, it's less human-readable, requires more effort when summing transactions, and doesn't account for adding footnotes to date and other non-numeric fields. It might have other efficiency problems, too.

If this problem makes sense, I'd appreciate any feedback and better solutions.

Thanks.

DB Model for custom-fields management (2 replies)

Chris Bassett — Tue, 25 Dec 2012 02:07:31 +0000

I'm looking to write a webapp that has a series of configurations. For each configuration, there can be any number of questions that needs to be asked. What I'm trying to do is create a generic "custom fields" table in a database that will allow the admin of the webapp to add as many custom question fields as needed. Some configurations will not need any custom question fields, whereas other may have 10 or more custom question fields associated with it.

In the end, I also need to be able to go through and determine which custom questions meet or fail a certain set of requirements.

Any ideas on how I could implement something like this?

Is storing a comma separated list in a database column really that bad? (4 replies)

Vincentas Grinius — Thu, 14 Feb 2013 03:44:34 +0000

Hi guys,

Imagine a web form with a set of checkboxes (any or all can be selected). I chose to save them in a comma separated list of values stored in one column of the database table.

Now, I know that the correct solution would be to create a second table and properly normalize the database. It was quicker to implement the easy solution, and I wanted to have a proof-of-concept of that application quickly and without having to spend too much time on it.

I thought the saved time and simpler code was worth it in my situation, it this a defensible design choice, or should I have normalized it from the start?

Some more context, this is a small internal application that essentially replaces an Excel file that was stored on a shared folder. I'm also asking because I'm thinking about cleaning up the programm and make it more maintainable. There are some things in there I'm not entirely happy with, one of them is the topic of this question.

Revisited: Working with a billion rows (3 replies)

Mike Mullins — Fri, 21 Dec 2012 23:22:26 +0000

I had posted a question back in August, but somehow I didn't see the reply and I got sidetracked from the project. I'd like to solicit some input once again. The original thread is http://forums.mysql.com/read.php?125,564514

Right now I'm just trying to clean up the data in the tables and I think if I can successfully do that, I'll have a good idea on the performance side when I start using it. Production queries will be very few - maybe 10 per day - but they'll need to be quick.

As I try to clean up the tables, my first task is to remove duplicates. I'll focus on two tables.

I'm running this in dev on Windows 7 64-bit 8GB RAM, MySQL 5.5.12. The two tables have just one column containing a list of words with a maximum length of 20 characters. Table2 has a bunch of entries that should already be in table1. I need to delete the duplicates from table2.

So far I've tried the following queries but they all go nowhere - just chugs away and never does anything.

delete * from table2 where record in (select * from table1 where record like "1%");

OR

delete from table2 using table1 inner join table2 where table1.record=table2.record and table1.record like '1%';

What can I do with my data to make it work? Any tuning I can do to the server? Indexing?

Help with partitioning an existing table (5 replies)

Ravi Malghan — Sun, 06 Jan 2013 00:25:34 +0000

I am a newbie to mysql db. I have a table that I have partitioned by a datetime field

create table status (
        SERVERSERIAL    NUMERIC(16) NOT NULL,
        SERVERNAME      VARCHAR(64) NOT NULL,
        FIRSTOCCURRENCE DATETIME NULL,
        CUSTOMER VARCHAR(64) NULL,
        <..many other fields>
        PRIMARY KEY ( SERVERSERIAL, SERVERNAME,FIRSTOCCURRENCE )
)
PARTITION BY RANGE COLUMNS (FIRSTOCCURRENCE)
(
        PARTITION p2012_3 values less than ('2012-09-30'),
        PARTITION p2012_4 values less than ('2012-12-31'),
        PARTITION p2013_1 values less than ('2013-03-31'),
        PARTITION p2013_2 values less than ('2013-06-30'),
        PARTITION p2013_3 values less than ('2013-09-30'),
        PARTITION p2013_4 values less than ('2013-12-31'),
        PARTITION p2014_1 values less than ('2014-03-31'),
        PARTITION p2014_2 values less than ('2014-06-30'),
        PARTITION p2014_3 values less than ('2014-09-30')
)

The table currently has 3Mil rows. I want to partition it further by customer since most of my queries also have customer name in the where clause. Have a few questions

a. I think I need to add the Customer field to the primary key. If so, do I need to suspend all writes to the database when I run the alter command?
b. can someone help with the alter statement for adding customer to partitioning in addition to FIRSTOCCURRENCE or some pointers?

Thanks
Ravi

Best Practice For Database table design (2 replies)

Tim McCune — Sat, 15 Dec 2012 11:32:28 +0000

Although I have been designing databases for quite some time. I would not consider myself an expert. I have always wondered which is a better practice, for the idea below:

Lets say that we have a database, with several tables. Some of these tables have a foreign key to a table which stores a status_code. The catch is, that while some tables may use the same status codes, other tables may use additional status codes. IE: Pending, Approved, (Suspended, hidden, delete)

Practice #1:
So the question is, would it be better to create multiple tables for status_codes, like such:

/* Members Status Codes */
CREATE TABLE `Members_Status_Codes` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(30) NOT NULL,
`Description` VARCHAR(255) NOT NULL,
PRIMARY KEY(`ID`)
);

INSERT INTO `Members_Status_Codes` ( `ID`, `Name`, `Description` ) VALUES
( 1, 'Pending', 'This member is pending email verification.' ),
( 2, 'Active', 'This member has completed email verification.');

/* Blog Status Codes */
CREATE TABLE `Blog_Status_Codes` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(30) NOT NULL,
`Description` VARCHAR(255) NOT NULL,
PRIMARY KEY(`ID`)
);

INSERT INTO `Blog_Status_Codes` ( `ID`, `Name`, `Description` ) VALUES
(1, 'Pending Admin Approval', 'This blog is pending admin approval' ),
(2, 'Approved', 'This blog has been approved for display' ),
(3, 'Hidden By Member', 'This blog has been hidden by the member' );

/* Advertisement status codes */
CREATE TABLE `Ad_Status_Codes` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(30) NOT NULL,
`Description` VARCHAR(255) NOT NULL,
PRIMARY KEY(`ID`)
);

INSERT INTO `Ad_Status_Codes` ( `ID`, `Name`, `Description` ) VALUES
(1, 'Inactive', 'This ad is currently inactive.' ),
(2, 'Active', 'This ad is currently active.' ),
(3, 'Expired', 'This ad has expired.' );

-------------------- OR --------------------
Practice #2:
Would it be a better practice to create one table to store all status codes and add an additional column which determines which status code belongs to which table. Like this:

/* Status code types table */
CREATE TABLE `Status_Code_Types` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(30) NOT NULL,
`Description` VARCHAR(255) NOT NULL,
PRIMARY KEY(`ID`)
);

INSERT INTO `Status_Code_Types` ( `ID`, `Name`, `Description` ) VALUES
( 1, 'Members', 'These status codes are used by the members table.' ),
( 2, 'Blogs', 'These status codes are used by the blogs table.' ),
( 3, 'Ads', 'These status codes are used by the ads table.' );

/* Status codes table */
CREATE TABLE `Status_Codes` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(30) NOT NULL,
`Description` VARCHAR(255) NOT NULL,
`Type_ID` INT UNSIGNED NOT NULL,
PRIMARY KEY(`ID`),
FOREIGN KEY(`Type_ID`) REFERENCES `Status_Code_Types`(`ID`)
);

INSERT INTO `Status_Codes` ( `ID`, `Name`, `Description`, `Type_ID` ) VALUES
( 1, 'Pending', 'This member is pending email verification.', 1 ),
( 2, 'Active', 'This member has completed email verification.', 1 ),
( 3, 'Pending Admin Approval', 'This blog is pending admin approval', 2 ),
( 4, 'Approved', 'This blog has been approved for display', 2 ),
( 5, 'Hidden By Member', 'This blog has been hidden by the member', 2 ),
( 6, 'Inactive', 'This ad is currently inactive.', 3 ),
( 7, 'Active', 'This ad is currently active.', 3 ),
( 8, 'Expired', 'This ad has expired.', 3 );

And then in the business logic, add code which queries only status codes for members, for blogs, for ads, etc?

Now in the above examples, im sure it may not make a bit of difference. Considering there are only 3 tables in the above examples. But lets say that there were 10, 20 or 30 tables? The first option would add 10, 20 or 30 tables which contain status codes. The second option would add many more records to one table.

Any suggestions would be greatly appriciated,
Tim McCune

db design for production selection process (no replies)

Chris Bassett — Mon, 08 Oct 2012 15:24:42 +0000

Need some advice here...

Trying to create a production selection system using PHP and MySQL (for the web, of course) where a person can specify what type of equipment they have (in this case, farming planter equipment) and our "selection wizard" can come up with a product that suits their needs based on the criteria they have specified.

I'm thinking this sort of needs to be a pivot table of some kind, where the wizard will "drill down" to one solution using the criteria.

Right now, I was thinking a series of fields (one field for each possible condition) in a table, and then use a query that finds a row in the table that maches the most number of fields in the query.

Any suggestions on how to do something like this? Examples??

How to retrieve this data as a single row with three new columns (1 reply)

V Ra — Sat, 08 Sep 2012 04:34:26 +0000

I have the following tables:

user {user_id, name}
family {child_id, mother_id, father_id, address, household_income}

I want to select a row in family given child_id, mother_id and father_id and have it return all the columns in family along with the 3 new columns for the names of the users associated with child_id, mother_id and father_id

family {child_id, mother_id, father_id, address, household_income, child_name, mother_name, father_name}

Is this possible?

Thanks

Table design issue and complex query questions (5 replies)

David Usherwood — Tue, 21 Aug 2012 15:05:55 +0000

Hi All,

Please could anyone advise about the following table design and resulting select query. I am getting a partial Cartesian because the joins are not quite right. These are the tables involved with the relevant indexes and the query follows:

CREATE TABLE IF NOT EXISTS `NewpersistentCentreProfile` (
`pmcp_id` int(11) NOT NULL AUTO_INCREMENT,
`CNA_ID` int(11) NOT NULL,
`YearMonth` date NOT NULL,
`ProfileActive` tinyint(1) NOT NULL DEFAULT '1',
`NetworkTypeID` tinyint(3) NOT NULL,
`CourseID` tinyint(3) NOT NULL,
`ProfileEffectiveFrom` date DEFAULT NULL,
`ProfileEffectiveTo` date DEFAULT NULL,
`RegsTarget` smallint(5) NOT NULL DEFAULT '0',
`ComplsTarget` smallint(5) NOT NULL DEFAULT '0',
`OGSAccessTarget` smallint(5) NOT NULL DEFAULT '0',
UNIQUE KEY `pmcp_id` (`pmcp_id`),
UNIQUE KEY `CNA_ID` (`CNA_ID`,`YearMonth`,`ProfileActive`,`NetworkTypeID`,`CourseID`,`ProfileEffectiveFrom`),
UNIQUE KEY `CNA_ID_2` (`CNA_ID`,`ProfileEffectiveFrom`,`ProfileEffectiveTo`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `persistentNetworkType` (
`NetworkTypeID` tinyint(2) NOT NULL,
`NetworkTypeDesc` varchar(50) NOT NULL,
`NetworkType` varchar(5) NOT NULL,
PRIMARY KEY (`NetworkTypeID`),
UNIQUE KEY `NetworkCode` (`NetworkType`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `centresDataByDay` (
`CNA_ID` int(11) NOT NULL,
`dateReference` date NOT NULL,
`myguideRegistrationCount` int(11) NOT NULL DEFAULT '0',
`goOnRegistrationCount` int(11) NOT NULL DEFAULT '0',
`myguideRegsYTD` int(11) NOT NULL DEFAULT '0',
`goOnRegsYTD` int(11) NOT NULL DEFAULT '0',
`myguideOLBStartsCount` int(11) NOT NULL DEFAULT '0',
`goOnOLBStartsCount` int(11) NOT NULL DEFAULT '0',
`myguideOLBStartsYTD` int(11) NOT NULL DEFAULT '0',
`goOnOLBStartsYTD` int(11) NOT NULL DEFAULT '0',
`myguideFundableOLBComplsCount` int(11) NOT NULL DEFAULT '0',
`goOnFundableOLBComplsCount` int(11) NOT NULL DEFAULT '0',
`myguideOLBCourseComplsCount` int(11) NOT NULL DEFAULT '0',
`goOnOLBCourseComplsCount` int(11) NOT NULL DEFAULT '0',
`myguideFundableOLBComplsYTD` int(11) NOT NULL DEFAULT '0',
`goOnFundableOLBComplsYTD` int(11) NOT NULL DEFAULT '0',
`myguideOLBCourseComplsYTD` int(11) NOT NULL DEFAULT '0',
`goOnOLBCourseComplsYTD` int(11) NOT NULL DEFAULT '0',
`goOnOLBLADCount` int(11) NOT NULL DEFAULT '0',
UNIQUE KEY `CNA_ID` (`CNA_ID`,`dateReference`),
KEY `dateReference` (`dateReference`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `currentCentreFunding` (
`ccf_id` int(11) NOT NULL AUTO_INCREMENT,
`pcf_id` int(11) NOT NULL DEFAULT '0',
`CNA_ID` int(11) NOT NULL,
`parentCentreRef` int(11) NOT NULL DEFAULT '0',
`NetworkTypeID` tinyint(2) NOT NULL DEFAULT '0',
`FundingStateID` tinyint(1) NOT NULL DEFAULT '0',
`startDatetime` datetime DEFAULT NULL,
`endDatetime` datetime DEFAULT NULL,
`profile` smallint(3) NOT NULL DEFAULT '0',
`dirty` tinyint(1) DEFAULT '0',
UNIQUE KEY `ccf_id` (`ccf_id`),
UNIQUE KEY `CNA_ID_2` (`CNA_ID`),
UNIQUE KEY `CNA_ID` (`CNA_ID`,`startDatetime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `hh_Centre` (
`CentreID` int(11) NOT NULL AUTO_INCREMENT,
`CentreCode` varchar(15) DEFAULT NULL,
`CentreName` varchar(255) NOT NULL,
`PartnerType` enum('Centre Partner','Access Point') DEFAULT 'Centre Partner' COMMENT 'What type of centre is this?',
`OrgTypeID` mediumint(5) DEFAULT NULL,
`LocationID` int(11) NOT NULL,
`Website` varchar(200) DEFAULT NULL,
`OpenToPublic` tinyint(1) NOT NULL,
`WhyNotOpen` varchar(255) DEFAULT NULL,
`CentreActive` tinyint(1) NOT NULL,
`MembershipCentre` tinyint(1) NOT NULL,
`CentreFundingTypeID` smallint(6) NOT NULL DEFAULT '0',
`CreatedBy` varchar(50) NOT NULL,
`CreatedDate` datetime NOT NULL,
`UpdatedBy` varchar(50) DEFAULT NULL,
`UpdatedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`DataSource` varchar(50) DEFAULT NULL COMMENT 'Where did we get this data from?',
`DeliverMaths3To2` tinyint(1) DEFAULT NULL,
`DeliverEnglish3To2` tinyint(1) DEFAULT NULL,
`ReceiptSFA1213` tinyint(1) DEFAULT NULL,
`SFAActivities` varchar(500) DEFAULT NULL,
`OtherFunding1213` varchar(500) DEFAULT NULL,
`AwardingBodyStatus1213` varchar(500) DEFAULT NULL,
`Partnerships` varchar(500) DEFAULT NULL,
`provideAltContact` tinyint(1) DEFAULT NULL,
`AltContactTitle` varchar(9) DEFAULT NULL,
`AltContactFirstName` varchar(100) DEFAULT NULL,
`AltContactLastName` varchar(100) DEFAULT NULL,
`AltContactJobTitle` varchar(100) DEFAULT NULL,
`AltContactEmail` varchar(100) DEFAULT NULL,
`AltContactPhone` varchar(12) DEFAULT NULL,
`doesCloseAtLunch` tinyint(1) DEFAULT NULL,
`lunchClosingTime` varchar(16) DEFAULT NULL,
`PartnershipsInfo` text,
PRIMARY KEY (`CentreID`),
KEY `PartnerType` (`PartnerType`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

And this is my query that is built dynamically in Joomla:

SELECT SQL_CALC_FOUND_ROWS
"---" AS CentreName,
NetworkType,
FundingStateID,
SUM(RegsTarget) AS RegsProfile,
SUM(goOnRegistrationCount) AS goonRegs,
SUM(goOnOLBStartsCount) AS goonStarts,
SUM(ComplsTarget) AS ComplsProfile,
SUM(goOnOLBLADCount) AS goonComps,
DATE_FORMAT(dateReference, "%Y-%m") AS dateReference,
"---" AS CNA_ID
FROM ukon_dw3.persistentNetworkType AS nt,
uko_2.hh_Centre AS c,
ukon_dw2.currentCentreFunding AS cf,
ukon_dw2.centresDataByDay AS cd
LEFT OUTER JOIN
ukon_dw3.NewpersistentCentreProfile AS p
ON cd.CNA_ID = p.CNA_ID AND
cd.dateReference BETWEEN p.ProfileEffectiveFrom AND
p.ProfileEffectiveTo
WHERE cd.dateReference >= "2012-07-01" AND
cd.dateReference <= "2012-08-17 23:59:59" AND
cf.NetworkTypeID IN (3) AND
cf.CNA_ID = cd.CNA_ID AND
cf.NetworkTypeID = nt.NetworkTypeID AND
c.CentreID = cf.CNA_ID AND
DATE_FORMAT(p.YearMonth,'%Y-%m') BETWEEN
DATE_FORMAT('2012-07-01 00:00:00','%Y-%m') AND
DATE_FORMAT('2012-08-16 23:59:59', '%Y-%m') AND
p.ProfileActive = 1 AND
p.CourseID IN (1)

GROUP BY DATE_FORMAT(dateReference, "%Y-%m"),
NetworkType,
FundingStateID

1). I am struggling to define the tables and resultant joins between the
cf and cd tables. The cd table possesses a compound key that hold the date i.e 365 record for each centre. The cf table doesn't possess a daily date only a centre (CNA_ID) and startDatetime colum. Hence I can't get a good join.

2).

I am also struggling to define the cd and and p joins

ukon_dw2.centresDataByDay AS cd
LEFT OUTER JOIN
ukon_dw3.NewpersistentCentreProfile AS p
ON cd.CNA_ID = p.CNA_ID AND
cd.dateReference BETWEEN p.ProfileEffectiveFrom AND
p.ProfileEffectiveTo plus ancillary p cols against constants in the where clause. The cd table hold 365 record for each centre but the profile table is by month. I have added the effectivefrom and effectiveto column to try to address the cartesian like problem.
I have added a number of unique keys to the p table because I am trying to cater for future scalability such as the introduction of profiles for multiple courses at a particular centre.

Many thanks for your help.

Dave

Working with a billion rows (1 reply)

Mike Mullins — Fri, 10 Aug 2012 08:01:54 +0000

My goal is to use a MySQL database to prevent users from using a password that exists in a password dictionary. The dictionary that I want to use has about a billion entries. Getting the data in is one obstacle, but the most important is efficient searching.

1. Is this feasible?
2. What are my options for table design? One table, multiple?
3. What's the best way to get the data in?
4. How can I optimize search?

I've used MySQL peretty heavily for basic data sets. This is my first foray into the world of large databases. My frontend will be PHP. I can use whatever (free) MySQL version that is recommended but I'm not inclined to switch to a different SQL.

Thanks for any tips.

A design quiz (4 replies)

Vincent R&D — Tue, 21 Aug 2012 05:22:18 +0000

Dear all,

"Company A have large data and needs quick realtime monitoring also. Normally two to three milions of records are inserted into database per day. A realtime monitoring is also needed over the latest incoming data. The historical data must be stored in a way suitable for future analysis and business applications."

Which DW model would you suggest?

- Dimensional Model,
- Corporate Information Factory,
- Data Vault,
- OLAP Cubes,

or what combinations of above items?

Yours sincerely,
Vincent

Business Intelligence + mysql (5 replies)

Vincent R&D — Sun, 19 Aug 2012 20:50:50 +0000

Dear all,

We are in development phase of a network monitoring system. As a demanding feature we are going to equip our solution with business intelligence.

As a person who is not aquainted with BI too much, I would like to know more about the requirements of a successful BI with regard to db scheme design. We already designed our mysql scheme almost close to OLTP standards, but I want to know what makes my database ERD scheme correctly useful for a BI implementation.

We have not yet decided about the tool we are going to use for BI, so I would be pleased if you give me some clues about available products.

Best regards,
Vincent

Formin sql query + Android (1 reply)

chanchal kumari — Sat, 04 Aug 2012 04:52:13 +0000

Hi,
In my android app, I am getting a list of nearby restaurants from Google Place API.

But unfortunately, this list does not give menus of the restaurant.

I have T_RESTAURANT and T_MENU tables.

Lets say, I get 4 restaurants in the list returned by API, then how should I make my query to extract data.

If I do:
select name, votes, review from T_MENU where restaurant_name = REST_NAME_1;
and I have to fire this query for each of the restaurants i.e. 4 times in this case.

Can anyone suggest me a good solution?

Thanks.

please check my db design (no replies)

Moisha Moska — Thu, 02 Aug 2012 07:28:40 +0000

wrong thead, sorry

Optimize this query (1 reply)

phouthasone thongmanivong — Thu, 02 Aug 2012 03:02:38 +0000

Dear friends,
i have the following query

select distinct `loan_trend_ltr`.`ltr_period` AS `ltr_period`,
`loan_trend_ltr`.`bnk_code` AS `bnk_code`,
`contact_ctc`.`ctc_province_code` AS `ctc_province_code`,
`loan_trend_ltr`.`lon_class` AS `lon_class`,
`loan_trend_ltr`.`lon_status` AS `lon_status`,
`loan_trend_ltr`.`branch_id` AS `branch_id`,
`loan_trend_ltr`.`lon_sys_id` AS `lon_sys_id`,
`loan_trend_ltr`.`lon_update_date` AS `lon_update_date`
from (`loan_trend_ltr`
join `contact_ctc`
on(((`loan_trend_ltr`.`bnk_code` = `contact_ctc`.`bnk_code`)
and (`contact_ctc`.`branch_id` = `loan_trend_ltr`.`branch_id`))))

where (`contact_ctc`.`ctc_province_code` is not null)
group by `loan_trend_ltr`.`lon_sys_id` having (`loan_trend_ltr`.`lon_status` = _utf8'ACTIVE')

each table has 100000+ records so it takes so long time running.

anyone please help me optimize this query

schema optimization (2 replies)

dileep ch — Mon, 23 Jul 2012 18:56:53 +0000

hi,

we are using a table event_logs, there are 24 columns in that table, by looking at columns can you say we normalize the table by splitting the columns.

Most of the queries are slow, We already have indexes for all queries.

+----------------+------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------------------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| event_id | int(11) | NO | MUL | NULL | |
| session_id | bigint(20) | NO | | NULL | |
| param1 | int(11) | NO | MUL | NULL | |
| param2 | int(11) | NO | MUL | NULL | |
| param3 | int(11) | NO | MUL | NULL | |
| param4 | int(11) | NO | MUL | NULL | |
| param5 | int(11) | NO | MUL | NULL | |
| param6 | int(11) | NO | MUL | NULL | |
| param7 | int(11) | NO | MUL | NULL | |
| param8 | int(11) | NO | MUL | NULL | |
| lat | float | NO | | 0 | |
| longi | float | NO | | 0 | |
| country_id | int(11) | NO | | NULL | |
| ip | int(32) | NO | | NULL | |
| cdate | date | NO | PRI | NULL | |
| ctime | time | NO | | NULL | |
| cts | timestamp | NO | | 0000-00-00 00:00:00 | |
| os_version | int(11) | NO | | NULL | |
| app_version | int(11) | NO | | NULL | |
| device_version | int(11) | NO | | NULL | |
| udid | int(11) | NO | | NULL | |
| jail_broken | tinyint(4) | NO | | 0 | |
| jail_broken2 | tinyint(4) | NO | | 0 | |
+----------------+------------+------+-----+---------------------+----------------

I would like to move lat,longi,ip,os_version,app_version,device_version itto another table.

Is it correct one to do to make the query easier.

The table contains 4 crore records.

Calculated "virtual" rows (7 replies)

Hauke P. — Thu, 02 Aug 2012 14:31:34 +0000

I'm new to these forums, so please forgive me if I chose the wrong forum for this post.

I'm currently planning the implementation of some kind of BI system. While creating the DB design, I encountered a problem for which I hope to get hints to solve it on these forums.
The system I'm designing should store a set of business figures for a set of years and locations. My core problem is that some of the figures are not directly stored in the database but have to be calculated from other figures.

Please take a look the following example DB:

mysql> select * from dim_location;
+-------------+------------------+---------------------+
| location_id | location_name    | location_address    |
+-------------+------------------+---------------------+
|           1 | Office Munich    | NULL                |
|           2 | Office Stuttgart | Koenigstraße 1      |
|           3 | Office Hamburg   | Reeperbahn 3        |
|           4 | Office Berlin    | Unter den Linden 16 |
+-------------+------------------+---------------------+

mysql> select * from dim_year;
+---------+------------------------+
| year_id | year_comments          |
+---------+------------------------+
|    2009 | NULL                   |
|    2010 | the crisis hit us hard |
|    2011 | NULL                   |
|    2012 | getting better again   |
+---------+------------------------+

mysql> select * from dim_figure;
+---------------------------+----------------------------------------------------+--------------------------------------------------------+
| figure_id                 | figure_description                                 | figure_rule                                            |
+---------------------------+----------------------------------------------------+--------------------------------------------------------+
| admin_costs               | Administrative Costs                               | NULL                                                   |
| admin_costs_purchase_dist | Administrative Costs distribution key for Purchase | NULL                                                   |
| admin_costs_sales_dist    | Administrative Costs distribution key for Sales    | NULL                                                   |
| admin_costs_stock_dist    | Administrative Costs distribution key for Stock    | NULL                                                   |
| goods_costs               | Costs for buying goods                             | NULL                                                   |
| marketing_costs           | Costs for doing marketing                          | NULL                                                   |
| purchase_costs            | Total Purchase Costs                               | goods_costs + admin_costs_purchase_dist * admin_costs  |
| sales_costs               | Total Sales Costs                                  | marketing_costs + admin_costs_sales_dist * admin_costs |
| stock_costs               | Total Stock Costs                                  | warehouse_costs + admin_costs_stock_dist * admin_costs |
| total_costs               | Total Costs                                        | purchase_costs + stock_costs + sales_costs             |
| warehouse_costs           | Costs for maintaining the warehouse                | NULL                                                   |
+---------------------------+----------------------------------------------------+--------------------------------------------------------+

mysql> select * from fact_data;
+---------+-------------+-------------+-------+
| year_id | location_id | figure_id   | value |
+---------+-------------+-------------+-------+
|    2009 |           1 | admin_costs |   300 |
...

My current plan is to store all "basic" figures (i.e. figures that don't have a figure_rule) in the fact_data table. On the other hand, all calculated figures should be calculated in some kind of magic view... something like this:

SELECT year_id, location_id, figure_id, value FROM ###MAGIC### WHERE figure_id IN ("total_costs", "purchase_costs", "goods_costs") AND year_id = 2010;

My question boils down to: What do I put in ###MAGIC###? ;-)
And: If something like this is not possible (or not performant for ~200 figures and ~30 locations), what design alternatives would you suggest?

Just for clarification: I put the figure_rule in the dim_figure table only for demonstration purposes. Of course the figure_rule has to become a part in some query.
I already tried something like this:

(SELECT * FROM fact_data)
UNION
(SELECT a.year_id, a.location_id, "purchase_costs", a.value + b.value * c.value 
 FROM fact_data a
 LEFT JOIN fact_data b ON a.year_id = b.year_id AND a.location_id = b.location_id AND b.figure_id = "admin_costs_purchase_dist"
 LEFT JOIN fact_data c ON a.year_id = c.year_id AND a.location_id = c.location_id AND c.figure_id = "admin_costs"
 WHERE a.figure_id = "goods_costs");

However, this is basically the opposite of performant. ;)

Thank you for any replies in advance!

Cheers,
Hauke

MySQL Query (2 replies)

Vance Raeburn — Mon, 09 Jul 2012 12:22:14 +0000

Any help appreciated -

I have 3 tables:

Table 1 (Postcode_Groups) where example rows are:
BS1,BS2,BS3,
BS4,BS5,BS6
etc

Table 2 (Customers) where example rows are:
customer postcode_district = 'BS2'
customer postcode_district = 'BS3'
customer postcode_district = 'BS7'

Table 3 (GolfClub) where example postcode_district of the club = 'BS3'

I need a select * on customers where golfclub postcode_district = 'BS3'

Thanks in advance - Vance (Bristol UK)