Do I really need foreign keys


I am currently designing a database that is to be used for ordering products from a site.

member, order-states, order, order-lines, product, review

I am considering using foreign keys between the tables. I am not sure if this would be the best way to do it or not.

Q1: If I use fk which tables need to be innodb, the ones containing the fk, the ones the fk are from or all of them.

Q2: If I were to delete a member do all related table entries get removed or does that only happen with the cascade keyword. This is important as you would still need a record of the transactions in the orders table.

Q3: What benefits are there to fk using innodb over myisam and simply storing the value from one table as a field in the other.

Hope these questions aren't to trivial and feel free to point me to tutorials.
Posted On: Thursday 25th of October 2012 11:06:15 PM Total Views:  423
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Multiple Selects pulling in my blog - im concerned this will run really slowly

Hi Guys and Gals my first post here. I am new to the world of MYSQL (what a subtly big world it is) and have spent a little time making a blog and reading some good books. I have my blog up and running on my dev machine and everything is working fine and I am now in the process of profiling and benchmarking and tweaking everything as I am concerned how its going to run when live and how it will handle increased traffic. I also want to learn about all of this. I wanted to post an excerpt from the page that pulls individual posts from the Database. My concern is that I have 7 individual select statements performing the following (in order): - Article id - Get article, title, author, postdate, image folder, main image, caption, comment status - Other images and captions - YouTube video - Quotes - Comments count - Comments and comment replies The last 5 tables all optionally have data in (I.E you can have quotes or not have quotes), the only fields that are there for every blog are parts of the first two: - Article id - Get article, title, author, postdate, comment status So pretty much that is why I have done them in different statements. I wanted to ask (and not be spoon fed) what people think to this Weather its an abomination and I should shoot myself and go and stack shelves somewhere or if I am on the right path but need a pointer or two. My site is expecting reasonable traffic and I will be implementing a PHP caching system on the blog but I obviously want the MYSQL to work as well as it can before I start caching. Anyway as always thanks in advance for any advice and time spent on this and any experienced pointers and helpful bytes of MYSQL wisdom would be gratefully accepted. And here is the code excerpt: Code:
VIEWS ON THIS POST

158

Posted on:

Friday 12th October 2012
View Replies!

Trivial question really... apostrophe

