Some Advice For A Newbie On Big Scale Websites Like Ebay, Facebook, Youtube
I am literally newbie in database design. Just finished kevin yank's build your own database driven website using php and mysql. The book helped me to understand the imporatance of deisgn in a database driven site but I still finding it difficult to figure out the large scale websites like ebay, facebook or youtube. Does anyone know how the database might be designed for these sites. Facebook and youtube are tagbased sites where everything is connected by tags. but how are these tags organized in tables?
View Complete Forum Thread with Replies
Related Forum Messages:
When Should I Scale Out From One LAMP Machine?
I just have one server that runs Apache, MySQL, PHP. It handles requests fine and never had problems. When should I think about a small step into scaling? By maybe having an independent MySQL server? How many QPS (Querys Per second) (approx) on the MySQL DB will it be optimal for me to have MySQL on a seperate maybe more powerful machine? The current server has 1GB Ram and intel duo core processors..
View Replies !
Search Engine For Videos Like Youtube
I have made one database table with embeded videos: Fields: Title, Description, Image(img src=thum from Youtube), Video ( embed from Youtube). Also I made one search script where it search trought Title with LIKE and outputs only: Title and Image as result. Now, when visitor on my page click on Image the script need to display the rest of the database: Description and Embed Video. So, my problem is in that I don't know how to link the Title to the rest of the content from that row in my database table.
View Replies !
Using One Database By Two Websites
I am with 1and1 and am trying to put together three small websites. Obe for my local church, mine, and a friends. At present I have access to two databases but I would like to share one as the cost is prohibitive to add another database. Is there a way in [for example] .htaccess or by putting in a "directory" that could sort this out.
View Replies !
2 Websites Using 1 Database
i have two logins systems(for 2 websites) and i use the same mysql database. still now when the users login at the first site and link to the other,they have to login again to access it.how can i manage this so when someone login at the 1/2 sites,they apear online at the other without needing to login again? note that: *i use the same database for these two sites *i use one url and users switch between these two sites using internal button link. *both sites r in the same server.
View Replies !
1 Database For 2 Websites?
While I have been doing simple HTML and CSS websites for some time I just recently started out with PHP and MySQL. The question I have is I have an existing website which runs off a MySQL database and am in the process of creating a second site which is the EXACT same type of website (myspace resouce) and will be using the exact same scripts on both sites so rather than having to create a second database and updating two seperates sites all the time if I can run them both off the same database so that by updating one database both sites will update with the new content. Also the only things that are run off of the database is non search engine friendly content such as myspace layouts, graphics, and other types of images but the pages text content for each site is unique.
View Replies !
Access A Database From Different Websites
Is it possible to access a MySQL database from a domain other than the domain that the database is phtsically on, and if so, how? For example, if my database is located on www.site1.com, and I want to access it from a script on www.site2.com, how do I do it? For arguments sake, assume both websites are physically located on the same server, and they share an IP address of 123.123.123.001.
View Replies !
Two Different Websites Connected To One Database But Only One Works...
I have been recently having problems transferring my website from one domain name to another. The first domain name was used whilst at uni, but the second is my own perosnal website. One the original website, the MySQL database allows the user to login and register new users without any problems. However, the second website does not allow me to do this. I would consider myself a newbie when its comes to MySQL so I was wondering if the community could help me out on this one. Both websites are hosted by the same company, and both websites use the same database but my limited knowledge cannot solve this problem. Are there certain permissions I need to set or something? Can anyone help me out on this one?
View Replies !
Wall To Wall Comments (like Facebook)
For testing I tried it for 2 users whos profile comments are both in the same table using this query: Code: Code: SELECT u.user_id, u.username, c.comment_id, c.user_id, c.friend_id, c.time, c.message FROM users u, user_comments_a c WHERE ( c.user_id =1 AND u.user_id = c.friend_id AND c.friend_id =2 ) OR ( c.user_id =2 AND u.user_id = c.friend_id AND c.friend_id =1 ) ORDER BY time DESC LIMIT 20 Whilst that works fine in that situation with both users sharing the same comments table, how could it be done if the comments are stored in 2 different tables? Such as * user_comments_a (fields: comment_id, user_id, friend_id, time, message) * user_comments_b (fields: comment_id, user_id, friend_id, time, message)
View Replies !
Is Query Possible? (Newbie)
I have 2 tables used for an online calendar... first table fields: primary_key , start_date, event_name, event_description second table fields: primary_key, end_date Tables fields are shortened and can't be changed. My second table only contains events that have a end date. I want to create a query that will take all the fields in. If no end_date exists then set to NULL. Been playing with it all day. Hoping some advance function exists. I thought of using a temp table but there must be a better way.
View Replies !
Newbie Question
I am trying to install mysql on XP to use with PHP for a web site that I am working on. I have done everything that the books are telling me but I can't seem to get either PHP or Mysql to speak to each other. When I do the tests on frontpage, it doesn't seem to do anything. On the Mysqladmin it tells me that my odbc driver 3,.51 not found is that a problem and how do I get the driver? Also when I go to the database section and right click I don't get the create database how do I get this?
View Replies !
Please Help Newbie Upgrading From 4.1.11 To 5.0.x
I'm almost in tears here, am having a lot of trouble with the MySQL upgrade from 4.1.11 to 5.0.x. I have read Chapter 2 of the manual. I've read it more times than I care to admit. I've done Google searches. I've searched this forum as well as the usenet group. I'm stuck and I need to get this upgrade done. I must be the most stupid human on the face of the planet but I don't think the manual is very clear when it comes to explaining how to do the upgrade. Please take pity on me and help if you can, I'll be EXTREMELY grateful! My environment: Linux RHE 3, the MySQL 4.1.11 is a non-RPM installation. The software is not installed under the default directory, it's in /opt/mysql/tst/4.1.11. I have many databases and many user accounts all password-protected. Here is what I tried: 1. dumped all databases, including mysql. 2. make a backup of the whole 4.1.11 (MySQL softare) directory. Shutdown mysql 4.1.11. 3. can't tell exactly from the manual where the new software is supposed to go (is it supposed to overwrite the existing installation?). So I extracted the mysql-...tar.gz file into the /opt/mysql/tst directory. It created its own directory structure called "mysql-5.0.51a-linux-i686-glibc23". 4. I renamed the "mysql-5.0.51a-linux-i686-glibc23" directory to "5.0". 5. tried to start w/ bin/mysql_safe & and the server started and then stopped immediately. 6. Couldn't figure out what else to do and am running on the assumption that the "upgrade" is really just an "install" so I followed the installation instructions that say to run the mysql_install_db script. 7. Now I can start mysql_safe, hooray. I logged in with root, no password required. 8. I ran the mysql.sql file (the script produced when I dumped mysql out of 4.1.11) assuming this would put all the user accounts & passwords back into the new 5.0 version. 9. Logged into mysql using root and still not required to enter a password. However, am pleased to find the user accounts are in the database. What am I doing wrong? How do I install the new version of MySQL so that the user account information is preserved?
View Replies !
MySQL Newbie
I have started to plan a conversion of my flat file system, to one run by MySQL. 1. I will have a table which in one of its fields, lists a series of items (alphabetical names). Where I want to retrieve only those beginning with A, can the MySQL query do this or should I bring in all the values and then process them with php/perl? 2. where I have a MySQL Db hosted on mydomain.com, can I enable other domains to read from it (to power their sites), without compromising security? (All the other sites would be on my own server).
View Replies !
Newbie In MYSQL
I m a newbie in MYSQL.Would somebody pls tell me the following questions- 1. WHich is my original database after creating a Database(e.g. Student) in MySql .I mean in MSAcces DB I can find a .mdb file which is to be used.But in MySql which file to use. (If I create a database named- Student).Would I have to use the whole SQL folder including all files for a single DB. 2. I want to use that DB in MYSQL with JAVA. Which should be the driver String for MySQL. I am new pls help me. Edit/Delete Message
View Replies !
Connecting To Database For Newbie
Ive only started using MySQL this week and fraid im getting stuck on the basics! Im using the mysql server from a localhost and having problems connecting to the database. (im using a php programme for the mysql queries) I can connect to mysql itself if i use the default settings: mysql_connect("localhost") But when i try to connect to my database i keep getting the same error messages: "Access denied for user: '@localhost' (Using password: NO)" Ive tried creating a new user in MySQL and granting privileges such as: GRANT ALL PRIVILEGES ON my_database.* TO user@'%' IDENTIFIED BY 'passw'; When i change the connect settings to: mysql_connect("localhost", "user", "passw") then i dont connect to mysql, but get an error message saying: "MySQL connection failed: Access denied for user: 'user@localhost' (Using password: YES)"
View Replies !
Newbie: Creating Users?
I'm tring to create new users. I've read the docs and still kee recieving the error: mysql> CREATE USER joeuser IDENTIFIED BY PASSWORD somepass; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'USER joeuser IDENTIFIED BY PASSWORD somepass' at line 1 How can I check to see if I have access to create users? I created tables, I don't understand why I can't create users. 13.5.1.1. CREATE USER Syntax CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'] [, user [IDENTIFIED BY [PASSWORD] 'password']] ... The CREATE USER statement creates new MySQL accounts. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE USER creates a new record in the mysql.user table that has no privileges. An error occurs if the account already exists. The account can be given a password with the optional IDENTIFIED BY clause. The user value and the password are given the same way as for the GRANT statement. In particular, to specify the password in plain text, omit the PASSWORD keyword. To specify the password as the hashed value as returned by the PASSWORD() function, include the keyword PASSWORD. See Section 13.5.1.3, “GRANT and REVOKE Syntax”.
View Replies !
Newbie Can't Upload Dump Pls Help If You Can
This is the error I'm getting. when trying to upload in phpmyadmin. PHP SQL query: -- phpMyAdmin SQL Dump -- version 2.8.0.2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: May 22, 2006 at 12:48 PM -- Server version: 4.1.18 -- PHP Version: 4.4.2 -- -- Database: `askdivas_ex` -- CREATE DATABASE `askdivas_ex` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; MySQL said: Documentation #1044 - Access denied for user 'askdivas'@'localhost' to database 'askdivas_ex'
View Replies !
Connect One Table To Another. Newbie Here....
i´m using phpMyAdmin. Hi have two tables: cars and constructor how can i make so that when inserting values in my cars table i will have lets say a dropdown list with my constructors? Like having a products table and one table just for categories and when inserting a new product i would have the categories list to select...
View Replies !
Newbie: MySQL Locally?
This may be a dumb question but I am a newbie and well, don't know the answer. Is there a way to set-up/test a MySQL database locally on my machine?
View Replies !
Newbie - Cannot Increase Field Size?
First of all my knowledge of mysql databases is about 1%! I am learning by my mistakes thus far but have stumbled onto a bit of a problem. I am trying to fix different things in a pre-made script I was given. I have a field which is a decription field and so will be filled with quite a large amount of text from the user when submitted. The problem is its only accepting "257" characters and really this field should be unlimited! The specs of the field are: varchar(255) Null =No
View Replies !
Newbie: Formating Data In Updates
My POS products table is formatted totally different from that of the Online products table. No problem, I've got FileMaker Pro and it is really good at changing and formating data how I want it. I can output the product records for my website one way, the output the records for Froogle in another format. The problem is that it takes and extremely long time with 70k+ records. My current scenario is outgrowing itself and I need to come up with a better solution. I need to find a quicker solution, possibly one I can automate down the road. Any advice for resources? Or examples?
View Replies !
Newbie With MySQL CXN Connection Problem
So, I am building a members only application and have the MySQL tables and database already set up and now having trouble connecting my application to the database and could use some help. On my line 13, I have a mysqli connect pointing to my local host (i just used the server name and then the username and database name. i hid the password in parenthesis here just for protection. PHP $cxn=mysqli_connect("h41mysql49.secureserver.net","mankatocustomers","","mankatocustomers") I also I have modified my php.ini file to include PHP extension=php_mysqli.dll .. because i saw on another form i should add this. i am using PHP Version 4.3.11 (php info file for me) i also built a members registration and members login php page you can see by clicking members registration. -------------------------- so, when you try registering or logging in (i inserted a new member, myself, using phpmyadmin and tried logging into that members registration section) and I kept getting an error like this: PHP Fatal error: Call to undefined function: mysqli_connect() in /home/content/p/e/s/peschomd/html/login/Login.php on line 14 Does anyone know whats up? I tried using "localhost" and changing the database name multiple times and looking at my two INC files and nothing changed. I always get the same errors. Anyone can help me would be greatly appreciated to this newbie to php and mysql!
View Replies !
A Lil Advice
i'm looking to build a relational database that will hold product reviews and statistics that i will eventually publish on the web where they will be searchable, etc. i'm working on a mac os 10.3.9. is mysql the way to go? what app should i be using to create the database?
View Replies !
Advice
I own a company whereby my 50 employees (soon to be 100) all work from home and access our server via terminal services client. These 50 people work on 40 separate Access databases. Some small, some large (over 100 mb). My IT consultant tells me that I should move to a sql server (which I agree), upgrade to Access 2003, and minimize term server use by moving to a web based input % reporting engine for common employee tasks (due to the load of 100 employees on Access). - Access 2003 is $200 a user (are there cheaper options) - We regularly modify the Access databases. Note that I created the databases but I am not a developer. I an very comfortable in Access (its the only database software I have ever used). So, is MySQL an option for this environment? I know its a big, ugly question with few details, but I am looking at all options.
View Replies !
Optimization Advice
I'm having Database connection problem on my VPS server runing SMF forum script. It often displays "unable to connect to database" notification when there is more than 200 users online (in 15 minute period). I have raised max mysql connection number from 100 to 150, but that didn't help. Connections per second number has doubled in last month (but traffic is only slightly higher) Here are some mysql informations, any advice how to get this numbers to normal values is welcome.
View Replies !
Advice Sought
I'm somewhat new to database design in MySQL from a Designing from the "Ground Up" Point of view. I mean I've used Databases in a limited capacity, but this is my first time developing one from scratch, and mainly its for educational purposes, so I can eventually do this myself, rather than hit hotscripts, or some other repository but will be for a functional website at the same time. I have a library of links I would like to display. My client has presented me with a word doc with the links and a description. I assume I would just create a table with Category,Title,URL,Description as fields, but if I want to present the end user with a dropdown list at the top of the page that allows them to select a category, would I have to create a separate table with just the categories, and link it to the 1st DB?
View Replies !
Best Architecture Advice
Let's say you have 800,000 employees (huge company huh?), 300 sectors, and 3 security levels for each sector. You want to hold the security level for each sector for each employee. I can't figure out the best way to do this. Right now, my company has a table for each sector - each with columns [employee_id, security_level]. This results in 300 different tables and the joining when searching on several sectors gets crazy. I'm trying to figure out an easier way to structure this. If you have one table [employee_id, sector_id, level_id], because of the many-to-many relationship between employee_id and sector_id - that's going to give you a 240,000,000 row table. If you have one table made up of a columns for each sector holding the security level value [employee_id, sector1, sector2, sector3, etc] you have a table with 301 columns, which will put me way out of my index max limit.
View Replies !
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.
View Replies !
Advice Requested
I'm going to be creating a signup form with the standard fields (fname, lname, email, etc), plus user definable fields. Currently, I'm kind of stuck on how to go about doing this. My users table has an id field (PK), email field, IP address field, etc. I want to give the site admins more control over what they can put into their signup forms. Some people may want to have a website field, others may want to have a field for the user's fav book. Whatever. Now, if I put these into the users table, I'm now fixed at how many fields I can have, correct? I mean, I can set a determined number of fields and call them custom1, custom2, etc. and let the admin define their user-readable names, but it's still a fixed amount of fields. I could create a new table with 2 fields - name and value - where name is the PK. That way the admin could enter as many as they want. But how would I then have the same fields in the users table to hold the data?
View Replies !
Newbie Warning: Learning MySQL/Ruby, Priorities?
I've pretty much decided on learning Ruby as my first server side language, and obviously SQL to go with it. Not knowing how the two inter-operate, I'm unclear as to which would be more appropriate to learn first, for someone who is already fairly thorough in Javascript/DOM. I'm concerned that if I get into one, I'll get stuck because I don't know the other, so I'm trying to decide on how to proceed without becoming dependand on a language i am yet to learn during the process. So the question would be, MySQL or Ruby first? Or maybe a book/tutorial that nicely fits the two together from the ground up?
View Replies !
Complicated Join Advice
My tables look something like this: FIGHTER fighter_id first_name last_name age style FIGHTS fight_id fighter1_id (key to FIGHTERS table) fighter2_id (key to FIGHTERS table) event_id (key to EVENT table) winner (this field will contain either 1 or 2 indicating which fighter won) win_round win_time codes... I've been trying to figure out an appropriate query but can't seem to get it. The only solution I've come up with so far is to break it into multiple SELECT statements which seems to defeat the purpose of a relational database.
View Replies !
Table Structure Advice
I have two tables - Users and Groups. Pretty straightforward layout where the Users table has ID, last_name, first_name, etc... The Groups table is simply ID, name, description, etc... I want to include in my Users table a group_ids field. What makes this complex, is one user could be a member of just one or many groups, so I can't simply say User1 is in Group1. User1 may be in Group2, Group17,etc... How does one (or how would you) apply this? I've thought about making the group_ids field a 'text' datatype and separating the group ids by comma or space., but for several reasons this doesn't seem to be the right way to do this. Let me know if I haven't explained this well or if I should give an example of the data for clarification.
View Replies !
Table Design Advice
I'm looking for some advice on suitable ways to construct a table or tables to handle the following situation. I have a dynamic and structured content requirement, where the user can define a new type of item with a new/different set of fields. So I have item types: - name, fields items themselves - one or more field+content pairs depending on the information in item-types. That in itself is pretty straight forward, I can store the item details in a single table of item-id, field-id, value triplets. ie. my db could follow the structure shown at the bottom of the post. However, I need to be able to filter for items based on the values of several fields. In order to do that I can join the item table to itself for each field after the first, e.g. SELECT item-id FROM item-details i1, item-details i2, item-details-i3 WHERE i1.item-id = i2.item-id AND i1.item-id=i3.item-id AND i1.field-id=<field1> and i1.value=<expr1> AND i2.field-id=<field2> and i2.value=<expr1> AND i3.field-id=<field3> AND i3.value=<expr3>; If the item needs to be selected on five fields then there would need to be four joins. Seven ... six, etc, etc. Also, I would like to be able to search the field values without reference to the fields themselves. e.g. Search for all items with a value containing "red". Is this the best way to handle the problem? Is there another table design that would work better? e.g. for each item-type, use the field information to create a table specific to that item-type with columns for each of the fields. Possible structure ============================= item-type - type-id - type-name fields - field-id - field-name - other field data (description, validation, etc) item-fields - type-id - field-id items - item-id - type-id item-details - item-id - field-id - value
View Replies !
Query Optimization Advice?
I am familiar with the VERY basics of MySQL (in other words, I am not an expert...), and I am currently working to create a simple search engine on our website. Users should be able to search a database of over 150,000 sheet music titles by simply inputting a keyword(s) phrase. That phrase should be searched into different tables at the same time but if the keyword phrase includes more than one word, any word need to be searched. For example, let's say an user is looking for "Brahms violin concerto". The search engine should display all the records that matches all those entered keywords. Here are the tables that need to be searched: |title|composer|instruments|description| and here is the query I am trying to work on (after have split the keyword phrase into separate keywords): Quote: SELECT title, composer, instruments, description, price FROM mydatabase WHERE (title REGEXP "(^| )brahms( |$)" OR title REGEXP "(^| )violin( |$)" OR title REGEXP "(^| )concerto( |$)") AND (composer REGEXP "(^| )brahms( |$)" OR composer REGEXP "(^| )violin( |$)" OR composer REGEXP "(^| )concerto( |$)") AND ((instruments REGEXP "(^|;| )brahms(;| |$)" OR instruments REGEXP "(^|;| )violin(;| |$)" OR instruments REGEXP "(^|;| )concerto(;| |$)") OR (description REGEXP "(^| )brahms( |$)" OR description REGEXP "(^| )violin( |$)" OR description REGEXP "(^| )concerto( |$)")) order by title like "%brahms%", title like "%violin%", title like "%concerto%", instruments like "%brahms%", instruments like "%violin%", instruments like "%concerto%" The query works, but it takes too long to be executed... over 10 seconds! I am aware that REGEXP (like "LIKE") don't uses indexes, but I cannot find a different solution to match any possible keyword or part of it. Any suggestion to optimize it? Or should I work on a completely different approach?
View Replies !
Beginners Advice / First Database
I am creating my first database / website and would appreciate advice on how to organize or set up the tables. what I have is user uploaded documents. CREATE TABLE `documents` ( `doc_id` int(10) unsigned NOT NULL auto_increment, `member_name` varchar(255) NOT NULL, `language` varchar(255) NOT NULL, `country` varchar(255) NOT NULL, `pgrating` varchar(50) NOT NULL, `document_name_file` varchar(255) NOT NULL, `type` mediumint(2) default NULL, `document_pathto` varchar(255) NOT NULL, `category` varchar(255) NOT NULL, `keywords` varchar(255) default NULL, `series` varchar(255) NOT NULL, `title` varchar(255) NOT NULL, `date` date default NULL, `votes` mediumint(255) NOT NULL default Ɔ', `price` mediumint(255) NOT NULL, `rating` mediumint(255) NOT NULL default Ɔ', `purchased` mediumint(255) NOT NULL default Ɔ', `leamount` varchar(255) default NULL, `leExpired` mediumint(2) NOT NULL default Ɔ', `userDisabled` tinyint(1) NOT NULL default Ɔ', `lowrateDisabled` mediumint(2) NOT NULL default Ɔ', PRIMARY KEY (`doc_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ; My question is, for searching for these should I make a lot of lookup tables such as? category id | comic id some of the documents can be (changed to) private and not displayed in search results. so I created a table "a_docs" user_name(id) | active doc (id) also the visitor can sort by keywords, ratings, category, country, language, latest and type My question is, should I create a "lookup" table for each of the search criteria and then do something like a join (I honestly don't have a clue what I am talking about yet) ? I also need to generate reports for the members such as how many and what documents were purchased. my guess is a lookup table purchased_docs user_name(id) | purchased doc (id) then once I have the doc (id) for the user I can then lookup the "documents" table to find the price for that document (id). Is this a good approach? I expect to eventually have hundreds of thousands of documents and after this is built I will have someone go over the structure to make sure it is fully optimized. btw I have been reading documentation and in my table the interger values and settings are all wrong. I am good with php and have built dynamic websites with user editable content in flat files but now its time to jump on the mysql wagon.
View Replies !
Advice On Plan Of Attack
OK, I have a large web based questionaire form I am constructing using PHP/MySql. There are around 50 questions with each question having anywhere between 4 and 30 possible answers, depending on the question, that the user can select using checkboxes, meaning they can have multiple checkbox answers per question. My question is what would be the best method to store the checkbox data in MySql, keeping in mind there is also is going to be a search routine that will need to pull the data back out efficiently? Should I | delimit each checkbox answer in a mysql field for each question so there are 50 fields with pipe delimited responses, or do I make a field in the table for each possible answer (around 750 fields)? Or is their a better method??? With so many checkbox answers, what is the best/ most efficient way to store their responses knowing the data will need to be searchable? I can get through the programming side of it, although I am not an expert by any stretch of the imagination. Just looking for advice on how to set it up before I get started. Don't want to do it one way then discover later that the search is too slow because I didn't lay it out right first
View Replies !
Advice On How To Structure Tables
I currently have a dynamic site that displays info about different musicians. My database is currently set up with a table that contains all the infor about each musican such as name, type of music, website address etc. i.e all single bits of information. Now I would like to add a form to allow the musicians to upload one or more mp3 files. It would be fine if it was just one mp3 file as I would simply have a field in the table to store the name of the mp3 file. However I am not sure how to deal with multiple mp3 files. Do I just create say 10 fields in the table for a maximum of 10 mp3 files for each musician? This doesn't seem the mosst sensible idea but I am fairly new to php and mysql so I am not sure what alternatives there are. I would appreciate some advice on this. I was thinking perhaps I could create a separate table to store all the names of all the mp3 files for all musicians but doing this I do not know how I would know which ones are related to each musician. Also related, should I store the mp3 files in separate folders for each musician or just have one mp3 folder on the server and save them all in here?
View Replies !
Advice On Hierarchical Schema
I'm trying to come up with a scema for a hierarchy of shopping categories. Each category can have many children categories and many parents. Ie. exist more than once, and at different levels of the hierarchy. There will be a limit of 5 to the depth of the tree, but some branches will terminate sooner. At the end of the branch will be a link to products within that category. I've read up on hierarchical structures, even read joe celko's book on the subject (I've just recovered from the resulting brain embolism ) but haven't really found solutions that seem to handle the many-many relationships going on, and in the case of the celko book there was a lot that mysql 4 can't do. I'm using version 4.1.20 (stuck on redhat and it doesn't support 5)
View Replies !
Tips / Advice For Searching A Database
I'm building a search facility that'll be the main focus of my website, with the plan being that it'll be the first port of call for people to browse the products, similar to Amazon. Apart from doing a couple of LIKE commands, is there any advice people can offer and maybe some example queries that will return the most useful results to the user? I'll be searching the following fields: product_name image_name product_description
View Replies !
Idea, Advice If Feasible (somewhat Off-topic)
Here is what I am facing. We need to be able to run some pretty intense queries on our existing MySQL database at work. The programmers say it will put too much stress on the servers and can't be done without a major overhaul, and its just a handful of users who need to do it. What I want to do is build some client software that can do the queries. I want to take the data from the MySQL server, build it into a client, and let the end user run as many queries as they want. If they slow down their machine, fine, but they won't slow down the server. Plus most users are sitting on some very powerful hardware, so it should be able to handle it. The issue is my coding skills are rather rusty. So I'm wondering, if I were to take on this project, what would be the best way to tackle it? What languages are best, is there something open source that is already available, etc. The end result has to go into Excel, because thats what my users can operate. Is my idea even feasible or will this be a giant pain? I'm looking at creating a custom application that can sort/calculate/compare a database that is 1,000 to 5,000 rows in length. Its comparing hours/shift lengths for a large company over several departments. The user will not be adding addition data, just analizing what is already there. I can think of a lot of ways I might be able to do this, but I don't know what way would be easiest. Any advice would be helpful, this project is a long time from happening, if at all.
View Replies !
Advice On Creating Categorie ID's
Hi (Sorry the title may be a bit confusing - only realised after submitting - and now cant change it!) Sorry if this is a bit basic but i am a noob at MySQL and therefore may be going about this in the wrong way. The situation I have is that i am trying to get users to record information against categories of work set by different departments, but also allow the user to create custom categories. The numbers of categories created by the Departments may be fairly small (20 - 30 per Dept) and the same can be said for each user - except that the number of users may grow quite large - and therefore the number of user defined categories. I think i need to therefore split this into two tables DeptCategories and UserCategories, but what i need is the user to only have one combined category list to work with when recording data... so therefore need to be able to to create a list of the users DeptCategories and their own UserCategories... The alternative I gues is to have one table with a column(s) that identifies the dept and or the user that entered them... but this then raises the question of how do you list all the available categories for a particular user in the order of DeptCategories and then UserCategories (both in alphabeticcal order) Any suggestion greatly recieved, as I would like to set the DB up correctly at the start so I dont have to alter tables etc.
View Replies !
Advice Needed On Database Schema
I am trying to come up with a proper database schema for this situation that's at least 3 NF. I'm just having trouble with a few of the associations. First, here's the situation. I want to store information about all the cards in a Tarot deck. In the deck, there are two types of cards; namely the major and minor arcana. Both of these have some common information I want to store, and I want to be able to choose just a random card, but they also have some data unique to each, and I also would like to choose a random card of a specific type. For example, both types have an associated element. I will have an elements table probably with simply id and name, and perhaps an element_id in the main cards table. Both also have an associated planet and zodiac sign. Most of the major arcana cards have either a planet or zodiac sign, but not both. Also the association is a little different. With the minor cards, the planet is a specific decanate within the sign. I guess that might not matter too much though in the storage of the data, though again it might if I wanted to retrieve the planets associated with the decanates of a sign. Further, the major arcana have a specific Hebrew letter associated with each card, while the minor cards have no such associated letters. Finally, each of the major arcana cards are classified as elemental, planetary, or zodiacal. The minor cards have no such classification. So, how should I store these data? They are both cards, and have some common data, but both have very different data as well.
View Replies !
Advice On Setting Up Address Database Please
(Before i start i feel obliged to point out my Noobie status so not to shock! ) As part of a larger poject i need to set up an name and address database. Before i start i would very much appreciate some comments on the format of the primarry tables. For example if i set first_name as VARCHAR(15), last_name VARCHAR(30) - this seems ok, but from experience what would people suggest as being realistic for the Address part.. Do i need several fields to allow for each line of the address: MyHome MyStreet MyStreet2 (optional) MyTown MyCounty / State MyPostCode I have found several different stratergies for this such as VARCHAR with no max length.... is this a good idea? Aslo UK post codes - i guess a VARCHAR of max 10 would suffice, but does any one know of an imput mask / regular expression to ensure valid imput?
View Replies !
DB Moving To New Server, Requesting Upgrade Advice
I currently have an older dedicated server with about 10 MySQL 3.23 databases on it. All but one of the DBs are for Wordpress. One is for Invision Power Board. The server is in need of an upgrade because it isn't handling the traffic very well so I've aquired a new, and much more powerful one. The new server has MySQL 4.1.20 on it so right away I'm in trouble because I can't seem to figure out how to get my tables converted. I'm trying to follow the instructions at http://dev.mysql.com/doc/refman/4.1/en/upgrading-to-arch.html, specifically: "You can also store the dump in a file, transfer the file to the target machine, and then load the file into the database there. For example, you can dump a database to a compressed file on the source machine like this: shell> mysqldump --quick db_name | gzip > db_name.gz" But when I try to do this with any of my databases while logged in as ROOT I get the following: "Got error: 1045: Access denied for user 'root@localhost' (using password: NO) when trying to connect." I tried using the help file to figure out how to specify the password but either I couldn't get the syntax correct or it just wasn't working. So, my first request would be for a little guidance here if possible. Assuming that problem is solvable, I have question. If I'm already going to have to import the data into the new 4.x format, would it make more sense to have the MySQL on the new server upgraded to 5.x now - while it's still brand new and before it gets all loaded up? What would be the benefits or drawbacks to doing so? It seems to me that since I have no experience with this that it would make sense to do the upgrade to 5 now so I don't have to go through the pain of another DB upgrade down the road so soon.
View Replies !
Advice On Converting SQL Server 2005 Express To My SQL
So we have a brand new database which is not being used yet. There might be a few testcases of data, but since it's not live yet, anything there is not important. Here is the setup: The SQL Server express database is broken into 3 distinct functional schemas: 1. Main and Worksheet Archive 2. Validation Method Archive 3. Raw Data Backup Each database has a unique table structure. As a result, they can be located in the same owner/schema within the same database. I believe we use a single database. However, because each database can grow at different rates, we do have the option of storing them in separate databases. The tables for each database/schema can be created using scripts provided by the company that created this Lims system. These scripts are for either SQL Server or Oracle database types. Connecting the Lims application to the database uses 'Microsoft OLE DB Provider for SQL Server'. We can also connect to an Oracle Provider, but we are not using Oracle, just SQL Server Express. We are running the SQL Server express database on Windows 2003 R2. The Server itself is a Quad Core Intel Xeon X3210 2.13 with 4 Gigs of Ram. The Lims application we've bought will only run on Windows operating systems. So, after a lengthy introduction to our setup...onto my question. 1. Should we convert the database, or try creating a MySQL database from scratch? 2. I've seen sticky's and posts on converting SQL Server to MySQL...since I know nothing about these databases (but I am willing to learn), do you think I will be able to convert this database? 3. If I can convert it, what is the best method? There are many commercial products I've found...will they work? Or should I use another method like a script to convert the database?
View Replies !
Advice On Table Setup To Maximize Efficiency
My table set is is garbage .. I knew this when I created the table a couple of years ago. Now that I have tonnes of data in it the queries are taking forever. So it's about time I constructed a more efficient table. I am turning to this forum for some advice. My table stores RSS feed content in it -- every 15 minutes a cron job INSERTs data from 300+ feeds and dumps it into the table. The table structure consists of: id dateUnix headline - headline of the article permalink - URL to the article body - TEXT type - body of the article author - person who wrote the article authorURL - URL to the person's website favico sitetype - I categorize the types of sites (0 = news media, 1 = blogs, 2= videos, 3= podcasts) vote - users vote on stories verified - 1 = verified, 0 = censored/banned story newsCat - 20 different categories newsSubcat - 50 different subcategories I think the best way to make this table more efficient would be to break it up into 3-4 different tables using the id as the key. ps: my cron job checks for duplicate entries and does not insert duplicates -- so duplicate stories are not entered
View Replies !
Need Advice On Switching From Sql2000 & Access To Mysql
Im a coldfusion programmer, so it all started for me learning on access db's. Then sql 2000. Along the way, I started installing the cms systems and learning how mysql | phpmyadmin worked and really started to prefer this. The .htaccess control is whats making my decision for me. I just cant control urls enough using IIS & coldfusion. I have found a host that supports coldfusion running on the lamp platform. To convert the sites, I figure 2 major tasks: 1. Converting the databases over to mysql 2. Making sure all my queries function properly. I figure converting wont be too bad, but Im wondering how much structural differences exist between T-sql and the syntax mysql uses. At most, my queries to convert are 3 tables using LEFT OUTER JOIN or INNER JOINS. Any feedback on the process will be most appreciated (I cant wait to get off IIS)
View Replies !
|
TRACKER
