Conceptualize Table Design For Stats
I have a stats page my users can go to so they can view how much traffic their listing has received.Creating this for the total view stat is straight forward and I have that working. But I want to also add two more features to this:
CODE1. See the total views for just today
2.See a history for the listing over the duration of the post (ie. 30 days).
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Trying To Conceptualize Table Design For Stats
I have a stats page my users can go to so they can view how much traffic their listing has received. Creating this for the total views is straight forward and I have that working. But I want to add two more features to this: 1. See the total views for just today 2. See a history for the listing over the duration of the post (ie. 30 days). I am at a lost how I would create a table or several tables to accomplish this. I am looking for some guidance to get me thinking about the best way to do this.
Table Design For Storing Item Stats
I've created quite the database for my app, and I'm familiar with normalization and general db design. However, I'm ready to design my tables for recording all sorts of stats for my db items. I want to be able to view stats for each item, such as number of small views, number of large views, number of downloads, ratings and so on. I also want to be able to view these items by periods of time, such as by day, week, month, by multiple months, by year, etc. So, I'm trying to figure out what the most efficient way of recording this data, so that I can keep my tables as small as possible. I'm wondering if I will have to insert a row for every stat, or if I should enter a row for every day, or only a day if there is activity, etc.
How To Setup A MySQL Table To Keep Track Of Stats?
I have a new system Im building that stores entries of what people are searching for on my sites. I want to be able to keep records of how many times a keyword was searched for daily, and from that I can calculate weekly and monthly. At this point I have one entry per search phrase with the number of hits the search phrase has gotten, and the last time it was updated. As I start to take the program out of testing and move in more into a used tool Im getting worried that my idea of switching to mutable entries for the same search phrase would be a bad idea as within the last 15 days I have stored more then 3 million unique search phrases, and a unknown number of hits. So should I make a rolling database that stores each search then rolls that data over to another database once a week that users can use, or is their a better way of doing it where I only keep one entry per search phrase and am still able to keep records how daily search amounts so that I can track trends, etc in search phases on my sites?
Table Design Question? House Table, Owner Table, Code Violations Table - Best Way?
Given the tables: HOUSE house_ID address OWNER owner_ID name telephone... HOUSE_OWNER_JOIN ? CODE_VIOLATION_HISTORY house_ID violation_ID violationStatement ... My goal is to be able to track code violations of the house PER owner. For example, I need to display a page that shows the current house with it's coe violations and a link to show the HOUSE's history of violation regardless of owner, Like: House 1009283 Address Past history (link to the following) House History 2001-01-04 Owner: John Smith Code Violation: Gutter issue 1999-06-01 Owner: John Smith Code Violation: Faulty Steps 1998-03-02 Owner: Sam Spade Code Violation: Driveway carcks 1990-01-12 Owner: Keith Sledge Code Violation: Grass untidy For the design of the HOUSE_OWNER_JOIN table, I thought of two ways I could go on this and this is where I need your help. Option 1: Have the HOUSE_OWNER_JOIN table keep dates so I can track the ownership changes that way: HOUSE_OWNER_JOIN houseID ownerID dateOwnershipBegan dateOwnershipEnded then I could look up all code violations by date and associate them with their rightful owner. ================================================== Option 2: Have the HOUSE_OWNER_JOIN table be the primary keeper of identity data by adding a new primary key and changing the CODE_VIOLATION_HISTORY table to reference that table by chaning the referencing key from house_ID to house_owner_ID: HOUSE_OWNER_JOIN house_owner_ID houseID ownerID dateOwnershipBegan dateOwnershipEnded CODE_VIOLATION_HISTORY house_owner_ID violationStatement ...
Table Design
I am designing a table for "friend relationships". I have three possible solutions: a) user_id (int) friend_id (int) created_at (timestamp) This will require 2 rows per friendship. b) user_id (int) friend_id (int) user_id2 (int) friend_id2 (int) created_at (timestamp) This requires only 1 row, but 2 indices. c) user_id (int) friends (text) This contains a comma delimited lists of all friend_ids. I am leaning towards option 3, ofcourse, adding & deleting friends will be a bit slower, but the bulk of the load is on reading of friend data anyways. However I have a question if this would be slower: QUERY 1 select items.* from items inner join friends ON friends.friend_id = items.user_id WHERE friends.user_id = [my_user_id] QUERY 2 select items.* from items where user_id IN (select friends from network where user_id = [my_user_id] QUERY 3 $friends (variable) = select friends from network where user_id = [my_user_id] select items.* from items where user_id IN ($friends) Query 3 requires 2 queries. 1. Which of these would be faster? 2. And would you still choose the solution you chose in 1. if I had to use the friend userlist on multiple occasions on the same page? (select from groups, select from bulletins, select from items, select from users)
Table Design
I have a question about table design, here is the background. The main table is "Customer" with standard stuff, first/middle/last name etc.. The next table is CustomerType. CustomerType has 16 types and a Customer can have more that one type. So below is what I think is correct design and I just wanted to confirm this is correct: Table: Customer CustID INT(11) not null primary key auto_increment First varchar (15) Middle varchar (15) Last varchar (15) ENGINE=INNODB Table: CustomerType CustID INT(11) not null Type1 enum('Y','N') not null default 'N' Type2 enum('Y','N') not null default 'N' Type3 enum('Y','N') not null default 'N' Type4 enum('Y','N') not null default 'N' FOREIGN KEY(CustID) REFERENCES Customer(CustID) ENGINE=INNODB Is there a better way?
Table Design
I need have a table that will have a column that will have a numeric value. In some instances the numeric value will be a percent and in some it'll be a dollar value. My questions are: 1. What is the best datatype to use? Currently I have it setup as a Decimal(10,2) datatype. 2. Guessing the datatype above is suitable, how can I tell if the value is a percent or a dollar amount? 3. There are some instances where, if the value is a percent, that it needs to be identified as < xx%. Using the Decimal dt, how could I record the "<" that needs to accompany it?
How To Design The Table?
i have a situation where a: Table : member One of the table field : hobby And my member table need transaction and at the same time, i need a FULLTEXT search to be perform in hobby table I know FULLTEXT support by MYISAM and Transaction supported by InnoDB So how can i do that?
Table Design
This is more related to table design, but there is no forum for that so I figured I post it here since I'm using a MySQL server. Basically I have two ways of designing the structure a table and I need advice. The app I am building requires that a user have access to certain files, so I have to store which files they have access to in a table. I can either create it with two fields: one for user id and one for the files they have access to. The other way I was thinking of is to create a table with one field for user id and one field per file. The second option would allow me to create a client adminstration screen easier since there would only be one row returned per user. But it does run risk of having a lot of fields.
Table Design
What is the preference with tables and column numbers? I was planning to build a table with 45 columns but is it better to split the table in to smaller tables with 10 columns each and then connect them via a primary key?
Table Design
I have a bunch of baskets. There are about 6 types of baskets, and some can only contain fruit, another vegetables, another cookies, and so on. Furthermore, each is designed to be able to only hold one of each type (i.e. the fruit basket has a slot for an apple, an orange, a peach, and a pear). Some baskets have more slots than others. Also, it is possible for a basket not to be full and have empty slots. Lastly, each basket type has a couple of unique fields (i.e. fruit basket has location grown, cookie basket has chefs name, etc). I need to create a list of all items in all my baskets along with the basket number and basket type such as: Basket 1, Fruit Basket, Apple Basket 1, Fruit Basket, Peach Basket 2, Vegetable Basket, Radish Basket 3, Fruit Basket, Peach ... I am having a difficult time figuring out how to structure the tables.
Table Design
I need a DB design to emulate a operating system so I can add folders (Categories) with unlimited folders (Sub categories) in side folders
Stats Query - If You Are Pro, Help Me :D
I've taken over developing a statistics page for a client. The statistics are recorded in a table named 'visitors'. This table is about 35,000+ records at the moment. I need to be able to display total site hits and new visitor hits for periods such as today, this week, month and year. It creates a new record on every page thats visited on the website. The table has 7 fields: ----------------------------- ID REMOTE_ADDR - logs visitors ip address HTTP_USER_AGENT - logs browser HTTP_REFERER - logs referal if exists SESSION_ID - logs visitors session id PAGE - logs page visited DATE_ADDED - date record created - timestamp ----------------------------- Currently the ID fields are used to test if the current remote_addr has a previous record. This is very resource intensive to test each 35,000 rows against each other. Can anyone suggest a better system or a solution? Thanks in advanced! I've added an example of the code the previous developer was working on. The sql below is meant to split up the day by hour showing total hits and new visitors for a specific day. I've tested the SQL and it times out on the new visitor 'if function'. SELECT CONCAT( DATE_FORMAT(date_added, '%k'), ':00 - ', DATE_FORMAT(date_added, '%k'), ':59' ) as `day`, DATE_FORMAT(date_added, '%W %e %M %Y') AS act_day, COUNT( DISTINCT REMOTE_ADDR ) AS un_total, COUNT( DISTINCT session_id) AS site_hits, COUNT(IF( v1.REMOTE_ADDR IN ( SELECT v2.REMOTE_ADDR FROM visitors AS v2 WHERE v2.id < v1.id GROUP BY REMOTE_ADDR ), NULL, 1 ) ) AS un_new, FROM visitors AS v1 WHERE DATE(date_added) = DATE('".$_GET['day']."') AND page != 'REFERAL' GROUP BY HOUR(date_added) ORDER BY date_added DESC;
Stats Not Logging
My stats arnt being logged. Page loads with no errors.. $update = "UPDATE `log` SET hits='hits + 1' WHERE ip='$ip' AND date='$date'"; if (mysql_query($update) or die(mysql_error()) ) { } elseif($logged[username]) { mysql_query("INSERT into log (ip, hits, date, user) VALUES ('$ip', '1', '$date', '$logged[username]'" or die(mysql_error()) ); } else{ mysql_query("INSERT into log (ip, hits, date, user) VALUES ('$ip', '1', '$date', '$user'" or die(mysql_error()) ); }?
Index Use Stats
Is there some way to obtain some sort of statistics of how often, or how much is any given index used for queries? What I want to know is if an index is not used so it can be erased or changed, because it is being of no help, and just wastes space and slows down insertions.
Mysql Table Design
Any advise are welcome with this inquiry. I have learned MySQL for two weeks after buying books from Kevin Yank and I would like to make a project called Attendance Monitoring System or TimeIn-TimeOut Monitoring System using PHP/MySQL flatforms. However, I have a huge vague or dont know what to do on database or table designing. Is it feasible if I make a one table for employee's TimeIn and TimeOut? Or do I have to make a separate table for TimeIn and TimeOut. If so, how I would fetch and match employees TimeIn with Timeout for particular shifts. Have 3 shifts in work 23:00 - 08:00, 02:00 - 22:00 and 8:00 - 17:00 on weekdays and weekends are their rest days.
Database Design: Keep One Table Or Add More?
This is perhaps due to bad planning but regardless its now an issue. I plan to have different sections on my site, each with a general article feel but displayed differently and whatnot. I decided using a simple `article` table with things such as title, short description, body, author, and so on. The problem is now I realize each section might need a bit more data than other sections. The question is: Do I make these seperate tables or just add fields onto my existing article table to handle these edge cases. There are a few issues with seperate tables. One being my comment system will no longer work since it assumes everything is an article. Using one table with extra fields would solve this problem but then some of the columns will be null for each row. I don't think that is such a bad thing, especially since I'm not going to have too many rows in the first place. What do you guys suggest? I'm leaning towards adding more columsn to my existing database.
Table Design Dilemma
While designing the database backend for a character management system for a fantasy game, I've come across a dilemma: In one particular table, there is a field that (without getting into the game mechanics) stores bonuses on equipment/effects/whatever. The problem is this: most of the time there is precisely one bonus, occasionally two or three. I'd say the statistical breakdown would be roughly: 1 bonus: 45% 2 bonuses: 35% 3 bonuses: 19% 4 or more: 1% There's theoretically no maximum to the number of bonuses for a given piece of equipment/effect/whatever. To make this clearer, here is an example of what a list of 4 bonuses might look like if represented as a comma-delineated string: str+6,ac+23,init+4,dex-2 So how do I store this? My first thought was to use a Varchar field and programmatically break it apart in my application (trivial to do), but that would limit me to an artificial maximum. A many-to-many relational table also occurred to me, but this seems ridiculous when one realizes that a given bonus could exist solely from a single source; also, there's countless bonuses that are just minor adjustments to each other, for example one could be str+1, another str+2, another str+3, etc. Despite that, however, I'm leaning toward the many-to-many relational table as my solution. I've also considered using a TEXT field, but I'm not sure that'd be the appropriate approach here. So what do y'all recommend? The vast majority of the time, this data is small (well under 20 characters 99% of the time if represented as a string; the example above is 23 characters but is an example of the extremely rare 4-or-more case), but I want to be able to handle the theoretical case of a really really long list of bonuses.
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
Question On Db Table Design
I'm designing a db and have 2 ideas on the table design and was hoping I could get some comments on the pros and cons of the 2 designs. Basically, this is for toolbar information that will be stored in db tables, and the data is somewhat in an outline form such as: Toolbar Heading 1 Menu Heading 1 Link 1 Link 2 Menu Heading 2 Link 3 Link 4 Toolbar Heading 2 Menu Heading 3 Link 5 So, there are 3 tables: Toolbar Heading, Menu Heading, and Links. For simplicity, I'm leaving out all the info that would be in those tables. Here are my 2 design options: 1. Add a field in the Menu Heading and Links tables indicating the "parent ID." For example, Menu Heading 1 would have a parent field containing the ID of Toolbar Heading 1. Likewise, Link 2 would have a field containing its parent: Menu Heading 1. And so on. 2. Don't put the parent IDs in the Menu Heading and Links tables. Instead, create 2 tables that indicate the relationships. For example, a Toolbar Headings To Menu Headings Table would list the IDs of all the Menu Headings and their associated Toolbar Headings. Likewise, there would be a Menu Headings To Links Table indicating all the Menu Headings and their associated Links.
Db/table Design Question
I have table/db I need to create that has a rectangle structure even when normalized, but the difficulty is that the rows can have a large number of column - over the max of what mysql can handle. If I put the data in vertical format I lose a lot of functionality. If I split the tables into n number of tables and keep the data on the horizontal it make for some combersomeness. I'm sure this is a common problem but I have never need more that 5-20 columns per table. I also am considering writing code that may compress/decompress the columns into say a blob, but this again leaves me in the cold, with out the power of sql.
Design Of Table For .doc Archive
I'm trying to build an archive of documents and need some help with the table structure to make the archive searchable. An example of my doc's are: Main category: Holiday, Lifecycle, History Sub Category: New Year, Birth, Independence Day Some files may be a main category i.e. a general doc on holiday, others could belong to more than one sub category i.e. a main category Calendar would have New Year and Independence Day as well. I also don't know if I could get them all under a main and sub and may need a third level. I would like to the user to find the file by 1) search box, 2) keyword index, 3) click on main category which will bring to sub, and so on. 4) search through drop down menu. (Did I miss any?) Am I building this properly with main and sub cats, or is there some other method? And how do I structure the table for sub and sub sub cat? What do I do with a file that doesn't really belong anywhre, make a misc.? Code:
Table Design Question
I am building a website that allows users to make a custom playlist for the site's media player. The playlist has no maximum number of entries, and each user to this site will have their own playlist. What would be the best way to organize this data? Should each user have their own table, with simply a list of which files to play? Or should I put all playlists in 1 large table? Is MySQL the best way to store this data?
GeoTarget And Table Design
I want to add GeoTarget feature to my in-house AD system. The problem is, how to add country attributes to my AD table. For example, to select all ADs for US customers, the query should be select like this: SELECT * FROM table WHERE us=1. To select all ADs for CA customers, the query will be SELECT * FROM table WHERE ca=1. But it is impossible to add N columns (us, uk, ca, cn...) to represent N countries. What is your suggestion? Can I use "Bit AND" operator on an index in WHERE clause?
Running Update Stats
I am trying to run an update stats command but keep getting a syntax error: Looking at the doco it looks like I can use: update stats <table_name>; but when I use: mysql> update stats x_balance; ERROR 1064: You have an error in your SQL syntax near 'x_balance' at line 1 What am I doing wrong.
Running Stats On Data
I have a table (artist_nodes) that refers to a number of keywords (around 100) (nodes table) associated with a number of artists (artists table) i.e. artist_nodes table col 1 - id_artist_node (primary key) col 2 nodeID_artist_nodes col 3 artistID_artist_nodes the content of each column is a number code that relates to the primary keys of content in the artists and nodes tables. Is there a method for counting the number of distinct values in the nodeID_artist_nodes column, summing these values, and calculating how often values occur with each other? So for example, if 1 = installation, and 45=interior and 46= exterior i am looking for the number of instances of 1, 45, and 46, and also how often each occurs with the others when associated with the same artistID (as a percentage) so the output i am looking for in php would be something like installation (1) = 35 instances (3% of sample) interior (45) = 23 instances (1% of sample) exterior (46) = 30 instances (2%) installation with interior = 10 instances (x %) installation with exterior = 20 instances (y %) installation with exterior and interior = 4 instances (z %) is there a way of doing the inverse of this, i.e seeing which keywords never occur together?
Normalization: Two Examples Of Table Design. Which Is Better?
Assuming I have an application where the user will only have one mother, father, and spouse, which is the more logical format to use? CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, created DATETIME NOT NULL, fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, bday DATE NULL, PRIMARY KEY (id) ) ENGINE = INNODB; CREATE TABLE mother ( user_id INT UNSIGNED NOT NULL, fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, bday DATE NULL, PRIMARY KEY (user_id), FOREIGN KEY (user_id) REFERENCES user(id) ) ENGINE = INNODB; CREATE TABLE father ( user_id INT UNSIGNED NOT NULL, fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, bday DATE NULL, PRIMARY KEY (user_id), FOREIGN KEY (user_id) REFERENCES user(id) ) ENGINE = INNODB; CREATE TABLE spouse ( user_id INT UNSIGNED NOT NULL, fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, bday DATE NULL, PRIMARY KEY (user_id), FOREIGN KEY (user_id) REFERENCES user(id) ) ENGINE = INNODB; *** .....OR..... *** CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, created DATETIME NOT NULL, PRIMARY KEY (id) ) ENGINE = INNODB; CREATE TABLE family ( user_id INT UNSIGNED NOT NULL, cat_id TINYINT UNSIGNED NOT NULL, fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, bday DATE NULL, PRIMARY KEY (user_id, cat_id), INDEX (cat_id), FOREIGN KEY (cat_id) REFERENCES cat(id) ) ENGINE = INNODB; CREATE TABLE cat ( id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(10) NOT NULL, PRIMARY KEY (id) ) ENGINE = INNODB; INSERT INTO cat (id, name) VALUES (1, 'mother'), (2, 'father'), (3, 'spouse');
Good Table Design Question
I have a quick question, let's say I have a table w/ 8 million rows, and each row is indexed by userid and "type", and let's say each user can have 8 "types". Now I was thinking of separating the tables into 8, based on the type, so I would have something like user_type1. And each row will have a unique userid as primary key. Now the question is, is it better to separate the tables or keep it as one huge table? Sample queries will look like: - SELECT * FROM big_table where userid = 123 => this will return multiple rows or some of these: - SELECT * FROM small_table1 where userid = 123 => this will always return 1 row because userid is primary key
MySQL Table Design For Web Tracker
I'm currenty writing a PHP based tracking script and was wondering about an expedient mysql table design. My experiences with mysql are not as good as with PHP at all, so I need a little help. The main problem is performance, so you may already asume that I'm not looking for poorly conceived table structures but some kind of denormalized ones, in order to improve the performance. So whats the best way to store the logged information? What you see here is the db structure from phpOpenTracker and that exactly is the way it shouldn't be. This normalized schema is horrible slow if you have to evaluate a lot of data. I know a lot about application design but not about db design, so it would be great if anyone could give me some approaches and/or examples.
Post Stats For Every File Looked Up?
I have 909,000 plus files in my database. Is it possible to post the amount of look ups , stats, for every file looked up? Example: People searching my database via my web site. If 700 people looked at a file, while viewing that file, it also tells you 700 look ups as part of the results? Code:
Search Design: Keywords Table (MySQL)
I'm trying to implement keyword searching in my database. Currently, I have a table containing all my entries with description and name. The existing sql search utilises fulltext search to find the relevant data from the description and name fields. I'm thinking of adding an additional keyword table that would have higher relevancy in returning results (currently, you'll be lucky to find any relevant entries). I have a separate table and the design is as follows: REATE TABLE `keywords` ( `id` smallint(5) NOT NULL auto_increment, `entryid` mediumint(9) NOT NULL, `keyword` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ; My other table that contains the description and name fields are `entries` My problem now is, how can I match the search keywords with that of the table `entries` and `keywords`? I want to avoid using more than one sql query for each search. For each entry in the table `entries`, there may be up to 20 keywords (e.g. 20 rows in `keywords` table). I'm open to another approach for the keyword table. The reason why I have a separate table is to facilitate management and hopefully be able to pool results in a way of a cloud for keyword statistics. Can anyone offer any advice/help in this regard?
Table Design Question (normal Forms)
I have a 5 web forms that share 7 fields. Then each form has separate fields depending on the form type. Here's the breakdown of the number of non-shared fields: form 1: 3 form 2: 13 form 3: 6 form 4: 1 form 5: 2 This table(a) will grow but it will never be gigs of data, just MBs of data. It would be MUCH simpler to have one large table with all the fields and add a form_type field to differentiate. In the long run, in your experience, is that the best way to do it or is it best to have one table for shared fields and separate tables for each form type?
Cocktail/Drink Mixes Table Design
I am thinking about developing a website that has a big databse of different alcoholic drinks, but the database and code is going to be huge so I was going to see if someone already has something like this I can use as a base or knows something I could use as a base... A example of something I want to make is at http://www.drinksmixer.com/. I have kind of sorted some MySQL tables I think. catergories: catergory_id catergory_name ingredients: drink_id ingredients_amount ingredients_name drink: cat_id drink_id glass_id drink_name drink_directions drink_comment glass: glass_id glass_name glass_description I think that is right... I don't if I have the ingredients rigt... Maybe I should just include that in the drinks table? ingredients table: 1 1.5 Tia Maria 1 1.25 Vodka And then when viewing a drink just grab the drink_id and match it against the drink_id in ingredients. Please help me atleast get the MySQL tables up to scratch as this is the main part. What else I should I do with these tables so that they are linked correctly? I am new to both PHP and MySQL and I want to the database tables finalized before I start any code.
Table Design For Diff. Levels Of Quiz
I have diff. levels starting from level 1 to say level 10 and each level has 10 questions and each question has four options. The Table for one level is as shown below: CREATE TABLE quiz( quizid tinyint(4) NOT NULL AUTO_INCREMENT PRIMARY KEY, quizlevel VARCHAR(50) DEFAULT NULL, quizquestion VARCHAR(255) DEFAULT NULL, quizoption1 VARCHAR(255) DEFAULT NULL, quizoption2 VARCHAR(255) DEFAULT NULL, quizoption3 VARCHAR(255) DEFAULT NULL, quizoption4 VARCHAR(255) DEFAULT NULL, quizanswer VARCHAR(255) DEFAULT NULL, UNIQUE KEY quiz_level (quizlevel) ); How to create the same for various levels?
Table Design, Normalization, Foreign Keys
In building a MySQL database what are the best practices to optimize speed and normalize? This is a food management database. Tables are such as item, manufacturer, brand, vendor, store location, nutrition facts, price, store dept, packaging, item type. And a few others. Given: 1. Tables are concise and have keys on all relevant search fields. 2. All tables have a prime, integer id field. (First field in table.) 3. Any repeating data field will be normalized. (Except for a few very minor areas.) Questions: 1. Use where field=field clauses to link the various tables only or mostly? 2. Use joins? 3. Use foreign field requirements to insure links and data completion? 4. Use triggers & procedures to do (3). 5. Have application programmers enforce the business logic? (Not really favored.) 6. Which approach is fastest? Where clauses to join, join clauses? Does it matter? Any recommended design tools? Am using MySQL Browser & Administrator.
Large Table, Fulltext, Design Decisions, Optimization
Maybe somebody can give me some suggestions, ideeas and help on my problem. I'm not a newbie with SQL but i'm not too great either, i usually don't care about optimization because i don't need to. I don't use forums too much and when i do i see replies like "you are a moron/idiot/stupid". If you want to reply with something like that for whatever reason at least tell me the reason you think that so i get enlightened . First of all let me explain the application I am developing so it is more clear. Brefly said, it will be a price comparison and social shopping site. Similar applications (sites) are froogle.com and shopping.com. The site's main features will be: * Product listing by category * Price comparison for a product using prices from several stores that sell it * Reviews for products * Reviews for stores * And many more that are not important right now First of all i have a problem regarding an important decision. This site must be multilingual so the question that arises is if I keep everything in a single database, in a single site or if i keep a database for each language. I am thinking to go with a database (or even a diffrent domain or subdomain - like uk.site.com , fr.site.com) for every language, and a main database that keep information thatis not language specific (like product attributes). The reason i'm inclined to do it like this is that a user from France will be interested only in products wich are being selled in France and if i keep all descriptions for all languages for all products in a single table i will have a huge table for no other reason than to keep everything in one place. So what is your oppinion regarding this? Please back up your oppinion with facts and reasons. The next problem is the huge ammount of data i will have to work with. Though the site will start verry small (on a shared/virtual host) if it will become popular (and i will do my best for that) i will not have a problem switching to a dedicated server or a cluster or whatever, but i want it to be as optimized as possible. So I was talking about large ammount of data as being a problem. That's because i did a benchmark on my PC and got really unplesent results (details about my PC configuration, servers versions, MySQL configuration and SQL of the database are at the bottom of my post so they don't get in the way): Records in products table: 844701 Times: (number in pharanthesis is the real one, i used MySQL Query Browser) ------ SELECT COUNT(*) FROM products; 0.0092s (0.0019s) SELECT * FROM products WHERE id=700123; 0.0183s (0.0349s) SELECT id, brand_id, title FROM products WHERE categ_id=3 LIMIT 0, 20; 0.0128s (0.0501s) Untill here everything is just great. As you see last query could be to get first 20 products in category with ID=3 (all products have categ_id=3) witch is usefoul for paginating. But when i change the same query LIMIT from LIMIT 0, 20 to LIMIT 700000, 20 i get the result set in 0.0096s (20.5210s), that is 20 seconds witch is unaccepted (even if most users don't browse but search instead). So what happens if i search for something with LIKE? First of all we need to find out total number of results the search returns: SELECT COUNT(*) FROM products WHERE title LIKE '%nvidia geforce%' 0.0304s (3.3100s) - that's alot Now let's get first 20 results: SELECT id, brand_id, categ_id, title FROM products WHERE title LIKE '%nvidia geforce%' LIMIT 0, 20; 0.0092s (0.0047s) - that's just great What about if we want to order by title column? SELECT id, brand_id, categ_id, title FROM products WHERE title LIKE '%nvidia geforce%' ORDER BY title LIMIT 0, 20; 0.0099s (26.7591) awww.... 26 seconds, but if I execute same query again... it's just 0.5 seconds (i belive it's caching or something) What if i want to see last results for this search? SELECT id, brand_id, categ_id, title FROM products WHERE title LIKE '%nvidia geforce%' LIMIT 231000, 20; 0.0029s (5.3272) (if i execute again i get the result in 2.8 seconds) Well i could throw in some caching techniques at the PHP side, maybe i will have justa few visitors, etc... but i will defenetly not use LIKE to search. I want to use FULLTEXT. So let's see some results with that: First let's get the number of results the search would return: SELECT count(*) FROM products WHERE MATCH (title, brief_desc, full_desc) AGAINST ('norwood nvidia geforce2'); i'm getting old here waiting for a result... it took 62 seconds and returned 406158 results. If i execute the query again it takes only 5 seconds but that's still alot. Now let's get the first results ordered by score: SELECT id, brand_id, categ_id, title, MATCH (title, brief_desc, full_desc) AGAINST ('norwood nvidia geforce2') AS score FROM products WHERE MATCH (title, brief_desc, full_desc) AGAINST ('norwood nvidia geforce2') ORDER BY score LIMIT 0, 20; it was fast 3.4 seconds Let's get last 20 results: SELECT id, brand_id, categ_id, title, MATCH (title, brief_desc, full_desc) AGAINST ('norwood nvidia geforce2') AS score FROM products WHERE MATCH (title, brief_desc, full_desc) AGAINST ('norwood nvidia geforce2') ORDER BY score LIMIT 406100, 20; this one took 6.7 seconds If i search for just 'geforce2' it does it in 0.6 seconds (beats me why so fast) Now i'm impressed. Yesterday i did a fulltextsearch (don't remember the string) and it took 200 seconds. Meanwhile i defragmented my HDD maybe that has something to do with it. So for now my questions are: * How can I optimize these queries and/or database structure? * Is this the best i can get out of it (on my computer)? * Any of you knows or can estimate what to expect from a 2xDual Core Xeon 3.2GHz (4 virtual CPU's), 2GB RAM, 2 SCSI HDD witch runs just Apache, PHP, MySQL, Mail on Linux? * Also how do these timings change in the case of a 10 milion rows table (instead of under 1mil like is now)? * Why is MySQL Query Browser showing me two times (one in pharanthsis witch is the real one)? I also have a database design issue. I need to keep attributes for products. For example for digital cameras i need to have Optical Zoom, Sensor Resolution, etc. I can have a table like this: CREATE TABLE prod_attrib ( id bigint(20) unsigned NOT NULL auto_increment, prod_id bigint(20) unsigned NOT NULL, attrib_id integer unsigned NOT NULL, attrib_value varchar(255) ); But with this approach if i have 10mil products and an average of 10 attributes for each product i end up with 100mil rows table witch i think is a problem. If i keep a table for each category of products (digital_cameras, mp3_players, etc.) and each of these has a specific structure i can't do an overall search. Any of you knows if the MySQL 5.1 XML support will help me with this? Please tell me how you would do this. Thanks alot to everybody who took the time to read all this. I'm waiting for your answers/suggestions. --------------------------------------------------- PC Hardware: ------------ CPU: AMD Athlon XP 2200+ (1.81GHz) RAM: 768MB DDR @ 400MHz MB: Gigabyte KT600 HDD: (1) Maxtor DiamondMax 9, 80GB, ATA 133 AND (2) Maxtor DiamondMax 10, 250GB, ATA 133. Both HDD are on the same ATA 133 Cable. OS: Windows XP Pro (SP2) Servers: Apache 2.0.59, PHP 5.1.4, MySQL 5.0 MySQL Instalation Details (Server Instance Configuration): ---------------------------------------------------------- - Server type: Developer Machine - Database usage: Multifunctional Database - InnoDB Tablespace Settings: default/unmodified - Concurrent connections: Decision Support (DSS)/OLAP (A number of 20 connections will be assumed) - TCP/IP Networking enabled, Strict Mode enabled (as recommanded) - Standard character set (latin1) - Installed as Windows service I have created a database (g2b_benchmark) for benchmarking purposes with the following structure: Note: I used a PHP script to populate the products table with 844701 rows. Some rows are repeating but not consecutivelly. CREATE TABLE `g2b_benchmark`.`brands` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(100) NOT NULL, `site` varchar(100) default NULL, `email` varchar(100) default NULL, `description` text, PRIMARY KEY (`id`), UNIQUE KEY `Index_2` (`name`), KEY `Index_3` (`description`(45)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `g2b_benchmark`.`categories` ( `id` int(10) unsigned NOT NULL auto_increment, `parent_id` int(10) unsigned NOT NULL default Ɔ', `lang` char(2) NOT NULL default 'en', `name` varchar(100) NOT NULL, `keywords` varchar(255) default NULL, PRIMARY KEY (`id`,`lang`,`parent_id`), UNIQUE KEY `Index_3` (`name`), ;category names must be distinct KEY `Index_4` (`keywords`), ;not important right now KEY `FK_categories_1` (`parent_id`) ;parent_id is the ID of the parent category ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `g2b_benchmark`.`products` ( `id` bigint(20) unsigned NOT NULL auto_increment, `categ_id` int(10) unsigned NOT NULL, `brand_id` int(10) unsigned NOT NULL, `lang` char(2) NOT NULL default 'en', ;i thought i throw in the table all products for all languages `title` varchar(150) NOT NULL, `brief_desc` varchar(255) NOT NULL, `full_desc` text NOT NULL, PRIMARY KEY (`id`,`lang`), KEY `Index_3` (`categ_id`), ;fast search/ordering by category KEY `FK_products_2` (`brand_id`), ;fast search/ordering by brand KEY `Index_5` (`title`), ;fast ordering by title FULLTEXT KEY `Index_2` (`title`,`brief_desc`,`full_desc`) ;i need fulltext search on these ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
MySQL Table Design Style With Foreign Keys
I just wondering what proper (My)SQL style would be for the following situation (which I made up for the sake of this)... You have a SpiceRack table and a Spice table. Each rack has many spices, in typical one-to-many fashion. So the Spice table would have rackID column. Easy. But let's say each rack has one default spice. So should the SpiceRack table have a column "DefaultSpiceID" or should the Spice table have a column "isDefault"? In one sense, I would say the former, to help enforce a single default spice. In another sense, I would say having two tables point to each other is ugly and just do some extra queries to enforce that there is only one with the latter. And while I'm at it... for that isDefault, and similar T/F columns... What's the best data type? SMALLINT (1) UNSIGNED, ENUM(0,1), or LONGBLOB?
Migrating Data From A Brain-dead Table Design To A Good One
I'm redesigning the database schema of one of our applications, and a lot of brain-dead decisions were made in the original design. In this specific example, a relational table was used when it was completely unneccessary. To be even more specific, we have a table for event times (call the table "times") and a table for people (call the table "people"). Each entry in the "people" table can only be associated with one event time (yes, if one person wants to sign up for more than one event time, there will be two separate entries, and we have good reason for that). However, the original creator made a rel_times_people table. In my redesign, each entry in the "people" table just has a "time_id" field to associate it with the correct event time. I'd like to migrate the time_ids from the rel_times_people table to the "time_id" field in the people table (as I said, there is only one entry in the rel_times_people table for each person). Is there a way to do this using a query (or perhaps using some feature in phyMyAdmin that I don't know about)? Or will I have to do this by hand (I'd REALLY prefer not to; there are over 1000 entries)?
Return Results Even If No Matching Row In Stats (was "Hmm, How To Get Around This?")
I have a MySQL query which works fine. This query references 3 tables, one of them (stats) may not have any entries for the particular user if they havent played any games. My issue is, if the user has no entry in the stats table the query doesnt work, and no results are returned. What would be the best way to get around this in MySQL so I get all the other information even if there is no entry for the stats table? SELECT p.id , p.player , p.team , p.mugshot , sum(s.goals) as goals , t.teamname , t.logo FROM league as p JOIN (SELECT goals , player_id FROM stats) as s on s.player_id = p.id JOIN (SELECT id ,teamname ,logo FROM teams) as t on t.id = p.team WHERE p.player LIKE '$playerselect' GROUP BY p.id , p.player;
DB Design
is there a tool to view a schematic of a MySQL DB I don't mean the .sql file I mean a pretty chart type schematic.
DB Design
is there a tool to view a schematic of a MySQL DB I don't mean the .sql file I mean a pretty chart type schematic.
DB Design
inserting a base64 string into a text field in my db. Again I may have design issues with this,but why in the world would it take 17 seconds to insert 1.7kb worth of text? I can read the record in milliseconds, why would it be different the other way around? What should I do to speed this up or design differently?
DB Design
I don't know why i am haveing a tough time on this one. i could just do it, but i want to do it the best way. Its for cars; make, model, engine, I'm just not convinced this is the way I should do it, because I also want to inlcude the years for the cars, and that vairies per model / engine combination I was going to have a cars table that would have the following, but need help with getting the years in there also. Should Each row in the CARS table have a YEAR field, so a 1993 VW Corrado SLC and a 1994 VW Corrado SLC would have their own rows, even though they are basicly identica cars? or should I do a single row with a from and to year field? i am leaning towards the one row for every configuration, since the data is small (mostly foriegn keys) and it would be precise and easy to look up between years, by model, etc.
Db Design
i am after a bit of advice here. i am creating a database that will apply to 3 venues london, paris and newyork with the potential for this to grow. i will be taking bookings for these three venues. Would it be more efficient to have sepearat tables for the bookings and rates and rooms for each venue eg london.bookings table paris.booking table etc or just have one big booking table and a location id. It is all theoretical but im working on the assumption that there will be only hundreds of site visitors each day.
Which Design Would Be Correct?
I have registered users, and these users can belong to a group. I'm trying to decide whether or not i should keep this group_id in the users table, or to create a cross referencing table and just keep the user_id and group_id in there. the cross reference table will allow for users to be associated with more than one group. and just keeping the group_id in my users table will allow me to avoid a few joins in some queries but only allow one user per group... any opinions on this matter? after typing this i feel as though creating the referencing table would be the better idea.. but i still would love to hear other opinions.
How To Design Database
I want to build a database for menu driven. like file --new --- html --- php --open --- html --- php --save edit --cut --copy --paste and like that. So,please help me how to start with designing the database as it can have n no. of levels.
DB Design Question...
I am almost done building a LAN-based Point-of-Sale system. I am somewhat new to mySQL, although I have worked with it for a few years now. I've only used mySQL for simple things like storing images and contact info for people that use my web-based contact forms. Now I want to do something a little more in-depth, and I need some suggestions. I want to be able to not only insert each invoice into the DB, but I also want to do so in a way that would allow me to provide various reports in a logical wy. As it stands now, I have 1 table that I call "invoices", where I store all the info that is found on a completed invoice: customer's information, date, sale type, and sale details, which is a semi-colon delimited list of all the items, quantities, and prices for everything they bought. Now, after building that table and playing with it a little, it seems rather cumbersome to work with. I will have to do a lot of parsing in order to make reports like 10 Most Popular Items, Total Profit for the last week, Total Item Cost of all tickets for month of Nov, for example, since all of those details are in a list in one column. How would you guys build your table/tables to make such data storage and it's subsequent access more logical with less overhead? A seperate customers table linked with an items table? Or would you do something different?
How Best To Design DB On MySQL 5.0 So That ..
I couldn't find this by a forum search. On my local PC, I have MYSQL 5.x and was wondering how best to design certain fields in my table that MySQl 4.1 ( my hosting service ) won't complain about. For instance, I noticed that when I pushed a PHP app from my local PC, up to my Host provider account, things failed because MySQl 4.1 didn't like 1024 character varchar's. Is there a guide somewhere that would make it easy to design an downward-compatible DB ?
|
TRACKER
