MYSQL :: Design Effective MySQL DB

What Is The Effective Way To Query Balance?
I have 2 tables, payment and receive, i want to retrieve the balance for a user.

mysql> select * from payment;
+-----------+--------+---------+
| paymentid | userid | payment |
+-----------+--------+---------+
| 1 | 1 | 2.0000 |
| 2 | 1 | 2.0000 |
| 3 | 2 | 2.0000 |
+-----------+--------+---------+
3 rows in set (0.00 sec)

mysql> select * from receive;
+-----------+--------+---------+
| receiveid | userid | receive |
+-----------+--------+---------+
| 1 | 1 | 1.0000 |
| 2 | 2 | 1.0000 |
| 3 | 2 | 1.0000 |
+-----------+--------+---------+

3 rows in set (0.00 sec)
I want to know, how do you deal with this situation?
If I want to get the balance for userid = 2, I have to get the sum of the payment, and minus the sum of the receive. So it will execute 2 sql query for getting balance.

Another approach would be creating another table, called balance. When a new payment being made, the value will be added to `payment` and modify the `balance`. likewise for receive.

mysql> select * from balance;
+-----------+--------+---------+
| balanceid | userid | balance |
+-----------+--------+---------+
| 1 | 1 | 3.0000 |
| 2 | 2 | 0.0000 |
+-----------+--------+---------+

2 rows in set (0.00 sec)
Which method will you use? or do you have better idea for this?

Most Effective Way To Do Multiple Categories ?
Well i'm doing some articles and i'm doing the db now.

But what's the best way to do multiple categories for an article ?

Another table with one row for each category that article is in, as well as the article id ?
Or a field in that articles table with all the category ids separated by something like a dot ? Could you do that and still pull out the names of those categories by looking up their ids in the category table in on query ?

Create The Effective Index
I want to create the best index for thar query:

"select * from talkback where status=0 order by bject_type,vertical_id,event_id"

Which index i need to create for best performance.

How Best To Design DB On MySQL 5.0 So That ..
I couldn't find this by a forum search.

On my local PC, I have MYSQL 5.x and was wondering how best to design certain fields in my table that MySQl 4.1 ( my hosting service ) won't complain about.

For instance, I noticed that when I pushed a PHP app from my local PC, up to my Host provider account, things failed because MySQl 4.1 didn't like 1024 character varchar's.

Is there a guide somewhere that would make it easy to design an downward-compatible DB ?

OO Design With MySQL
I know how to pull data from MySQL into an ASP.NET page using MySql.Data.MySqlClient and its connection, command, and data reader classes. Now I would like to kick it up a notch and start working in OO design principles into my code. The tutorials I've seen seem to be geared toward SQL Server and so I'm spinning my wheels.

Can anybody help me find a good tutorial (or a good book) that spells out in detail how to get the most out of MySQL in an ASP.NET environment. I'm especially interested in learning how to build a data access layer and/or typed datasets.

Mysql Table Design
Any advise are welcome with this inquiry. I have learned MySQL for two weeks after buying books from Kevin Yank and I would like to make a project called Attendance Monitoring System or TimeIn-TimeOut Monitoring System using PHP/MySQL flatforms. However, I have a huge vague or dont know what to do on database or table designing.

Is it feasible if I make a one table for employee's TimeIn and TimeOut? Or do I have to make a separate table for TimeIn and TimeOut. If so, how I would fetch and match employees TimeIn with Timeout for particular shifts. Have 3 shifts in work 23:00 - 08:00, 02:00 - 22:00 and 8:00 - 17:00 on weekdays and weekends are their rest days.

MySql Design Tools
Hello I would like to know if you could advise which would be good design tools for MySql databases.

I already have tried MySQL-Front which is ok but no very graphical, and I tried DBDesigner 4 but this is ineffective as it refuses to connect to any of MySql databases I have setup.

Design Mysql Frontend
I wish to design an entirely new GUI frontend for mysql without the use of php. I would also like to avoid the readily available GUI frontends like EMS mysql manager , rekall etc. I would like to know which programming language is suitable for this purpose. Is it possible to do it with python or perl. If possible which language is better. I wish to run the frontend as a standalone application. Cross platform compatibility is also one of my main concerns. i would be using it mainly in linux, freebsd and windows.
i dont wish to have a webpage frontend for mysql. the frontend has to run as a standalone application.

