MYSQL :: Design Of Database Servers

Two MySql Servers, One Database
I have two MySql servers, one in my local PC and one as a testing server in the next room. Using DW 2004 I can use the "put" and "get" to get my PHP files from one PC to the next quicky. How can i do that with the two MySql servers? So if I change the Structure of a table or and data to the Mysql database on my local PC, it will send the changes to the testing server.

Sharing A Database Between Two Servers
I have 2 harddrives, one with linux and windows, and the other holds all my php and sql databases. I set up the databases with windows and everything is fine. Now I want to be able to just have the linux version of mysql look at that same database on the second harddrive and work with the database. The error I get is "Can't open file:

'**.ibd' (errno: 1): "** being any of my tables. Is it possible to configure them to work together in anyway? I dont want to manually have to dump the database and then reload it every time i switch between the OSs... any suggestions?

Charset Problems Copying Database Between Servers
I am trying to copy a database from one web host to another using the export/import functions in phpmyadmin.

The MySQL connection collation for both servers is utf8_unicode_ci.
The database collation for both databases is latin1_swedish_ci.

One of the problematic fields is "page_title" in the table "wikipage".
The collation of the field is latin1_bin but the data stored is in utf-8 (that is no problem).
In one row in the original database, the page_title field contains the following value: Firefox/GrÃƒÂ¥_kasse
In the new database, the same value is: Firefox/GrÃƒÆ’Ã‚Â¥_kasse

As you see, the value is changed during copy.

I use phpMyAdmin - 2.9.1.1 for both export and import. The charset used for the exported/imported file is utf-8.
I copy from a server running MySQL 5.0.24a to a server running MySQL 4.1.21-log

My guess is, that the problem is during import, not export. That's because in the exported file, the string mentioned above looks like this: Firefox/GrÃƒÂ¥_kasse

What am I doing wrong?

Comparison Of Open Source Database Servers
We need to compare the open souce Databases- MySQL and PostgerSQL based on features as well as on performance.

If anyone has done such a comparison, please share your knowledge with us.

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.

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']!="") ? ............................

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.

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???

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?

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?

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?

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.

Database Design
I am designing a database for a dating website that needs to be able to hold hundreds of thousands of records and still be searchable.

What is the best way to go about this.

Should i just define indexes for all of the fields i need to search by or are seperate databases the way to go?

Database Design
is there any database design tool for mysql in which we just design database and it generates database scripts.

Database Design
A complex network configuration will be modeled into a database: nodes, ports, paths, links,We suppose that the model exists and the database is created
Now the issue is: each user should be possible to create it's own network configuration, store it into database, and work with it.
So a user can have 10 networks configured. The same for each user.
Usually a user works with his own configurations. He doesn't need the configurations of other users.However it should be a restriction that a user can not see other user configurations.
The question is: which is the best way to implement this ?
Ex:
1. each time a user is added, duplicate the database for that user.
2. keep everything in a database but add a column user in most of the tables.

Database Design
I'm try to design a database where students can take class, and teachers teach classes. The tables are: users, enroll ( to connect users table and class table), class, course, and roles (to determine the privilege of the user whether is a student or teacher).

Because of the security reason, I would like to seperate the Roles from within the Users table, but then I run into trouble when designing the "roles" table.

Users table: User_ID (PK), User_Name, etc...
Roles table: User_ID (PK), Roles_Teacher, Role_Student

1st question: How can I link these two tables together?
2nd question: Is it better to seperate students into Students table and teachers into Teachers table, or just put all of them into Users table to simplify the design?

Database Design
I would like to create a ticketing system using MySQL. Since I have never done any database design/data modeling I am looking for someone to point me in the right direction in terms of reference materials, examples or anything else you feel will help me. I will attempt to outline what I am trying to accomplish below.

data to be stored:
1. customer data
2. employee data
3. vendor data
4. contact data
5. parts data
6. scheduling data
7. work order data
8. parts order data

