MySQL Forums - Database Design and Data Modelling

Web App Database Design (no replies)

Nav Moud — Fri, 20 Nov 2009 21:30:28 +0000

I am confused in how to setup an Internal/External Environment. We have a Windows 2003 Server box that has MySQL server running on it. Which in turn have 5 databases on it. One DB is used for Our Internal Application and other 4 are used for things like internal messenger/internal portal/bug tracking system etc. We recently got a new server which is strictly going to be an External Box and will have an external FTP/Email/SMTP Server/A new Web App to be built.

My question is that is it a good idea to install another MySQL Server on this New External Box or just create another DB in the existing MySQL Server (Internal) and use that DB for External Web Application? What would be a good Practice in terms of Architecture? Another thing is that this "Internal Database" is being used in an Internal Application and eventually we would like Our Users in remote area to be able to go on new Web Application log in put there times/hours in and would like to integrate the data received from users with the internal application we are running. Of course, in future this Web App will most probably grow to something bigger.

I can even create a Virtual Server on this new External box to install another MySQL Server and use this strictly for the External Web App, but I am not sure if that's a good way to go or not. Since we already have an Internal DB with lots of data.

Please advice and thanks in advance!

Mysql Experts Please suggest/comment on my DB design (no replies)

Wane cooper — Fri, 20 Nov 2009 06:38:49 +0000

Hello,

Ours is a java application which works with multiple vendors and their different services and needs weekly latest db updates from them. Means we upload the databases given by them on a weekly basis in to our db. At times these vendors change there table structures and in turn we too modify our tables to accommodate the change and our code too. There are times when vendors go away and new vendors come in. As you can see that ours is a very volatile business. We used the exact table structure given by the vendors which was creating a mess/re-work every time. In order to avoid this we came up with dynamic structure.

We created a
1)vendor table
2)Vendor services table
3)Vendor and service mapping table.
4)Store just all the vendors table names in one table.
5)Store all vendors table columns in one table.
6)Store all vendors column data in one table.

So the columns became each row in this new design. Here are the tables.

CREATE TABLE `third_party_vendors` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `third_party_vendor_service_type` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(40) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB

CREATE TABLE `vendor_service_mapping` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`third_party_vendors_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`third_party_vendor_service_type_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `fk_third_party_vendors_id` (`third_party_vendors_id`),
KEY `fk_third_party_vendor_service_type_id` (`third_party_vendor_service_type_id`),
CONSTRAINT `vendor_service_mapping_ibfk_1` FOREIGN KEY (`third_party_vendors_id`) REFERENCES `third_party_vendors` (`id`),
CONSTRAINT `vendor_service_mapping_ibfk_2` FOREIGN KEY (`third_party_vendor_service_type_id`) REFERENCES `third_party_vendor_service_type` (`id`)
) ENGINE=InnoDB;

CREATE TABLE `virtual_table_name` (
`id` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,
`table_name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `virtual_column_name` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`virtual_table_name_id` tinyint(2) unsigned NOT NULL,
`vendor_service_mapping_id` smallint(5) unsigned NOT NULL,
`column_name` varchar(30) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_virtual_table_name_id` (`virtual_table_name_id`),
KEY `fk_vendor_service_mapping_id` (`vendor_service_mapping_id`),
CONSTRAINT `virtual_column_name_ibfk_1` FOREIGN KEY (`virtual_table_name_id`) REFERENCES `virtual_table_name` (`id`),
CONSTRAINT `virtual_column_name_ibfk_2` FOREIGN KEY (`vendor_service_mapping_id`) REFERENCES `vendor_service_mapping` (`id`)
) ENGINE=InnoDB;

CREATE TABLE `virtual_column_data` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`virtual_column_name_id` smallint(5) unsigned NOT NULL,
`data` tinytext,
PRIMARY KEY (`id`),
KEY `fk_virtual_column_name_id` (`virtual_column_name_id`),
CONSTRAINT `virtual_column_data_ibfk_1` FOREIGN KEY (`virtual_column_name_id`) REFERENCES `virtual_column_name` (`id`)
) ENGINE=InnoDB;

I hope you must have got a picture what i am trying to do. The virtual_column_data will hold a every vendors column data and each column data becomes a single row.

