Designing Databases Properly
After what seems like far too much agonizing, I think I've decided to build my own php "shopping cart" and hack my own interface to paypal IPN, because all the other solutions I've come across are massively oversized and overdeveloped for my needs.
But I could use some help in setting up the structure. I'm not sure of the best way to handle "options" with my various products.
First of course I set up a basic database with product fields like:
sku, price, name, etc. etc.
The tricky part is that some products will have special options, some dropdowns and some text fields, and I know I won't be able to predict all these in advance, like:
item color, accessory color, font, initial, initials ...
In addition, some products will call for extra product photos and others won't ...
And then I will need some way of storing all these extra options for a given customer's order when it comes through.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Designing
I am designing a SELECT query that joins a bunch of tables with tons of fields. I need all the fields from some of the tables but I don't need many from others (in fact from some tables I don't need to actually retrieve any columns...I just need those tables to help limit the retrieval on others with WHERE or ON conditions). Is it more efficient to say SELECT * or to instead specify the fields I am trying to select even though that would be a long list? And if it is better to specify the fields I am trying to select, is there a way to tell it that I want to select all (*) from certain tables in the join but I do not want to select all from others? In other words, despite the normal sytax where you say SELECT [specify] FROM A, B, C is it possible to somehow say select * from table A and just a few columns from table B and C?
Designing A DataBase
How do I go about designing a database. I know what all tables I need but I can't get myself to work it to a normalization point. I am working on making a personal CMS, just to hone my skills, and I am stuck with the database. I think its most important to get the database right. My previous experience with a db-based website was awful. Please suggest a way of working a db. What's the first thing I should do when I am building a database? When does normalization really occur, shud it be done from the beginning or does it come after going through the whole DB design?
Designing Database
I am creating a "simple" web application to look up guitar tunings. Each string on a guitar can be tuned to a variety of notes, and although most people are familiar with 6 string guitars, guitars are built with anywhere from 5 to 24+ strings. I want to design a database which lists the artist (join table), song name, the number of strings, and the tuning for each string. Where I am drawing a blank is how to design the database to account for different numbers of strings. I don't think that creating one table with many potentially blank fields is good database design.
Designing A Database
I was trying to design a stock database wherein a user could search a product. I need a large (in fact, huge) list of attributes for each product. I consider two possibilities, but I wonder which is better: A) Create a unique table “products” with all the attributes; it would be a table with 70 fields! B) Create 3 tables: Products: product_id, product_description Properties: property_id, property_description Products_Properties: product_id, property_id ,value The table Product_ Properties would be 70 times bigger than Products. Accordingly, if we have 1 million rows in Products, this means that Product_Properties will have 70 millions!!
Designing Forms
I'm designing a personal website for a school project. For another project (I attend a Project-Based schoo, I visit the YMCA. I'm trying to design a form in FrontPage (I could do it by hand, but I'm too lazy) that will stick the results into a MySQL Database. From there, I want a different page to display those results. If it's not possible, is there another way? I'm running XAMPP on my Dell Latitude D620. (Nobody will really access the site; it's for the experience)
Idea About Designing
I am trying to code a little project for handling the orders at my job to get read of all the paper and i have a little problem normalizing the tables. Any help would be appreciated. What i want is an order form which handles 10 fields for the stuff to buy with quantities and prices for each of them. What i have come to so far is that the supplier would be an other table as would be the plant that the stuff would be bought for. The problem i have is with the 10 stuff. if they are a part of a table with the rest of the order which has a number and a date i should have stuff1, quantity_stuff1, price_stuff1...stuff2, quantity_stuff2 and so forth...that doesn't really stike like a good idea to me. on the other hand i can't figure out a better one
Designing A Secure Database?
I'm working on a website/database deal, and right now I'm designing the security model. It's using LAMP(HP), so all the tricks are going to be through PHP. First off, this will be behind a firewall with SSL encryption. We may also setup some kind of VPN tunneling.I'll be ignoring other security details that don't apply to the problem at hand, but please feel free to suggest. I'm going to have users enter a username and password, with PHP emulating the htaccess dialogue boxes. I would like to check the entered values in a permissions table. This table looks like: username | passwordhash | ipaddress | permission All users will have a static IP, so they have to match the username-password-ipaddress combination. I'm storing the passwords as an MD5 hash in case someone breaks in and reads the table. So what MySQL user does PHP log onto as the database as, in order to read the permissions table? (After that, we just go with the logged user's permission). If I make a special user that just has permission to read the permission table, do I have to store that user's password plaintext in a php script somewhere, thus adding a security risk if someone were to get a hold of that password?
MySQL DB Designing And Programming Tool
Are there any software for MySQL developers that you could recommend? I`ve been using MSSQL and its Server Management Studio makes my life easier. I can easily make diagrams during planning and creating DB structure, programming SP, Functions, Triggers... and many more in one place. I`ve downloaded DB Designer but it seems to be good only for creating a diagrams. Of course I can generate the code for my diagram and then paste it to phpMyAdmin but... I`m looking rather for one (complex) tool which could make my work more efficient. Is there any?
Database Visual Designing Software
guide me to a software that I can use to make visual database design, something where I can make pictures of tables and not table by sql commands. In short I need a tool where I can show my client how am I going to relate and make my 23 table database.
Using Keys Properly
I have a couple questions on how to properly create keys. Basically i know nothing about them, how they work, how it speeds things up, and most importantly, where to create them to help speed up my tables. I do have multiple tables that are used and joined together, i just dont know where to make keys. I heard that if you do it improperly, it can slow down your database too?
UPDATE Will Not Add Properly
I have an online comic engine I'm working on, and I just noticed that, as opposed to adding one view (like it should) it will always add two. Can anyone help me out with this? I've already determined it's just this line of code, and that it's not being called twice. $query = "UPDATE `".SQL_COMICS."` SET comic_views = (comic_views + 1) WHERE comic_id = '".$comic_id."' LIMIT 1"; mysql_query($query) or die(mysql_die_message()); echo($query);
Properly Querying A Relational Db.
I've recently been working with more relational dbs and I was just wondering...am I working with them properly? The reason I ask is because I'm wondering if there are easier/more efficient/more "proper" ways to be working with a relational db. So here is an example: two tables, a project table and an employee table. The project table stores employee id in emp_id and is linked to the employee table by a foreign key (a one to one relation). SELECT P.project_id, E.first_name, E.last_name, P.name, P.code, P.start_date, P.end_date, P.imdb FROM project AS P, employee AS E WHERE E.emp_id = P.supervisor The thing is eventually we'll start to have tables with 4-5 foreign keys and I'm wondering if this is still "proper". Honestly it may seem a stupid question to some of you experts :-p
Getting Utf8 To Work Properly
I have made a few tables in a database, and I'm trying to get my Japanese text to display properly. I have the collation set to utf8_general_ci, and I even added the mysql_query("SET NAMES 'UTF8'"); code into the php page. But even with all of that in, it still won't display properly. The characters show up as セ*チクにんじゃたいけつ and other junk like that. Now this was working before until I added a second mySQL connection into the page, which inputs the second table. I'm not sure if you can make 2 connections on the same page, but I did and it pretty much messed everything up. I took the second table away, and things were still messed up. My question is how can I get the Japanese text to display properly? It does display properly on the phpMyAdmin page, but I get garbage on the actual page.
Search Does Not Match Properly
i had created a search function on a web application. the problem is, i try enter some of info to it, certain text can be found but certain text can be match although they are in database. this my query: $keywordu = strupper($keyword); $keywordl = strlower($keyword); $sql = "SELECT E.* FROM employes AS E, dvi_problem AS DV WHERE AS.empId = DV.empId AND (E.emp_name LIKE '%".$keyWordl."%' OR DV.reference LIKE '".$keywordi."''%".$keywordl."%' OR E.emp_name = '%".$keywordu."%' OR DV.reference LIKE '%".$keywordu."%')" $query = mysql_query($sql, $db) or die('Fail to search.'); i test with direct copy the data from database, some string i copy can search it but some other string i copy direct it can work wo... i try show out the variable $sql value, it show with not error, the keywordu n keywordl aslo show out the value when i print out the $sql variable.
Mysqld Is Not Running Properly
I have installed mysql server & client version 3.23.54a from Redhat installation 9.0. I could install the RPM 's with out any problem. I can see 'mysqld' program . But when I run it shows a message like mysqld started [ok].When I checked 'ps aux' , I could not see any process running with mysqld. Also I could not run any programs like mysqladmin etc. All of them fail. does anybody know what is the mistake ? What should I do ? Also when I try to stop mysqld it fails.
Extended-insert Not Working Properly
I am having a problem with using the --extended-insert option when using mysqldump.As I understand it, this option creates multiple insert statements within the .sql file for tables that contain more than 1 row of data. According to documentation I have found, this substantially speeds up the restoration process.The main database I am concerned with uses the InnoDB engine, however I also want to backup the mysql database as well (which, to my knowledge, uses the MyISAM engine). However, when I execute the database dump using this option, there is only 1 insert statement, and all values are seperated by commas.When trying to restore my databases, this causes my system to eventually freeze up due to the inefficiency of inserting multiple rows this way.I am using Windows XP, 1.8 Ghz, 512 MB of RAM. The command I am issuing is as follows: mysqldump --opt --single-transaction --u root --all-databases > "C: estbackup.sql" I have also issued the following command, with the same result: mysqldump --extended-insert --single-transaction --u root --all-databases > "C: estbackup.sql"
Properly Re-starting Replication Process
I've just tried to re-start a replication process and the slave's tables came up marked as crashed. I'm doing a repair on one of them but there are a lot of databases/tables so I can't check them all (or is there a table entry somewhere that flags crashed tables?) Here is the sequence I used: Code:
Storing Large Integers Properly
I am storing book isbn numbers in a table. isbn numbers are 10 digit numbers and many start with 0. The data type of the field I am storing this info in is a bigint(16) unsigned. It appears that isbns that start with 0 are going in as 9 digit numbers, the 0 is being ignored or stripped. I have experienced this before with integer data types ignoring leading 0s. I'm wondering how to address this? Should I change the field to a varchar or char data type?
Two Digit Numbers Not Formatting Properly
I have a table I am using to populate drop-down menus. It is for languages. each language has a specific code that must be saved to the field in question.They work fine except for two codes: 00 and 01. I can't change the codes to anything else. They have to be 00 and 01. THey are saving to the spreadsheet as 0 and 1 however.
Inserting Dates Properly Formatted
I have a dataset that has all it's date formatted as MM/DD/YYYY. I want to be able to insert this into a table that is using a field set as 'date'. How would I go about properly formatting the MM/DD/YYYY to insert into the table? Right now when I insert I end up with all the dates storing wrong. IE: 9/6/2005 stores as 2009-06-20.
Mysqlcheck: Clients Are Using Or Haven't Closed The Table Properly
I'm using the command mysqlcheck --all-databases --auto-repair --silent and I see these warnings (listed below) on a daily occurrence. Is mysqlcheck causing these warnings? I can't see how a table can close inappropriately. How can I prevent this. Hoping to here from MySQL users who have came across this. I'm using MySQL 4.1.11 on RHEL. Code:
Getting Text Retrieved From Mysql To Fit Tables Properly
I have been working on a really simple mysql/php set of pages. Page 1: Input 3 peices of data (Title, Date and Text) Page 2: Display all news that has been inputted I have it working, except for one problem. My website is built with html, and tables. I want it so that when the text gets to the edge of the table, it drops down a line and starts again on the next line - so it doesn't carry on and on. Theres no problem with title and date - they're just a few words, but the text part of the news is usually 5 or 6 lines long. This means that it wont all fit on one line without ruining the entire website display. Code:
How Many Databases Do I Need
I'm in the preparation stage at the moment of a really big project, now the project is going to entail people signing up to a website and then selling stuff online. We are looking at about 1000 people signing up to start and then growing to about 8000. The question I have is how many database's should I build the site around as there is going to be a "Super Admin" section where I will add all the products available on the site (about 15,000) and manage ad's and stuff like that. Then the person that pays to have an online shop will select products from the ones i have inputted and be able to change all his settings for his personal look to the site. Then there is the end-user who will come to the site sign up and then buy stuff, and his settings will need saving as well. So as far as performance goes would it be better to have a separate DB for each section ("Super Admin" - "User Admin" - "End User") or just use one and store everything in there?
Databases
I have a small residential design company and would like to create an online database so that I can start selling my plans online. I downloaded mysql a couple of months ago and played with it a little bit and even created a small database so that I could see what it could do. But how would I go about getting a database that people could do searches on with my website? Or is there another interface that I need to install ontop of mysql that will allow me to do this easiely?
How Many Databases Should I Use?
What do you think that would be better? To have 20 databases with 10 tables each database and a total of 2.000 rows in each database, or... To have 1 database with 11 tables and a total of 40.000 rows. How should I handle large projects?
Cant See My Databases
I don't know what the problem is here. I used to be able to install MySQL and go to this interface (please see attachment). Right-click and 'create datbase' but it doesnt let me do that. The only options it gives me is flush hosts; flush logs; flush tables and flush threads.
Use Two Databases
Use two Databases for authentication I would like to use two databases to authenticate users. The first database I would like to be checking usernames and passwords only and usernames in plain text and passwords to be in md5. The second database with the rest of the data the users need for privileges of the application that has to be in plain text. HOW? I have all the create scripts written but don’t know how to link the databases properly.
Each Set Of Databases
I would like to set up separate data directories and have mysql store data in those subdirectories. Each client would get their own directory. They would each contain the following databases: gl, 2004gl, 2003gl, ap, 2004ap, 2003ap, etc. I like to separate the database files for speed which was required when using MSAccess.Can this be done, or must I either wait for 5.0.2's schemas or redesign my database structure? I am using mysql 5.0.0 right now, but am only in the development phase, so I will upgrade asap before going into production mode.
2 Databases
I have a database with all users profile information, but i would like to export ceritain parts into another database, and the layout and colum names in the second database will be differnt. How would i do this on all users, then when a new users signs up, it will happen to them?
A Doesn't Get All Databases
I have an odd mysql configuration. Suffice to say that I have over 6000 databases (small ones, all automatically created). When I run mysqldump -A, it appears that it's only getting a subset of the 6000. It's only backing up 1175 databases.
2 Databases
I have a website with a forum, build in PHP and using a SQL database. When I use the phpmyadmin.com I can see my SQL database localhost/websitename_dk. Now I want to build another forum, using a complete other SQL database. Q: How do I create another SQL database at my server e.g. localhost/anothername so I have 2 databases? If I install the other forum, it will delete all my tables in my database, unless I give it another name.
Different Databases
I am developing an application that uses two separate databases. Each database is hosted by our ISP on a different IP address. I would like to write a query of the form: SELECT * FROM table WHERE db1_tbl.val = db2_tbl.val where db1 and db2 are my two independent databases. I write in ASP and I have a connection string to each database. What I need to do is select records from one database with a column containing values from the other. My current method, in code, is to open each recordset in turn, convert to an array and then do a look up from one to the other, which in code is complicated. Is there any way I can do it through a query?
Synchronizing Databases
I have my database locally and same database on sesrver also. i want that whichever thing is update don live server should also be updated the same time on my local machine automatically. how can i achive this? and also thier are certain folders for saving the uploaded pictures and other files ,i want alos the things which have ben uploaded should be downloaded to mt local folders also whever users upload these pictures and files. how can i do this also?
Listing Databases
Let's suppose I have 3 database in mySQL 4.1. They are myDatabase1, myDatabase2, and myDatabase3. I like to see the list of myDatabses in DOS mode. The following is my trial code. Code: trail code mysql> list all database; result ERROR target result myDatabase1 myDatabase2 myDatabase3 How can I get my target result correctly?
Organizing Databases
I have a problem with deciding how to properly setup my database/s. Iam creating a game site. In one place im storing all the game info and in another all the animation info. Should i create separate databases for the 2 categories? all the info now is stored in 1 table called either games or animations. But is it necessary in this situation to separate the 2 into different databases?
One Or Multiple Databases?
Just a quick question. My site uses three (potentially four or five databases) MySQLs, having no more than half a dozen tables each (one database just has ONE table). This was done because I wanted to keep different information distinct, and seperate from each other, to reduce database size, and to be somewhat more secure (if one db gets corrupted, the rest don't, heh). I've got a daily backup of these databases, the total size is about 4mb. The databases hold text, no big blob/file fields as yet. However, they do hold duplicate data, for example I've got a database for 'users' and one for 'forums', but if a user posts on a forum (to the 'forums' database), they need to pull their user data (avatar, custom sig, etc) from the 'users' database. This involves two database connections, and the usual tripe. Still in the same users/forums example, it'd be easier to have ONE database, but would this be safer/more efficient? I know that now, to retrieve a post, it's just a simple SELECT, since all the data's in the row. If they were in the same database, I'd use a JOIN using the userID in the posts table, plus the options for that post (avatar, sig, blah blah). What's the recommended practice? I can see which is easier, but would there be a perfomance hit using the JOIN (one which we'd notice, heh)? And while we're on that subject, how LARGE can a single MySQL database get? My project's fairly small, but could get bigger, I wouldn't like to have it slow down suddenly, because it's working pretty well now. Or would the differences be negligible, unless we get in the thousands of rows league?
Moving Databases
I am planning on moving hosts quite soon and am wondering how to go about moving my database from my current host to the new one. What should I do?
Syncing Databases
I have a LAMP system at home in which I develop my web sites. I also try to keep a current copy of the published site. My host is also a LAMP system. Updates are done via a control panel. Content is mainly photographs, thumbs and a blog. I am trying to develop a strategy to update the sites on my home system, then in a way that is as automated as possible, duplicate my local files and database on the web host.
2 Databases Updated
i have a software running over a mysql db. i want to, for example at midnight myself manually ask it to sinchronize with another db in another host, without the necesity of running the whole mysql again from zero.
Query Between 2 Databases
hi,i would like to make a query between 2 databases eg. PHP select memberID from databaseA.table1$id=$row['memberID']select address from databseB.table2 where memberID='".$id."' may i know how will the connection among these 2 databases work and how can i perform the query?
Portable Databases?
I am wondering if someone could please tell me how someone could make mysql databases portable? Someone has said this to me: The data in the databases needs to be portable so that if I need to changes servers or database design in the future it will be able to be done painlessly.
Moving Databases
Just how do you go about moving a database? I have a forum linked to a MySQL db, which is on one site, and the main site is on a completely different server. If I were to move the main site to my now main hosting package, how would I reconcile the URLs? I've searched the web, and talked to my host, but I'm none the wiser on the process involved. I'm not really after a detailed explaination here, just how you would go about it. I can work from there really.
Transfer Databases?
I want to transfer some databases from my machine to my web server. Do I just mysql dump and then upload them to the server database? If so, how do I upload them, which command?
Querying From Two Databases
I need to query from two servers at once [in the same script], both databases are on the same server [localhost] and use the same username & password. How can this be done [php]?
Searching From Two Databases
I have two databases, one called codes and one called crs_wk. One contains a table called school_1 and the other one school_2. Both tables have a column called school_name. I want a query that searches from both databases. something like this... SELECT * FROM crs_wk.school_1 union codes.school_2 where school_name like "cypress%" but it doesn't work. How do i do this?
|
TRACKER