The thing I can't seem to get my head around is how to create and store lists (e.g.. customer contacts, vendor contacts, employee contacts, work order contacts, etc... Also how to create and store a list of parts for a parts order.) while maintaining database normalization.

Database Design
I've been looking over all the database design threads and they all seem to say 1 database one table for everything. It seems like that would decrease the search time for my program if it has to look through everything.
What I was thinking was 1 db and 2 tables. One for products the other for reviews. I'm anticipating about 10,000 products and any number of reviews. Combining these two would over bloat my products table - I don't see anything wrong with seperating the tables. Perhaps I'm wrong. I'm a total SQL newb but I'm trying to get everything right as possible.

Database Design
working on building a database to hold all the information for "new world of darkness" characters.I'm building it with mysql, FYI.I'm not sure on some things.
one is how to handle merits each character can have an unlimited number of merits, each merit is ranked 1 to 5 dots. and some merits can have additional... 'strings'.
the main table will probably be just the character table including names, supernatural 'race', faction, etc.I'm going to give a table for merits including merit name, merit_id , book it came from, page number in book.
should I have a table like this?

character_merits
char_id
merit_id
dots
extra_info

or is there a better way to do this?
also if I separate merits from the main table, should I include attributes? or should I put attributes in there own table? each attribute can only have dot's.

Database Design
I am trying to create a MySQL database. I have my info in Paradox databases. The databases are builders production records. Some are separate databases per year (neoplan1981, neoplan1982, etc). Which would be the best way to do this in MySQL? One database for all of the builders (neoplan, mci, etc), with each in a separate table, then the years in a separate table, or create the tables as I already have them in Paradox?
My goal is to have an input field on the web page. Then having the returned data be a combination of the buildres databases. For example, I would like to see all records for octa (octa having 1 record in neoplan1981 and 2 records in mci1984), so the resuls would show all 3.

Database Design
Hope you can help a little I am hoping to develop a website which will be for a charity looking at bringing carers and people who need help together. I have developed the site and I am now looking at developing the MySql database.I am slightly unsure how to design it.On the one side there will be people who need help, this table will contain there id, first name, last name, address, email, disabilities. And on the other side will be people or organisations that can help this table will contain there id, name, address, email, disabilities that they have experience in.I can establish that the disability is common between them, but I am unsure of the whole Normalization process i.e First Normal Form, Second Normal Form and Third Normal Form.

Database Design
I have a simple site where many items are listed and each item can be rated 1 through 10. The obvious database design would be to have one table with items (ItemID, Name, ...) and another for ratings (ItemID, UserID, Rating), but will I run into problems later down the road with this design?
Also, since I want to easily be able to retrieve the overall rating of any item, should I store that information separately in the items table? Or is it not computational expensive to calculate the average over a table whenever I need?

Database Design Questions
I have 2 questions:
1st How many records can you store in the mysql table and database before you slow it down?(Im not referring about the web server storage)

2nd Having a table of people(contact info) data, is it bad database design to create 50 tables(one for each state) in one database to hold the data. If I can I think it would be fast for the searching of peopleâ€™s info. You can search one state instead of all 50(if you known what state you want).

Database Design Question
I am creating a script that will display a table that has the days of the week in the top header and the restaurant name in the first column for every row. Each restaurant will have an entry in the backend for the day of the week and it's hours.

What would be the best way to go about designing the database. How is this?

restaurants
-->id
-->name
-->phone
-->url
-->hours_sun
-->hours_mon
-->hours_tue
-->hours_wed
-->hours_thu
-->hours_fri
-->hours_sat

Database Design Tool
is there any software to draw a database design with his relationship for mysql?

Database Design Issue
i am working on a site redesign that include 25 articles in distinct html files. The plan is to incorporate into the new design an "article of the month" feature where the monthly showcased article is taken from the existing articles. Is it possible to create a database that points to the html files instead of entering the content of each article into the database? Or is there a better way of achieving my goal? Your help is appreciated!

Database Design: Keep One Table Or Add More?
This is perhaps due to bad planning but regardless its now an issue.

I plan to have different sections on my site, each with a general article feel but displayed differently and whatnot.