Advantages
-----------
1) I dont have to change the my code or db structure
2) Irrespective of any vendor table structure changes or any vendor leaving or coming in. there will not be any profound change.
3) As this table grows over a period of time i am planning on apply partitioning on the last table.(i know partitioning does not support foreign key, but got a work around that too).

I want mysql experts to please guide me or suggest is my approach correct or not.What are the various draw backs to this approach. Or Is this approach at least practical in nature in long term perspective...

Thanks

Book on Database design (no replies)

Ankur grover — Thu, 19 Nov 2009 09:18:37 +0000

Hi,

I am new to web development and satrted working on a project in php-MySql, could someone suggest a good book on Database Design.

Regards
Ankur

Can this query be improved ? (1 reply)

Qamar Ali — Wed, 18 Nov 2009 23:04:48 +0000

I have the following database structure:

CREATE TABLE IF NOT EXISTS `dailydak` (
`DAK_ID` int(10) NOT NULL auto_increment,
`REC_DATE` date default '0000-00-00',
`DAK_TYPE_ID` int(11) NOT NULL,
`DAIRY_NO` varchar(20) NOT NULL,
`DAK_FROM` varchar(50) default NULL,
`SUBJECT` varchar(255) default '',
`PresidentsRemarks` varchar(255) default NULL,
`ENTRY_U_N` varchar(15) default NULL,
`ENTRY_DATE` date default '0000-00-00',
`DEP_CODE_FROM` varchar(2) default '',
`DEP_CODE_TO` varchar(2) default NULL,
`ACTION_TAKEN_1` varchar(255) default NULL,
`ACTION_TAKEN_DATE_1` date default '0000-00-00',
`ACTION_TAKEN_U_N_1` varchar(15) default NULL,
`ACTION_TAKEN_2` varchar(255) default NULL,
`ACTION_TAKEN_DATE_2` date default '0000-00-00',
`ACTION_TAKEN_U_N_2` varchar(15) default NULL,
PRIMARY KEY (`DAK_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;

-- --------------------------------------------------------

--
-- Table structure for table `dak_types`
--

CREATE TABLE IF NOT EXISTS `dak_types` (
`type_id` int(11) NOT NULL auto_increment,
`dak_type`http://202.59.74.108/phpmyadmin/index.php?db=lccidak&token=5df78c2b99f20c2d536cd6132cabe3b6 varchar(24) NOT NULL,
PRIMARY KEY (`type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='hold the types of dak received' AUTO_INCREMENT=5 ;

-- --------------------------------------------------------

--
-- Table structure for table `department`
--

CREATE TABLE IF NOT EXISTS `department` (
`DEP_CODE` varchar(2) default '',
`DEP_NAME` varchar(40) default NULL,
`PK_ID` int(12) NOT NULL auto_increment,
PRIMARY KEY (`PK_ID`),
UNIQUE KEY `PK_dep_file` (`DEP_CODE`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
`USERID` int(10) NOT NULL auto_increment,
`U_N` varchar(15) default NULL,
`PASS` varchar(40) NOT NULL,
`ADMIN_STATE` char(1) default '3',
`USER_PERMISSIONS` text,
`DEP_CODE` varchar(2) default NULL,
PRIMARY KEY (`USERID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=76 ;

I need to improve the following query:

SELECT M.rec_date, M.dak_type, M.dairy_no, M.subject, M.dep_name_from, DP.DEP_NAME as
dep_name_to, M.entry_u_n, M.entry_date,
M.action_taken_1, M.action_taken_date_1, M.action_taken_u_n_1,
M.action_taken_2, M.action_taken_date_2, M.action_taken_u_n_2 FROM
(
SELECT d.rec_date, dt.dak_type, d.dairy_no, d.subject, d.dep_code_from,dep.DEP_NAME as dep_name_from,
d.dep_code_to,'' as dep_name_to, d.entry_u_n, d.entry_date,
d.action_taken_1, d.action_taken_date_1, d.action_taken_u_n_1,
d.action_taken_2, d.action_taken_date_2, d.action_taken_u_n_2
FROM dailydak d, department dep, dak_types dt
WHERE (d.dep_code_from=dep.DEP_CODE) and (d.dak_type_id = dt.type_id)
)
M , department DP WHERE M.dep_code_to=DP.DEP_CODE

How can I improve the performance of this query ?

Thank you

Qamar,

Engines, Fulltext Indexes, Transactions? (1 reply)

Remington Seph — Thu, 19 Nov 2009 01:05:48 +0000

I'm in the process of designing a database that should be able to incorporate fulltext searches. When inserting information into the database it is of absolute importance that all queries execute or none at all. This needs to make use of transactions. If I use the myIsam engine which supports fulltext indexes, I cannot perform transactions. Also if I use the InnoDB engine i'm unable to perform fulltext searches. Any suggestions? Workarounds?

Combining multiple tables by date/time (1 reply)

Dion Houston Sr — Sun, 08 Nov 2009 04:06:56 +0000

Hi all,

Full disclosure, I'm not actually doing this in MySQL, but I couldn't find any general database modeling forums and I need help. :)

I am trying to model a database containing information about events from different sources. All of these tables have date and time (DATETIME and INT respectively), and I've added an integer primary key to each table.

To make it a bit more concrete, say for example table1 and table2 look like

PKEY, DATE, TIME, DESCRIPTION
PKEY, DATE, TIME, CATEGORY, LOCATION, etc.

The desired end state is a query which produces

DATE, TIME, DESCRIPTION, CATEGORY, LOCATION, etc.

Which contains all records in both tables (my real model currently has 4 tables). Currently the way that I'm doing it is by having an additional table in the form

PKEY, DATE, TIME, table1ID, table2ID

Which I populate with the key of the appropriate table in the appropriate field and then join.

The biggest issue I have with this approach is that each change I make to table1 or table2 requires me to make an appropriate change in the additional table. If possible I'd like to do this through modeling instead. Secondarily, I imagine that as I add sources that the performance of this will be close to O(N^2).

Does anyone have any suggestions on how to improve this scenario? I'm quite a bit rusty on SQL, but I don't recall an easy way to do this. Theory rather than actual code is most appreciated, but any suggestions I'll make work.

Thanks in advance,

Dion

need help with DataBase Normalization (1 reply)

piotr motyl — Fri, 06 Nov 2009 05:11:52 +0000

Hello. I have to normalize a tables in database, I would like to ask anyone experienced with databased if below normalization is correct for 1F,2F,3F. thanks for reply

Normalization

Cooking( rID, rname, ( ingredientID, ingredientName, amount), cookID, cookName, cookPhNumber);

cookID -> cookName
cookID -> cookPhNumber
?rID -> rname
?rID -> cookID
?ingredientID -> ingredientName

1NF:

RECIPE:
rID – primary key
rName

INGREDIENT:
ingredientID – primary key
ingredientName
amount

COOK
cookID – primary key
cookName
cookPhNumber

I created separate table for each group of related data and identified each row with unique data – primary key.

2NF:

RECIPE
rID – primary key
rname

INGREDIENT
ingredientID – primary key
ingredientName
amount

COOK
cookID – primary key
cookName

COOKPH
cookID – primary key
cookPhNumber

3NF:

RECIPE
rID – primary key
rname

INGREDIENT
ingredientID – primary key
ingredientName
amount

COOK
cookID – primary key
cookName

COOKPH
cookID – primary key
cookPhNumber

COOKRECIPE
cookID – primary key
rID

Urgen query help needed (1 reply)

Eric Hadson — Wed, 04 Nov 2009 21:54:26 +0000

Hi there,

My knowledge in MySQL is pretty basic and i hit a dead-end on one of my projects that is due tomorrow.

I have a table that stores all menu items that looks like below:
----------------------
| CATEGORIES |
----------------------
ID | sub_ID | name |
1 | 0 | name1 |
2 | 0 | name2 |
3 | 0 | name3 |
4 | 1 | name1-1|
5 | 1 | name1-2|
6 | 1 | name1-3|
7 | 2 | name2-1|
8 | 2 | name2-2|
9 | 3 | name3-1|
----------------------

And I also have a table that stores items that go under IDs of the table above:
------------------------
| ITEMS |
------------------------
ID | cat | itemName |
1 | 4 | Stringvalue |
2 | 4 | Stringvalue |
3 | 4 | Stringvalue |
4 | 4 | Stringvalue |
5 | 5 | Stringvalue |
6 | 5 | Stringvalue |
7 | 5 | Stringvalue |
8 | 6 | Stringvalue |
9 | 6 | Stringvalue |
10 | 5 | Stringvalue |
11 | 5 | Stringvalue |
12 | 4 | Stringvalue |
13 | 4 | Stringvalue |
14 | 4 | Stringvalue |
------------------------

My question is how do I get the values with cat=4 from the table above when all i have is value 1 from the first table?

In other words, with value `1`, i want the query to first get the ID values on sub_ID=1 and use those ID values to get the records from the second table on cat='whatever those ID values are'..? make sense? PLZ HELP

Any comments and suggestions are greatly appreciated!

Thanks!

Data modeling techniques and resources (1 reply)

Dermot Duncan — Sun, 25 Oct 2009 18:41:12 +0000

Hi All,

I'm currently working on a thesis for my masters which is investigating 3D data modeling. At the moment I'm just trying to create a concise set of resources before starting my literary review and want to make sure that my facts aren't dated. I was wondering if anyone could offer any advice on good modern data modeling resources. Basically I'm looking for info on data modeling techniques - their similarities and differences and how data modeling has evolved since the theory was adapted in the 70s. Also what works in modern data modeling and what are are the drawbacks. A lot of articles and books I have found seem to be from the late 90s, early 2000s which do seem to be a bit dated? Thanks in advance for your time - any advice or help you could give me really would be greatly appreciated.

Derm

Large table designissue (7 replies)

omkar prabhu — Thu, 19 Nov 2009 18:55:16 +0000

I have a table growing immensely every day , currently it has around 18777410 records.
Our site is available 24x7 and we are using mysql replication to distrubute load on the database server ( a master and single slave combination).
It is almost two years we running the application on mysql 5.0.45 everything seems to fine, but now we are facing problem with maintaining this huge table.
Following are key problem areas:
a) Running aggregate queries takes more than 5 minutes, which is not acceptable with respect to site performane
b) Adding index is not possible as it takes more than 3/4 hours which result in site downtime
c) command like optimize and analyze cant be run as it is online application and it will result in locking of this important table.

Can any one suggest possible approach to solve this problem, we are thinking of trying following approach
a) Have seperate table for having archive data,so query requiring complete data, will need to be fire two queries or have union on archive data and live data
b) Upgrade to mysql 5.1 and see having partitioning on this table helps
c) have mutliple database slave server to hit queries on most used use cases on this table.

Let me know if anyone faced similar problem and how to go about solving it

Data Modelling for an email table (4 replies)

Chris Russell — Wed, 18 Nov 2009 06:02:45 +0000

Hi All,

I have a table in my database, which is basically a maillog ... ie:

ID, Timestamp (int), From (varchar), To (varchar), error

Currently has 2.1 million rows, but I need to potentially double/triple this.

The server itself is being upgraded within the next few weeks, to a 10K RPM, RAID 5, 8 Gig machine, but I`m really looking for some ideas on how to improve the efficency as right now even inserts are taking 5-10 seconds to execute.

I have indexes on timestamp, from and to .. based on lookups the user will perform (which are unfortunately any string in the address).

I've thought about normalizing down by splitting the addresses into from/to but this brings in complexities into the logging application.

Recipient uniqueness is pretty high, but at least count From was around 1.4 million unique addresses.

Could any guru's suggest an alternative way to approach this ? POssibly a stored procedure to auto-split the sender etc thus bring down the number of entries in the index ?

Failing that is there a better indexing method for this type of data.

Any thoughts would be greatly appreciated

Cheers

Chris

calculate variable between two times (no replies)

akerou wassim — Thu, 22 Oct 2009 16:20:01 +0000

my file to calculate is
date time temp
2008-12-01 00:01:00 10.2 -9999 -9999 10.45 10.3 10
2008-12-01 00:02:00 10.233 -9999 -9999 10.5 10.3 10
2008-12-01 00:03:00 10.317 -9999 -9999 10.2 10.1 10.2
2008-12-01 00:04:00 10.467 -9999 -9999 10.5 10.3 10.1
2008-12-01 00:05:00 10.517 -9999 -9999 10.5 10.5 2008-12-01 00:06:00 10.65 -9999 -9999 10.6 10.4 10.217
......
2008-12-31 00:59:00 10.314 -9999
i want to calculate maxi of temp betwenn two times
prog:
for($jour= $jourdebut; $jour <= $jourfin ; $jour++)
{
SELECT date, HEURE, maxi(temp) FROM tt1208 WHERE HEURE BETWEEN '06:01:00' AND '06:00:00' AND date = '$jour' GROUP BY date;

result NULL
}
please help me!!!

Join or subquery for repeating data item (1 reply)

Adrian Cole — Wed, 21 Oct 2009 00:32:43 +0000

Hi, we're struggling to write a query in a simple booking system that has repeating dates.

The tables involved are
Booking table:
booking_id
date
time
location
who_with
fee... etc

Dates table:
booking_id
date

We need to find bookings within a date range, plus who_with and location, and return all fields in the booking. I've read up about joins and subqueries but still can't figure it out.

Thanks in advance for any suggestions

real client's project...please help in optimizing design!! (1 reply)

Ajeet Chadha — Sun, 18 Oct 2009 17:01:50 +0000

Right, I poster earlier with a practice table I was working on. I appreciated the reply and have been working on that.. but yesterday I got a real client's project.

I have been given the task of optimizing the database design.
The database keeps track of the transactions the company makes. A transaction is made and records are stored in several different tables.

These are the tables:

Ledger
Group
User
Opening Balance
Transaction
Transaction Type

The client would like

1) the data to be stored in ONE location and not 2 different places

2) to save disk space

3) prevent duplicate entries being

Any ideaS? I wont post the current design of the database unless necessary as I would like to try and do it all by myself but some pointers would be appreciated.

Initially I was thinking of denormalization. Eradicating the multiple tables and placing all data in one table. But this might not be the best strategy. How would you do it? What practices would you use?

ANY help would be appreciated.

Thanks!!

Designing a Normalized Database (1 reply)

Gautam Kumar — Sun, 18 Oct 2009 03:46:52 +0000

I am in a fix on how to crack this database design nut. I believe that all you coders are gonna love this.

I have a class of students whose grades are to be recorded. There are 52 distinct types of grades that a single student can get. A single student can get plural types of grades. Also a student can get same type of grade multiple number of times or not receive any grades at all. Consider the following sample:

Student A gets Type I Grade 3 times, Type II Grade 1 time
Student B gets No Grade at all
Student C gets Type II Grade twice, Type XII Grade 5 times
etc, and so forth

I have a report based on grades to be displayed by querying this database, example report may be:

Names of students that received no grade at all
Names of students that received Type II Grade once
Names of students that received Type VI Grade more than once
etc and so forth

What are your suggestions??

Is this database structure correct? Please help oh wise ones :) (2 replies)

Ajeet Chadha — Fri, 16 Oct 2009 05:53:12 +0000

I am fairly new to mysql and wanted to practice making a database and tables. As well as filling the tables with made up data and then practicing queries. I wanted to emulate a real life database project.

So I decided to make a database for a Car Dealership.

the database consists of three tables...Car...Buyer..Repairs. The structure is displayed below.

CAR table -

car_id int(5) primary key
make varchar(15)
model varchar(15)
year int(4)
origin varchar(15)
buyer int(5) foreign key references buyer_id in the BUYER table
cost double(5,2)

BUYER table -

buyer_id int(5) primary key
name varchar(20)
address varchar(20)
tel int(10)
car_id int(5) foreign key references car_id in the CAR table

REPAIRS table-

part_id int(5) primary key
origin varchar(15)
cost double(5,2)
car_id int(5) foreign key references car_id in the CAR table

Do you see any problems in this structure?
anything I should have done differently?
anything I could do to improve database performance?

Please bare with me as I am new to this and wish to be a DBA one day soon!

All comments will be appreciated!!

Thanks people

How to index this table? (5 replies)

Ian Barnes — Mon, 12 Oct 2009 06:46:38 +0000

Hi,

I have a table that stores article views per day per article:

`post_id` int(11) NOT NULL,
`date` date NOT NULL,
`views` int(11) NOT NULL

A couple of entries:
1987 2009-10-04 1744
1583 2009-10-04 2626
1238 2009-10-04 2211

This works fine - but I am now trying to figure out whats the best way to add an index to this so that if I am running a query limiting between dates I dont have to run through all the hundreds of thousands of records to find a couple hundred results.

my query looks something like this (for 7 days top articles):
SELECT articles.*, sum(views) AS views FROM articles LEFT JOIN article_views ON article_views.article_id = articles.id WHERE ( date <= '2009-10-07' AND date >= '2009-10-01') GROUP BY article_id

Any help would be greatly appreciated.

Thanks
Ian

ok for import data (no replies)

akerou wassim — Tue, 06 Oct 2009 13:00:45 +0000

thank you very much for your advice I finally managed to import my data Climate minute basis by applying the attempted DATETIME column is the only way to import my data.
greetings

m.mezred

Problem with Database Design (1 reply)

aaa aaa — Mon, 05 Oct 2009 01:57:17 +0000

Hi!

I am a student and I am working on database that concerns customer-order-product relationship. When designing it I encountered an issue that I do not really know how to solve it in the way it seems reasonable and obeys Normalization process. I would appreciate for any assistance.

Description of my problem:
We have product for instance T-shirt that can have different sizes and colors and each of this specific combination has quantity (http://img251.imageshack.us/img251/8871/products.jpg)
Next I created relation between Orders and Products as many-to-many (http://img406.imageshack.us/img406/8471/orderdetails.jpg). And here problems starts… In Order_Details table if someone order 5x of T-shirt with size S and color blue, and then 3x of the same T-shirt but with M size I will have repeated some values. Another thing is that Size and Color attributes rely only only on ProductFK Key and not the whole Key (OrderFK-ProductFK) so I moved these details to another table like here(http://img18.imageshack.us/img18/3507/productorderdetails.jpg), but now arise another problem Quantity attribute… in my it should be placed in Order_Details, right ? The thing is now I ended up with basically the same table like Product_Size_Color (http://img251.imageshack.us/img251/8871/products.jpg) but without Quantity attribute which we do not need here since I assume it should be in Order_Details table. How I can combine it altogether to get consistent database ?

Whole database until now looks like this (http://img18.imageshack.us/img18/8130/wholedatabase.jpg)
If there is anything else that in Your opinion is bad please let me know.

Using null to mean all or any (7 replies)

Jim Haberlin — Fri, 02 Oct 2009 20:48:58 +0000

I am new to MySQL and I am trying to determine how to handle a situation where I have a foreign key but I want to have a hierarchy and have a meaning of ALL or ANY.

These easiest way to describe it is with an example.

I have a customer table with many customers.
I have a rate table with many rates.
I want to set up a rate of $100.00 that applies to all of my customers.
I then need a rate of $90.00 that applies to only one customer, Customer A.
If I am searching for the correct rate for Customer A I should find the row with the $90.00 rate.
If I search for a rate for any other customer I should find the rate of $100.00.

My instinct is to have a customer foreign key on the rate table that would have Customer A's key on the $90.00 rate and a null value on the $100.00 rate.
The query would be: Select amount from Rate where rate.customer is "Customer A" or rate.customer is null.

This works but I am concerned with efficiency and with having correct database design. I am not arguing in favor of this solution I am merely trying to find out if it is a valid design or if there are other solutions for this scenario.

I would appreciate any suggestions.

Distributed database creation (1 reply)

Amit Rane — Thu, 01 Oct 2009 14:13:35 +0000

HI

I want to create a distributed database.
the structure of database is same on all the servers, only the contents are different.

How should i create this kind of distributed database...
What are the necessary implementation steps fot doin this

Thnx
Amit.

Basic Primary key syntax (3 replies)

BW Bloch — Wed, 30 Sep 2009 04:47:23 +0000

I have a table with data about organizations. Each row represents a quarterly report filed by an organization; an organization files many reports over time and thus generate multiple rows. One column is an OrgID number that uniquely identifies each organization. OrgID also appears in another table of people that donate to the different organizations. I want to make OrgID in the donations table a foreign key referring to OrgID in the organizations table as the primary key. But there will be multiple entries for a given OrgID in the organizations table. What's the way around this?

Thanks!

Help storing time in database table (1 reply)

Aadlene Wesley — Mon, 28 Sep 2009 00:05:07 +0000

Hi Guys,

I am new to mysql, I am creating a database where in one of the tables I need to store just the time
not the date value. So I need to store just "23:12:04" and frequently a search will be applied to the feild.

Can anyone suggest a best way to store the time in table which also has good performance on search.

Thanks

How to import data from Multiple servers using federated engine (6 replies)

Amit Rane — Thu, 24 Sep 2009 06:16:59 +0000

Hi all,

I wish to import data from multiple servers onto a single local machine using a federated engine.

the data is distributed on multiple servers where the database structure is same
only the table contents are different.
eg:

Server1:

table name question.

id........type
_________________
1 ........easy
2 ........medium
3 ........hard

Server2:

table name question.

id........type
_________________
4 ........easy
5 ........medium
6 ........hard

now the above two tables are on different servers.
I want to import data from both these tables using single federated table
and show the output as follows on a client machine.

Client:

table name question.

id type
________________
1 easy
2 medium
3 hard
4 easy
5 medium
6 hard

please i need it urgently.

thanx in advance

Amit.

How to import data from Multiple servers using federated engine (no replies)

Amit Rane — Fri, 18 Sep 2009 09:31:29 +0000

Hi all,

I wish to import data from multiple servers onto a single local machine using a federated engine.

the data is distributed on multiple servers where the database structure is same
only the table contents are different.
eg:

Server1: Server2:

table name question. table name question

id type id type
_________________ ______________
1 easy 4 easy
2 medium 5 medium
3 hard 6 hard

now the above two tables are on different servers.
I want to import data from both these tables using single federated table
and show the output as follows on a client machine.

Client:

table name question.

id type
________________
1 easy
2 medium
3 hard
4 easy
5 medium
6 hard

please i need it urgently.

thanx in advance

Amit.

How to make an Entity relationships with Shopping casrt application (no replies)

Vikas Tailor — Fri, 18 Sep 2009 08:56:37 +0000

Hi everyone. Can you please suggest me the idea of ER planning with any E commerce application. I am confused with this by thinking a lot of stuffs in my mind.

Thanks

Query question (1 reply)

Neil Bailey — Fri, 18 Sep 2009 03:09:11 +0000

I'm not sure if this is the correct forum for this or not - if not, if someone could point me in the right direction, I'd be happy to repost the question there.

We have a table of medical diagnosis records, and each diagnosis record contains a field called 'code' - a gov't assigned code for that particular diagnosis.

A sampling of the codes might be something like:

151
151.1
151.12
151.2
152
153
153.1
154

We want a query that would return the codes for which there are no more granular codes - so in the example above, 151 would not be returned; 151.1 would not be returned; 151.12 WOULD be returned, as would 151.2, and 152; 153 would NOT be returned, but 153.1 WOULD be returned, as would 154.

does this make sense?

Is there a query that would return this data that is not going to absolutely CRUSH performance?

storing multiple values as binary in one field (1 reply)

Ole Mortensen — Sun, 13 Sep 2009 21:06:36 +0000

Hi
I have a project where I need to store a large number of values.
The data is a dataset holding 1024 2Byte Unsigned integer values. Now I store one value at one row together with a timestamp and a unik ID.
This data is continously stored based on a time trigger.

What I would like to do, is store all 1024 values in one field. So would it be possible to do some routine that stores all the 1024 2byte integer values in one field as binary. Some thing like when you store a image.

Thanks.

Br.
Enghoej

Database Design for Demography (no replies)

Amit Tandon — Thu, 10 Sep 2009 13:36:30 +0000

What would a good design be for a Demographic / Demographic Surveillance Database ??

Does anyone have any links or tutorials on this ??

Simple Help With Multiple Tables and JOIN? (1 reply)

John Jackson — Thu, 10 Sep 2009 14:08:04 +0000

Hi All,

I'm rather new to mysql, and could use some guidance in performing a query. I believe that I need to use the 'JOIN' operators.

Here is what I have/what I'm trying to accomplish:

table_players
table_games
joiningtable_players_games

in table_players each player is given a unique 'player_id'

in table_games each game is given a unique 'game_id' AND a field called 'playerTurn' to indicate whether it's player 1 or player 2's turn... simple
enough

in joiningtable_players_games i have 2 foreign keys (1 for 'player_id', 1 for 'game_id') and a field called 'assignedPlayerNumber' to indicate which playerNumber (1 or 2) this 'player_id' is in this unique game's record.

I need to formulate a query statement in order to retrieve the NUMBER of games in which the GIVEN player_id in which both the columns 'assignedPlayer' in 1 table, and 'playerTurnNumber' in the other are = .

Does that make sense?

I can't seem to retrieve the correct number of games in which this $player_id is assigned to the current playerTurnNumber in each respective game_id...

I am using php, and attempting to use the command: mysql_num_rows($result)

Can anyone help me out in formulating a correct Query SELECT statement?

Thanks in advance...