User Permissions: MySQL DB Design
There is a huge problem I have been breaking my head over in the field of setting user permissions for my CMS.

It is still in the development stage so I have all the freedom I need for any adjustments I need to make.

My main focus of worry is on how to set the permissions, not in a static, but in a dynamic way.

The end goal is to install the CMS for a variety of people. They will then have the control to make their own user groups and permission rules for each group and post.

What I am having problems with is the following:
Figuring out how to design the database to give me the options to:

-Set read, write, edit, move, delete, vote, comment, attach permissions for posts.

-Users can only do these things on posts of their user-level and lower or if they are the author. IF the post category allows this.

Update: Where I am at now:
I have decided to go with assigning 'levels' to each group and for each action.

Level 0 is the admin level, the admin can do whatever he wants. Level 255 is a ban.

I think I should set level-requirements on different types of posts. Let's say an admin announcement has edit-requirement level 0 where as an author article would have edit-requirement level 1 so only trusted mods can change it but reading-level 10 so all people can read it. (example level values).

I was thinking something like this:
posts[post_id....post_level_id=ì«']
post_permissions[permission_id=ì«', read_level=ཆ' edit_level=Ƈ' delete_level=Ɔ'....etc]

Do you guys think this would be a good way to go? And how would you integrate this into the database?

As stated before I edited the post a bit.

MySQL Table Design For Web Tracker
I'm currenty writing a PHP based tracking script and was wondering about an expedient mysql table design. My experiences with mysql are not as good as with PHP at all, so I need a little help.

The main problem is performance, so you may already asume that I'm not looking for poorly conceived table structures but some kind of denormalized ones, in order to improve the performance.

So whats the best way to store the logged information? What you see here is the db structure from phpOpenTracker and that exactly is the way it shouldn't be.
This normalized schema is horrible slow if you have to evaluate a lot of data.

I know a lot about application design but not about db design, so it would be great if anyone could give me some approaches and/or examples.

Visual Design / Editor Of Mysql Databases
I am wondering if any tools exist that provide a GUI for developing mysql databases.

Search Design: Keywords Table (MySQL)
I'm trying to implement keyword searching in my database.
Currently, I have a table containing all my entries with description and name.
The existing sql search utilises fulltext search to find the relevant data from the description and name fields. I'm thinking of adding an additional keyword table that would have higher relevancy in returning results (currently, you'll be lucky to find any relevant entries).
I have a separate table and the design is as follows:
REATE TABLE `keywords` (
`id` smallint(5) NOT NULL auto_increment,
`entryid` mediumint(9) NOT NULL,
`keyword` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;
My other table that contains the description and name fields are `entries`

My problem now is, how can I match the search keywords with that of the table `entries` and `keywords`? I want to avoid using more than one sql query for each search.

For each entry in the table `entries`, there may be up to 20 keywords (e.g. 20 rows in `keywords` table). I'm open to another approach for the keyword table. The reason why I have a separate table is to facilitate management and hopefully be able to pool results in a way of a cloud for keyword statistics.
Can anyone offer any advice/help in this regard?

MySQL Table Design Style With Foreign Keys
I just wondering what proper (My)SQL style would be for the following situation (which I made up for the sake of this)...

You have a SpiceRack table and a Spice table. Each rack has many spices, in typical one-to-many fashion. So the Spice table would have rackID column. Easy.

But let's say each rack has one default spice.

So should the SpiceRack table have a column "DefaultSpiceID" or should the Spice table have a column "isDefault"?

In one sense, I would say the former, to help enforce a single default spice. In another sense, I would say having two tables point to each other is ugly and just do some extra queries to enforce that there is only one with the latter.

And while I'm at it... for that isDefault, and similar T/F columns... What's the best data type? SMALLINT (1) UNSIGNED, ENUM(0,1), or LONGBLOB?

DB Design
is there a tool to view a schematic of a MySQL DB I don't mean the .sql file I
mean a pretty chart type schematic.

DB Design
inserting a base64 string into a text field in my db. Again I may have design issues with this,but why in the world would it take 17 seconds to insert 1.7kb worth of text?
I can read the record in milliseconds, why would it be different the other way around?
What should I do to speed this up or design differently?

DB Design
I don't know why i am haveing a tough time on this one. i could just do it, but i want to do it the best way.

Its for cars; make, model, engine,

I'm just not convinced this is the way I should do it, because I also want to inlcude the years for the cars, and that vairies per model / engine combination

I was going to have a cars table that would have the following, but need help with getting the years in there also. Should Each row in the CARS table have a YEAR field, so a 1993 VW Corrado SLC and a 1994 VW Corrado SLC would have their own rows, even though they are basicly identica cars? or should I do a single row with a from and to year field?

i am leaning towards the one row for every configuration, since the data is small (mostly foriegn keys) and it would be precise and easy to look up between years, by model, etc.

Db Design
i am after a bit of advice here. i am creating a database that will apply to 3 venues london, paris and newyork with the potential for this to grow.
i will be taking bookings for these three venues. Would it be more efficient to have sepearat tables for the bookings and rates and rooms for each venue eg

london.bookings table
paris.booking table
etc

or just have one big booking table and a location id.
It is all theoretical but im working on the assumption that there will be only hundreds of site visitors each day.

Which Design Would Be Correct?
I have registered users, and these users can belong to a group. I'm trying to decide whether or not i should keep this group_id in the users table, or to create a cross referencing table and just keep the user_id and group_id in there. the cross reference table will allow for users to be associated with more than one group. and just keeping the group_id in my users table will allow me to avoid a few joins in some queries but only allow one user per group... any opinions on this matter? after typing this i feel as though creating the referencing table would be the better idea.. but i still would love to hear other opinions.

Table Design
I am designing a table for "friend relationships".

I have three possible solutions:
a)
user_id (int)
friend_id (int)
created_at (timestamp)