I decided using a simple `article` table with things such as title, short description, body, author, and so on.

The problem is now I realize each section might need a bit more data than other sections.

The question is: Do I make these seperate tables or just add fields onto my existing article table to handle these edge cases.

There are a few issues with seperate tables. One being my comment system will no longer work since it assumes everything is an article.

Using one table with extra fields would solve this problem but then some of the columns will be null for each row.

I don't think that is such a bad thing, especially since I'm not going to have too many rows in the first place.

What do you guys suggest? I'm leaning towards adding more columsn to my existing database.

Database Design Question
I need to set up a database and am a bit confused as to how to set up the tables, columns etc. Can someone tell me what would be the best way to go about setting this up? (two tables?) There will be multiple groups and each will contain the following:

Group A
Location: Somewhere
Leaders: John, Sally, Jack
Members: Joe, Bob, Bill, Frank, Don, Jim, Sandy, etc.

How's this:

groups (table)
id, name, location, leaders, members

people (table)
id, name, email

Simple Database Design Qs
just a simple database design question...

I have an employees table where I store general info (firstName, lastName, address, etc)...

Would it be wise to store usernames, passwords and access levels there as well or should I create a separate table for that?

I have not seen any reason why to create another table but I googled my dilema and I found some tutorials where they kept login info in a separate table.

Database Design Question
(INT category,INT reseller,INT product,TEXT desc)

Then I will have a page for each 'category' showing what's available. The question is: should I make a seperate table for each 'category' or just use one huge table ? There will be about 100,000 rows in total.

Speed is not really an issue because it won't be real-time.

Also, from a programmer's standpoint (me) it seems better to use integers, but it would be easier to use names for the categories; for example: apples, oranges, pears instead of 1,2,3. Maybe MySQL does that internally ? So if I have (say) 5,000 rows of 'apple' it doesn't actually store that string 5,000 times but instead stores an integer somehow cross-linked (internally) to the string; or maybe I am way off and it doesn't work like that at all.

Stock Database Design
i'm looking to make a "simple" stock database for a growing list of stocks (say adding maybe 20 or so each day).

the thing is, i'm not sure how to design this database considering the potential size of it in the future.