Really simple question. I just want to know why in sql files that often the key columns are escaped with ` instead '. Why is that Is it so that ' can't escape the string I am sure there is a good reason behind it. Could someone please explain
VIEWS ON THIS POST

274

Posted on:

Friday 12th October 2012
View Replies!

Is RAND() function really bad?

I used RAND() function and don't seem to notice much difference. My table size is around 10000 records and on my local system it takes around 5 seconds when I run RAND() LIMIT 50 Secondly using the RAND function many times I get either the top most random records or lower side. Is there any better way I could get 50 random records spread across the entire table , Update with regards time... I had placed the time counter at the wrong place. It was showing me 5 seconds for generating entire page... but the rand query takes around 0.015 ...so would any other query be better or is rand the best in my case
VIEWS ON THIS POST

166

Posted on:

Sunday 21st October 2012
View Replies!

Some advice on performance needed

I have two tables. One for products and one for reviews. I have setup a detail page which displays the various fields out of the products table and then looks for all reviews in the reviews table which match the products key and display the average. good so far. I want to have a page which displays a whole list of the products and show their rating next to them but I'm wondering if making it search the reviews table and calculate the average for 50 products in one go is going to cause a bit of a strain on the server Is there another way I can do this, perhaps storing the precalculated average in the products table but how would I go about doing that Or is this not a problem Any input you can offer would be very much appreciated. Thank You.
VIEWS ON THIS POST

229

Posted on:

Wednesday 24th October 2012
View Replies!

WHERE statement- ' ' needed?

I have the following statement: Code: SELECT DISTINCT u.user_title, u.user_first_name, u.user_last_name, e.course_name, e.course_date FROM user_name AS u, course_enroll AS e, course_complete as c WHERE e.course_ID = c.course_ID AND e.course_date = c.course_date AND e.course_session = c.course_session AND e.user_ID = u.user_ID AND e.user_ID != c.user_ID ORDER BY e.course_date DESC, e.course_name ASC My problem is the "e.course_session = c.course_session" in the WHERE clause. Both of these fields are varchar types. With the current syntax, the appropriate matched records are not being found. I tried: 'e.course_session' = 'c.course_session' and no records were returned. The data that will populate these fields will be things like "Session 1". Any advice on how to clean this up to get the statement to pull the right recordset is much appreciated...
VIEWS ON THIS POST

208

Posted on:

Wednesday 24th October 2012
View Replies!

Database Tean needed for important project - Please Read!

AnimalHelp Foundation is looking for some fabulous database developers for a very important pro-bono type job. If you have time to give for a really great cause, please contact Jodi Witte at jwitte@animalhelp.com to join the team! The Concept: Recent devestating hurricanes along the gulf coast have truly brought to light the inadequacies of a central system of animal record keeping and ultimately helping these displaced animals reunite with their families. There is NO central database that data can be easily entered by volunteers, and searched through by rescuers or pet owners. Petfinder.com has built a makeshift system that is helping, but is not great. If you visit http://disaster.petfinder.org/emergency/rescue/searchForm.cgitype=found and lets say you are looking for a solid black, 10 year old male german shepherd, you will find it is almost impossible to find your pet. Now, consider that this is not the only list of animals displaced due to Hurricane Katrina. These animals are spread out over 50 different databases such as http://www.badrap.org/rescue/katrina_dogs.cfm and http://katrinapets.shanonandmelanie.com/viewfound.asp. (The list of all known databases is available if you are interested). If that is not proof enough, I can send you a photo of a grey/black standard poodle which was picked up by a coast guard helicopter in New Orleans, then I can also include the list of all the databases. If this was your dog, would you find him I don't think so. I have tried. This dog I mention was in my care at the New Orleans airport as I received him off the helicopter. He sat for 2 days with me on the tarmac watching for his family to show up. While we were indoors, he would sit facing the door, just waiting. Nothing and no one could get his attention, he was so sure his owner would walk through the door. He was then moved to a shelter in Baton Rouge where I visited him again. He had given up and no longer was watching for them. But then he was moved again, and now despite my best efforts, I can't find him. I don't know where he was sent, which of the multiple shelters all over the US he has been taken. If I can't find him, then I can only conclude that his owners never will either. This is very sad!!! What happens after a disaster is many groups converge on an area and begin rescue. The animals are sheltered at a few main locations, with some smaller private home sheltering one or two animals, vet clinic and also boarding kennels also doing sheltering on a small scale. Each group uses their own intake forms, and then it often takes 3 to 4 weeks before data entry is started. The Solution: AnimalHelp Foundation will provide one central online database, digital cameras and laptop computers at every location, along with Directway satellite internet access and generators to power the computer equipment. AnimalHelp Foundation will also provide dedicated data entry volunteers at each location. Right from the beginning these animals are entered in the database and their locations tracked at every step. In the database all information will be entered by the volunteers, including sex, coloring, tags, collar color, location found, breed, etc... This database will be searchable by breed and color, or color alone, or any combination of description or information. It will be a high powered database, making it easy - because everything is there - for an owner to find their pet. Every pet will have a photo with their entry. Owners will also be able to add information on lost pets and the last known location. This database must be able to cross reference on each and every detail. Shelters and rescue groups must be able to pre-register before a disaster. Individuals who find an animal can register at that time. And, it will include a credit page with bios of the entire database team who gave their time and expertise to build this invaluable tool. I will need a team - the cream of the crop in database development. I know disaster response, I know what we need. But I need a database team, headed by someone who can lead and direct the team to make this the most powerful animal disaster tool ever. If you are willing to join this team, please contact me immediately. We need to get started because the disasters just keep happening...and the sooner we are ready, the better!!! I look forward to hearing from you! Jodi Witte President AnimalHelp Foundation jwitte@animalhelp.com Jodi Beck Witte currently works full time for AnimalHelp and is also a member of the Veterinary Medical Assistance Team (VMAT), part of the National Disaster Medical System, US Department of Homeland Security. In addition to her disaster response experience, she has worked as a veterinary technician in both small animal practice and at a zoo, and has done wildlife rehabilitation for many years. She is certified in Chemical Immobilization/Remote Capture, Large Animal Rescue, and is certified as a Weapons of Mass Destruction Specialist.
VIEWS ON THIS POST

185

Posted on:

Wednesday 24th October 2012
View Replies!

3 tables delete query. mysql geek need :)

I have 3 tables! Need a MYSQL delete query to delete rows in 3 tables at same time... table1: log_visit 1) Need to DELETE from log_visit WHERE counter > 5 id | visit | visitor | counter 1 - 10 - qwerty | 15 2 - 11 - qwerty | 10 3 - 12 - azerty | 8 4 - 13 - azerty | 1 it will delete row 1 and 2 table2: log_link_visit_action 2) need to get visit and visitor rows deleted from table1 and delete in table2, in this case [row 1-table1] DELETE from log_link_visit_action WHERE visit = 10 AND visitor= querty [row 2-table1] DELETE from log_link_visit_action WHERE visit = 11 AND visitor=querty [row 3-table1] DELETE from log_link_visit_action WHERE visit = 12 AND visitor=azerty id | visit | visitor | url 15 - 8 - dodid - yahoo.com 16 - 10 - qwerty - google.com 17 - 11 - qwerty - abc.com 18 - 15 - kaz - zzz.com it will delete row 16 and 17 table3: log_action 3) Need to delete all url deleted in table2, in this case [row 17-table2] DELETE from log_action WHERE idaction = 'google.com' [row 18-table2] DELETE from log_action WHERE idaction = 'abc.com' id | idaction 1 - google.com 2 - yahoo.com 3 - abc.com it will delete row 1 and 3 Any possible help joining delete queries in one query would be extremely helpful Many
VIEWS ON THIS POST

196

Posted on:

Thursday 25th October 2012
View Replies!

Join sintax help - not even know if I really need a join - please advice. :)

all, This is a common scenario but I'm struggling here. I have 3 tables. The middle one relates table 1 and 3 by having a Fk from both tables. I would like to list some data from table 1 and same data from the table 3. But that data should be organized by ordering a given column in table 2. Can I have an example of the above, so that I can study and try to apply it to my code If you prefer, I can well provide the create tables here, I was just thinking that, like this, I can properly learn. K.
VIEWS ON THIS POST

221

Posted on:

Thursday 25th October 2012
View Replies!

subquery max group needs and additional where clause

I have a discussion forum that I would like to filter approved posts. I would like to know how to select the max(datetime) but only if the max has an approved value of 1. The only way I can think to do this is adding a where clause of approved = 1 but this will not work with a group by. Any suggestions Code MySQL: select * from questions where datetime in (select max(datetime) from questions group by id )
VIEWS ON THIS POST

240

Posted on:

Thursday 25th October 2012
View Replies!

need help with simplifyling mysql queries.

ok im really not sure what this is called so im making a new thread. in my current website i need to select photos using 3 different mysql calls, is there a way to simplify them into one mysql call there are 3 types of photos i need selected and they are related to the current photo and "report" thing, so far i have been doing it this way: Code MySQL: "SELECT id FROM photos WHERE seq > :seq and reportid=:reportid and seq NOT LIKE '0' ORDER BY seq ASC LIMIT 1"; "SELECT id FROM photos WHERE id < :fotoid and reportid=:reportid ORDER BY id ASC LIMIT 1"; "SELECT id FROM photos WHERE seq=( SELECT MAX(seq) FROM photos WHERE reportid=:reportid) and reportid=:reportid ORDER BY ID ASC LIMIT 1"; but now i started wondering if its a bit excessive and could be possibly simplified into 1 mysql query
VIEWS ON THIS POST

283

Posted on:

Thursday 25th October 2012
View Replies!

CTE /w Multiple JOIN needs

I'm using CTEs along with a JOIN to pull personnel information. This works fine. Now, I want to try to pull more information from another table, "Phones". Phones contains multiple phone numbers, of multiple types, for personnel. Each person's phone are also ranked in order of importance and they can have multiple phones of the same type (cell, home, work, etc). Phones PhoneID PersonnelID PhoneType PhoneNumber SequenceNo Now, what I'm trying to do is continue pulling all Personnel, but also the highest phone number of a particular type (optimally in the same SP). What I have so far: Code SQL: CREATE PROCEDURE dbo.Personnel_GetContactHelpSummariesByStartingNodeID ( @StartingNodeID INT, @PhoneType CHAR(3) ) AS WITH Nodes (NodeID, ParentNodeID, SequencePath) AS ( SELECT NodeID, ParentNodeID, SequencePath FROM Personnel_CommandHierarchy WHERE NodeID = @StartingNodeID UNION ALL SELECT CH.NodeID, CH.ParentNodeID, CH.SequencePath FROM Personnel_CommandHierarchy CH JOIN Nodes R ON R.NodeID = CH.ParentNodeID ) SELECT Personnel_Personnel.PersonnelID, Personnel_Personnel.FirstName, Personnel_Personnel.MiddleInitial, Personnel_Personnel.LastName, Personnel_Personnel.SuffixCode, Personnel_Personnel.CommandHierarchyNodeID, Personnel_Personnel.ServiceMember, Personnel_Phones.PhoneNumber, Personnel_CivilianPersonnel.CivilianClassificationCode, Personnel_CivilianPersonnel.CivilianRankCode, Personnel_ServicePersonnel.ServiceRankCode FROM Nodes INNER JOIN Personnel_Personnel ON Nodes.NodeID = Personnel_Personnel.CommandHierarchyNodeID LEFT OUTER JOIN Personnel_Phones ON Personnel_Personnel.PersonnelID = Personnel_Phones.PersonnelID INNER JOIN Personnel_CivilianPersonnel ON Personnel_CivilianPersonnel.PersonnelID = Personnel_Personnel.PersonnelID INNER JOIN Personnel_ServicePersonnel ON Personnel_ServicePersonnel.PersonnelID = Personnel_Personnel.PersonnelID WHERE Personnel_Personnel.CommandHierarchyNodeID = Nodes.NodeID AND Personnel_Personnel.Archived = 0 ORDER BY Nodes.SequencePath, Personnel_Personnel.LastName, Personnel_Personnel.FirstName RETURN Since I'm already using the WHERE clause to join my Personnel table to the table created by the CTE, I'm not sure where I can indicate that I only want the top-ranked phone number of a particular type.
VIEWS ON THIS POST

129

Posted on:

Thursday 25th October 2012
View Replies!

simple database. just need some pointers.

I there. I am currently working on a clients site that i think would benefit from a simple database. The site is a semi truck dealer all i would need is some way to display a list of trucks with pictures and a brief description. From there be able to click on a specific truck and view a details page with an expanded view of the truck, a bigger picture and more details. The most important thing is that what ever the information comes from the client needs to be able to update it him self. I have worked with mySQL and some php but really have almost no experience in those or any kind of CMS. can anyone point me in the direction of a tutorial or something that could help me learn how to make or adapt something to my needs.
VIEWS ON THIS POST

159

Posted on:

Thursday 25th October 2012
View Replies!

need help with a query

The relevant cols in my table are these. |Session_ID | referer | domain_viewed | (please don't worry about the upper and lower case. this is a test/practice db and I have learned about that issue. ;o I am trying to find out the total number of unique viewers (sessions), who were refered by referer AND who visited domain_viewed My query is like this but it gives seems to give me the total number of visitors referred irrespective of session. Code mysql: SELECT count(*) from tbl_Statistics where referer = 'www.refering_domain.com' and domain_viewed = 'www.our_domain.com' group by Session_ID How can I make sure that it returns say a composite result of 4 sessions :: 650 page views (total across those sessions) bazz
VIEWS ON THIS POST

169

Posted on:

Thursday 25th October 2012
View Replies!

Help needed for query.

Everybody, i want to know about how to retrieve value order by numeric and alphabetic words in one query e.g:- I have value in db like 1180A,1180B,1180C so how to get 1180A first then 1180B then 1180C
VIEWS ON THIS POST

196

Posted on:

Thursday 25th October 2012
View Replies!

I need to update a large amount of values in 2 columsn in 1 table

I have a table that I need to change all values for all rows in 2 columns (used for mapping products). Is there any SQL that will let me just say something like, update the 2 columns and insert these values in this order Example: these are the values: 2, 4, 6, 3, 4, 1, 3, 4 And those values would be inserted as the values for the first 8 rows Sorry, noob question probably Thank you
VIEWS ON THIS POST

183

Posted on:

Thursday 25th October 2012
View Replies!

Complex SQL query - which statements do I need?

I've got a table with over 1,000 entries. Those entries are category names for an eCommerce site. Each entry has cat_id and also cat_father_id. I'm trying to get information from within the one table which is like this: cat_father/cat_father/cat_father/cat Basically keep looking up in the table until all parent categories are identified so that I can build a URL to any particular category. What statements should I be looking at to get this
VIEWS ON THIS POST

139

Posted on:

Thursday 25th October 2012
View Replies!

I need some guidace regarding ACCOUNTS table

I am developing a website. That website will offer some products, registration fees and monthly fees etc. What i think is that to create a TABLE called "ACCOUNTS" and maintain records with following fields. This website will receive the payments in terms of REGISTRATION FEES / MONTHLY FEES AND PRODUCT PRICES and It will also pay the commissions to sellers etc. it is simple like a SALESMAN can register with suppose $100/- and can sale the products with this reference id. so that is how we will receive the payments, and he, the seller will earn from our site so we will pay to him as well. So we need to record both the transactions. RECEIVEs and PAYMENTs now, should I make separate table for the 2 transaction or i can make one table like follow : 0) transaction id 1) transaction date 2) description 3) debit /// to record the REVENUE 4) credit /// to record the EXPENSES 5) to_userid 6) from_userid 7) status please guide !
VIEWS ON THIS POST

146

Posted on:

Thursday 25th October 2012
View Replies!

Stored Procedure needed

I have a table named players with these fields: Code: playerid {int} teamid {int} jersey {int} playername {varchar(50)} age {int} And a table named pitchcounts with these fields: Code: id {int} gamenum {int} playerid {int} pitchcount {int} Now, what I need is a stored procedure to return the number of pitches each player pitched in a week where the weekno is passed and the days rest required and next availability date of the pitcher based on the criteria below: Code: In-House Ages 13-16 (Babe Ruth) -------------------------------------------------------- Pitches Days Rest ------------------------------- 0-20 0 Rest 21-40 1 Rest 41-60 2 Rest 61-95 3 Rest ******************************************************** In-House Ages 11 & 12 (Majors-American, National & Continental) -------------------------------------------------------- Pitches Days Rest ------------------------------- 0-20 0 Rest 21-40 1 Rest 41-60 2 Rest 61-85 3 Rest No more than 125 pitches in a week. 4 12's can pitch but no more than 240 pitches no matter how many games in a week. ******************************************************** Travel Ages 8, 9, 10, 11 & 12 -------------------------------------------------------- Player can pitch up to three innings in a weekend. Two innings doesn't affect In-House the next day, but prior In-House pitching does apply. If 61 or more pitches on Thurs or Sat no travel that weekend. A travel manager must obtain approval from the In-House manager in order to pitch a player for more than one inning if the in-house team has a game following the travel team on the same day. If pitchers does pitch more than 1 inning in travel, pitcher limited to 60 pitches In-House. If travel follows In-House on same day, player can pitch in travel if no more than 60 pitches thrown in-house. These rules will be in place until may 1st. We want these rules to be followed in spirit as well as literally. If abuses are found, even if within rules, we will change them after may 1st. Under no circumstances is a player able to be made ineligible for In-House play. Code: ******************************************************** In-House Ages 10 and 9'S in Majors (Majors American, National, Continental) -------------------------------------------------------- Pitches Days Rest ------------------------------- 0-20 0 Rest 21-40 1 Rest 41-60 2 Rest 61-75 3 Rest ******************************************************** Double A In-House (AA) -------------------------------------------------------- No more than 40 pitches in a game or 80 for a week. ******************************************************** Additional In-House Notes -------------------------------------------------------- If a pitcher gets to 85 (75 at age 10) pitches in a game in the middle of a batter, they can finish the batter only. Pitching results must be reported by email to Director within 24 hours of the game by the winning manager. Pitcher can not re-enter to pitch in the same In-House game once removed from the mound. Pitcher receives seven warm-up pitches before their first inning on mound and five every inning thereafter. Warmup pitches no not count toward total counts. Pitches to final batter, if count gets past game or week totals, don't count toward totals. 12 year olds in Continental League can not pitch. Rest Examples: Code: if 20 or less on Monday, available on Tuesday if 21-40 on Monday, available on Wednesday if 41-60 on Monday, available on Thursday if 61 or more on Monday, available on Friday
VIEWS ON THIS POST

158

Posted on:

Thursday 25th October 2012
View Replies!

Help needed merging 2 different but similar tables

Can someone tell me how I can import tables from another non-Joomla mysql file into Joomla Basically it is just from one mySQL database into another. I use phpMyAdmin to import and export the entire file but I don't know how to do queries. I tried exporting the source database and then renaming all the database names to match the ones I want to merge into but all that happened was a new table was created - no merging. I really just want to select the relevant 3 fields and match them up with the fields in the database table I want to import into. I have an old program that listed all the fields of database 1 in one column and database 2 in another and all I had to do was drag the fields across to make a match and the program did the rest. Perhaps there is a similar one out there What I am trying to do is import all the articles I have built up using ccTiddlyWiki into Joomla. The only table names of relevance from the source file are: id - title- body IN other words, just the bare essentials. The target files has a table called jos_content with columns: id - title - title_alias - introtext - fulltext in that order and others but are not important. I think it is fairly simple but I am not trained enough. I have 200 items so it would save an enormous amount of time! It seems easy but I don't know enough about queries to do it well. I'm hoping that I can import the articles and then just go through the list in Joomla adding subtitles and so on to conform to Joomla database essentials. There is another column called tags which I would be happy to have as well and I'm not bothered about start dates or anything else.
VIEWS ON THIS POST

141

Posted on:

Thursday 25th October 2012
View Replies!