This will require 2 rows per friendship.
b)
user_id (int)
friend_id (int)
user_id2 (int)
friend_id2 (int)
created_at (timestamp)
This requires only 1 row, but 2 indices.
c)
user_id (int)
friends (text)

This contains a comma delimited lists of all friend_ids.

I am leaning towards option 3, ofcourse, adding & deleting friends will be a bit slower, but the bulk of the load is on reading of friend data anyways.

However I have a question if this would be slower:

QUERY 1
select items.* from items inner join friends ON friends.friend_id = items.user_id WHERE friends.user_id = [my_user_id]

QUERY 2
select items.* from items where user_id IN (select friends from network where user_id = [my_user_id]

QUERY 3
$friends (variable) = select friends from network where user_id = [my_user_id]

select items.* from items where user_id IN ($friends)

Query 3 requires 2 queries.

1. Which of these would be faster?
2. And would you still choose the solution you chose in 1. if I had to use the friend userlist on multiple occasions on the same page?

(select from groups, select from bulletins, select from items, select from users)

How To Design Database
I want to build a database for menu driven.
like
file
--new
--- html
--- php
--open
--- html
--- php
--save
edit
--cut
--copy
--paste
and like that.

So,please help me how to start with designing the database as it can have n no. of levels.

DB Design Question...
I am almost done building a LAN-based Point-of-Sale system. I am somewhat new to mySQL, although I have worked with it for a few years now. I've only used mySQL for simple things like storing images and contact info for people that use my web-based contact forms. Now I want to do something a little more in-depth, and I need some suggestions.

I want to be able to not only insert each invoice into the DB, but I also want to do so in a way that would allow me to provide various reports in a logical wy. As it stands now, I have 1 table that I call "invoices", where I store all the info that is found on a completed invoice: customer's information, date, sale type, and sale details, which is a semi-colon delimited list of all the items, quantities, and prices for everything they bought.

Now, after building that table and playing with it a little, it seems rather cumbersome to work with. I will have to do a lot of parsing in order to make reports like 10 Most Popular Items, Total Profit for the last week, Total Item Cost of all tickets for month of Nov, for example, since all of those details are in a list in one column.

How would you guys build your table/tables to make such data storage and it's subsequent access more logical with less overhead? A seperate customers table linked with an items table? Or would you do something different?

Database Design
I need the perfect mysql_fetch_assoc function for my db class file, i wan't to use it on the following situation:

I want to select and list some fields from a table in the mysql database.

I want to list them by fieldname and not by number result.

I mean not $result['number'] , i want $result['fieldname']

Here is a little example:
=================================================================
$result = $db->execute("select id,name,text from categories where catid=$catid;");
echo ($result[1]!="") ? "<tr><td> Name: </td><td>{$result[1]}</td></tr>" : "";
=================================================================
I want to list the result by fieldname. Maybe so:

echo ($result['name']!="") ? ............................

Design / Primary Key
The situation is that I currently have a excel sheet that is used to store schedule information for three trainers in the following format

DATE | Trainer 1 | Trainer 2 | Trainer 3|
12/1/07 | Mackens | In office | On Course|

My manager wants to place this info into a MySQL DB so that we can develop a php web site to manage the schedule and allow the trainers to view schedule info etc when offsite. I've designed most of the tables whose primary keys are all forigen keys of the schedule table but would like some advice on developing the schedule table in relation to its primary key.

I know that I can't use the date alone as a primary key so I was thinking of using both the Trainer_ID and Day(date) as the primary key as this should allow info for all trainers to be entered for the same date and be flexible enough to allow for growth in the number of trainers without having to edit any external php code etc.

However I was just wondering if I should continue with this method or could someone suggest a better method as I've missed something that an experienced DB admin/developer would spot.

Database Design
I have database tables for folders and a database with documents in it.

The folders table is setup like this

folderid
foldername
folderdescription

The document table is setup like this

documentid
documentname
documentdescription
folderid

I have a user table and want to set it so I can say certain users have permission on certain folder but not others.

I was thinking about adding a group table and putting the users in groups, but then certain users may need access to more than one document folder. I am thinking I will need to add some sort of table that handles just the permissions, but I am not sure.

I am just trying to sort out how to make it so I can put groups in and assign access to folders based on the groups.

Database Design
I usage of mySQL will revolve around a common group + user system. There
can be multiple groups and each group will contain some subset of users.

Each group will have a custom set of data whose values vary per user.

So basically a sample structure might look like this:
(some details intentionally left out)

Database

Table_Group_A
# of user columns

Table_Group_A_UserX
Column 1 Data
Column 2 Data
...
Column N Data

One probably incorrect thought on my part is that it would not be
necessary to store the usernames in Table_Group_A of those users who
belong to that group. But, thinking about it more, it seems like a good
idea. My original intent was to simply look for tables named
Table_Group_A_* and extract the username from the table name...

Does anyone have any recommends concerning this kind of design? I would
like to be able to lay things out in mySQL as cleanly as possible.

Database Design
I've read Ben Fortas book MySQL Crash Course which is a usefull introduction. However, I need to create a database that includes product descriptions, shopping card, stock levels, customer details, order records etc. While the Crash Course book gives some very useful information, I didn't really feel it gave me sufficient information for designing a database. Can anyone recommend any books that cover this aspect in more detail.

I do have the MySQL manual download pdf which I haven't read, but I'm assuming that this wont provide the sort of guidance I need. Presumably a book that also covers topics such as database maintenance/backup etc will be very useful too.

Database Design
I currently have 2 databases. The first one is used to store each user's info. This database has 3 tables. One that stores the login and password, another that stores their preferences, and another that stores their information (email, phone number, name, ...). In each table I use the login (char(25)) as the primary key.

The other database is used to store the posts and replies. I use 2 tables. One stores all of the information for each post and the other stores all of the information for the replies. I currently do not have a primary key for these tables.

First of all, I have tried to break up the User's database into tables based on how much I will be searching for that info.(for example, I have placed only the login and password in a table). Is this a good way to speed up my queries? My database does seem to be normalized with or without splitting the database up into different tables.

Table Design
I have a question about table design, here is the background.

The main table is "Customer" with standard stuff, first/middle/last name etc.. The next table is CustomerType. CustomerType has 16 types and a Customer can have more that one type. So below is what I think is correct design and I just wanted to confirm this is correct:

Table: Customer
CustID INT(11) not null primary key auto_increment
First varchar (15)
Middle varchar (15)
Last varchar (15)
ENGINE=INNODB

Table: CustomerType
CustID INT(11) not null
Type1 enum('Y','N') not null default 'N'
Type2 enum('Y','N') not null default 'N'
Type3 enum('Y','N') not null default 'N'
Type4 enum('Y','N') not null default 'N'
FOREIGN KEY(CustID) REFERENCES Customer(CustID)
ENGINE=INNODB

Is there a better way?

Relational DB Design
I've contructed a fairly simple normalized (3NF), relational database. By simple, I mean each table relates to other table(s) in a 1 to many (1,n) relationship. Now, I am trying to extend this database, but I'm not sure how to proceed given specific qualifiers. Allow me to illustrate:

* 1 Pod has 4 Channels > (1,4) relationship.

* 1 Sensor may occupy 0, 1 or 2 Channels > (0|1|2) relationship.

* 1 Pod has between 0 and 4 Sensors > Indirect relationship between Pod and Sensors. Direct relationship is between Channels and Sensors.

* 1 Sensor connects to 0 or more Pods, but only 1 Pod at any given time. Indirect Many-to-many relationship between Pod and Sensor. Again, the direct relationship is between Channels and Sensors.

I've drawn up a db diagram and attached. PodSernsorAssociation allows a many to many relationship between Pod and Sensor, although it may not be correct to even have this relationship.

Table Design
I need have a table that will have a column that will have a numeric value. In some instances the numeric value will be a percent and in some it'll be a dollar value.

My questions are:

1. What is the best datatype to use? Currently I have it setup as a Decimal(10,2) datatype.

2. Guessing the datatype above is suitable, how can I tell if the value is a percent or a dollar amount?

3. There are some instances where, if the value is a percent, that it needs to be identified as < xx%. Using the Decimal dt, how could I record the "<" that needs to accompany it?

Database Design
What I've got is a database with different
category's and products below them. The thing I'm having trouble with is
the products all have different sizes and colors, and each (different)
product has it's own id and quantity. My first idea was to create one
huge table and deal with it. Is there a better way to do this? Is there
some resources on database design which would point me in the right
direction?

Database Design
I want to design a database that will have 4 main elements:

1. A person database - of individuals and their qualifications

2. An airplane database - of individual airplanes and their attributes

3. A flight database - where 'a person above' flies 'an airplane' with details of the flight

4. A maintenance database - containing descriptions of maintenance items done by 'a person' to 'a plane'

The outputs from this database would be several different log book types, pilots log, engine log, airframe log, propeller log, etc.

I just need a little guidance. I can see where I could just have one single large maintenance table where entries are ID'd with a plane ID and a person ID. But is that the right approach? Should the maintenance log be specific to a certain plane?

The same holds true for the flights. Just one big flights log with entries ID'd bt pilot & plane or each pilot have his own separate flight log???

Design Issue
I've tried a couple of times to design a database for some web programs I'm working on. I keep running into this issue: What if I have a table, and I want one of the data types to be a list. It seems like a table within a table is what I want. The other way I thought would be to create a seperate global table and have a variable that links to the ID of the able I want to contain the list (if that makes any sense). Here's an example:

Character
ID
Experience
Level
Strength

ItemInventory
ID
CharacterID
ItemID

But for some reason that seems really messy to me. Is that the way it's supposed to be done?

DB Design Users
If you have a database with several table and u want users to access the information. What is the available methods to allow these users to access information ( READING and ADDING at least).

Do I add users like the root. Or creating table which hold users informations in which case I wonder how these users access data without mysql user???

Database Design
I have make an lyric databse, the structure is like that

table: artist
artist_id (int)
name (varchar)
gender (varchar)
region (varchar)

table: lyric
lyric_id (int)
artist_id (index)
song_title (varchar)
lyric (text)

these 2 tables only for lyric for single artist. As you music lovers know, sometime artists sing some songs with other artists. Do you know how should I design another table to store those lyrics?

Database Design Tip?
Im designing a database and find that I seem to have a few small tables with say, 3 or 4 fields, where all of those fields are required to identify a record. Is it better to make a composite primary key consisting of all the fields, or introduce a non-composite primary key, like an auto_increment integer?

How To Design The Table?
i have a situation where a:

Table : member
One of the table field : hobby

And my member table need transaction and at the same time, i need a FULLTEXT search to be perform in hobby table

I know FULLTEXT support by MYISAM and Transaction supported by InnoDB

So how can i do that?

Database Design
I have my design like this. I have one table for the protuct that has a
collum for product type. Product type is another table that has links to
other tables like motherboards, hard drives etc. I hope you understand this.
Is that the easyest, most efficent way to do it?

Design Advice
I am looking at designing a mini database to handle subsets of data at my company. You probably know the drill--it's too much to handle with a spreadsheet, but not enough to warrant hassling the DBAs with loading it into our main servers.

So I am looking at managing the data via a local instance of MySQL. The largest file I need to manage at the moment is about 100 columns x 165k rows.

Obviously, I wouldn't build table of that size. That's the whole point of relational DBs.

But here's the deal, at my company, once someone finds a way to do something, it tends to stick. So I'm looking at solving a few temporary problems by managing subsets of data from our main DB.

I have this suspicion that once I am able to do this, the demands will grow, and all the sudden, I'll get stuck managing a whole bunch a data for a bunch of people.

So I want to build the DB right. I've never built a DB before. I just query the main DB.

Without getting into proprietary details, I can say that I am looking at unique IDs that correspond to clients--about 165k, plus all relevant associated data.

What I am tracking is which clients have performed what jobs for us, when, and what the result was.

I need to be able to run reports on this, and I need to add data at the end of each week.

Given all of the above, what general advice do you have? Are there any huge pitfalls to avoid? Any thing that I absolutely should do? Keep in mind that for now, this is just going to be run on a Core2Duo Desktop with 2 gigs of ram, so anything I can do design-wise to speed things along will be useful.

Luckily, I am not in a time crunch. But thanks in advance for whatever advice you are able to come up with.

Database Design Qn
I have a user_account table consisting of an auto id, email address and
password fields.

The id is made the primary key. Is that a good idea or should i just
discard the id and use email address instead?

If I were to have the email address as a foreign key in another table,
will the space consumed bigger that if i were to just use the id?

Which criteria should I give more priority to in deciding which way to go?

Design Question
I'm a somewhat new user of MySQL and I'm looking to use it in a business I am starting. I'm getting ready to start the planning phase for the databases that I'll need but I have a question that I have been unsuccessful in finding an answer to and I hope you can help.

My business will involve selling data files that will be accessible online. I will have a storefront online that will allow users to shop for the data on their own and once they check out, will be able to download the files. Since I'm fairly new to this, I don't know how to bring it all together. Should a database of this nature just store links to the physical location in the filesystem where the data can be found or do people usually store the actual files within the database itself? I hope I've explained this adequately but if I haven't please let me know and I'll try to clarify it better.

New Database Design
I am creating a database of used books for my website and am new to Mysql. My question is this:

should I use the normal form design, or use one table?

I will probably not exceed more than 5,000 books (not in the near future at least. By the time I exceeded 5,000, I would probably have a new site).
I will be updating it almost every day.
I want to query the database using php, with maybe 4 or 5 optional criteria (i.e. author, title, edition, publisher, etc..). With the option of having all those criteria being used in one query.
I would like to also store images for maybe 500-1000 books.

Knowing these things, and the fact that I am a beginner (and normal form querying and altering does seem quite difficult!) can someone advise me as to how I should design my database?

Database Design
I am implementing a web site which will have to support multiple pages of articles (you've all seen "page 1 of 10" on many sites). Since each page can be fairly large and if someone requests page 5, for example, I don't want to load the whole article. I'm visualizing article as a linked list where each page is a node and 'next' pointer tells me which row in 'pages' table to select when a user clicks on "next" page.

Database Design
I need a database which tracks individual users progress in accomplishing 9,100 identical tasks. How can I structure table(s) to accomodate thousands or millions of users. One table with a record for each user would have 9,100 fields which I believe is beyond the limit of some if not all MySql table types. Not to mention it would make one extremely large table. On the other hand, if I go with individual tables for each user that would mean perhaps a million tables. It seems that having thousands of tables open at one time would adversely effect performance.