should i make a table with 'stock symbol,' 'date,' and 'stock price' columns and then add new rows for each new day and stock symbol? (so i'd have multiple stock symbols and a ton of repeating dates in the date fields)

or, should i make a table with 'stock symbol' and make every column a date? so each row would have 'stock' 'date' 'date+1' 'date+2'... and the prices would be under each date. the only thing is i think making new columns would be a huge pain compared to just adding rows. i think.

Database Design Question
I have a question about creating a database.

Background:
I have been keeping server information in flat files; one server per file. This flat files looks something like :

date1, server metric 1, server metric 2
date2, server metric 1, server metric 2

I would like to create a table in a database. My first thought was to do something like:
server1, date1, server metric 1, server metric 2
server1, date2, server metric 1, server metric 2
...
server2, date1, server metric 1, server metric 2
server2, date2, server metric 1, server metric 2

There is too much redundant data; storing the server name for each entry.

The next thought would be to use:

date1, server1 metric1, server1 metric 2, server2 metric 1, server2 metric 2
date2, server1 metric1, server1 metric 2, server2 metric 1, server2 metric 2

but then there are dates where server1 may not have data (is this a problem? can there be a null field).

I was thinking of using the following command to create the table:
CREATE TABLE `server_info` (

`date` date NOT NULL,

`s1_m1` varchar(50) default NULL,

`s1_m2` varchar(50) default NULL,

`s2_m1` varchar(50) default NULL,

`s2_m2` varchar(50) default NULL,

PRIMARY KEY (`date`)

) ;

This is nice and compact but the server information is based on the position within the table (field name). Which way is better? Is there another way?

Simple Database Design
Question is - I have a table of records that represent people, and a table of records that describe roles/responsbilities/etc. I need to link people to multiple records in the second table, how do I do this? I'm guessing I can't do it with just one field, do I need another table or something?

Database Design Opinions
I have been asked to build a site for a client and I am not sure of a good way to design the database. Its an online site to buy tickets to various venues.

I wonder if I should have a table for each venue, that records customer info and order info, or should I create one table for customer info and one table for order info that cross refrences.

So two tables, or tons of tables.

Any idea? I know that I havn't given alot of info, but I hope I have given anough to get some ideas.

Database Design For Dummie
I'm a total idiot when it comes to mysql. I have to design a database that will get queries from a Borland c++ builder program. My questions is:

Firstly how 2 create a database.... that sounds stupid, but I can't get it working!
Secondly how to use it and access it on a remote server.

Basic Database Design
I have a table called 'nms_apps' which
stores information about all of our applications which
we have developed/maintained for our client. One
column which I would like to use is called 'used_by',
which would store information about which business
sections (Financial Management Branch, Human Resources
Branch, etc.) use a particular application. Often
times more than one section use a particular
application. My question is this:

How do you handle multiple entries in one column? Is
it acceptable to more than one value in a column for
one row? If not, what is the best way to design that
in the table? Have multiple 'used_by' columns?
'used_by_1', 'used_by_2', 'used_by_3', etc.? .

Basic Database Design
DB novice checking in here with a basic design
question. I have a table called 'nms_apps' which
stores information about all of our applications which
we have developed/maintained for our client. One
column which I would like to use is called 'used_by',
which would store information about which business
sections (Financial Management Branch, Human Resources
Branch, etc.) use a particular application. Often
times more than one section use a particular
application. My question is this:

How do you handle multiple entries in one column? Is
it acceptable to more than one value in a column for
one row? If not, what is the best way to design that
in the table? Have multiple 'used_by' columns?
'used_by_1', 'used_by_2', 'used_by_3', etc.? .

Web Design Needing A Database
I have made a website called www.freepspwallpapers.co.uk At the moment i am listing images on here which are simply added in HTML, however, i want to be able to put all my images into a database and pages to be generated?

Is myXQL the right program/software/database? If so, what is the best way to get started? Do i need any special tools or programs?

If i were also to ask about bells and whistles for my site, i want a feature that makes the people visiting have to register before being able to access the pages on the site (example - www.joke-central.net) and also for the site to measure downloads.

MysqlImport And Database Design
If i am importing Like this.
mysqlimport --local --ignore --lines-terminated-by="
" --columns=ITEM,ORDER,WEIGHT My_Database MyTabledata.txt

I need to import records where I will have ITEM the same in more then one record.. but they will not have the same ORDER..... How would i have to set my columns key properites? I thought about setting it to unique but, that won't work becuase We use the same item on many orders I need a way to make the ORDER AND ITEM UNIQUE TOgether?
if that makes any sense.

For example my txt file looks like this
ITEM ORDER WEIGHT
22 7777 500lb
33 9999 777lb
22 7878 555lb

how do i make it import all 3 of these records without droping the 3rd one becuase they both have a 22 in item.

Database Design Resources
During my latest project I am creating an online application, this application is half-way done and has a daunting 425 different fields, some of the fields are arrays so that number may be a bit higher. All of the sudden I am trembling in my boots at the thought of storing this in one freaking huge table, so I need what I think is called database abstraction, or relationships maybe.

I am creating the form in HTML_QuickForm_Controller so it's been pretty easy to keep it together on the application end, but storing this data is awfully scary, and all the tutorials I have seen haven't been helpful in seeing the solution I know is right in front of me. I don't even know what information to surrender to get adequate help in this.

Database Design Tool
Is there a good (free) database design tool for windows and MySQL? I have been using DBDesigner but it seems to ignore foreign key constraints when I generate an SQL create script.

Database Schema Design
Each article/faq item can be associated with some topic (and both articles and faq items have nothing to do with each other)
Right now i have 2 separate topics tables for articles and for faq items but these tables have exactly the same schema(same columns)
The alternative is to use only one topics table and have additional column used to figure out if it is article's topic or other's.