Efficient Database Structure
I have been developing a new website and i need help in deciding the best database structure for it. The site is basically a dating website with various modules like blogs, videos, comments, friends, photos etc. I have created a member table that stores all the basic profile info and created seperate tables for friends, photos, messages, blogs etc and MemberID as foreign key. Now on profile page, i want to display all info related to member like his profile info, his photos, friend list, messages etc and i have to execute 7-10 short queries on profile page for this. Also, i think Joins will not be much helpful as there is one to many relationship e.g there can be more than 1 photo for a member and i am saving each photo in a seperate record. Similar is the case for other tables?
View Complete Forum Thread with Replies
Related Forum Messages:
Creating An Efficient Database
i'm currently writing a web based catalogue system in php using a mysql database. the catalogue has a number of products in it from different brands. i would like to know if it is more efficient to have each brand in a separate table then a "master" table just listing the brand name and corresponding table or all the products in one large table and a "master" table listing each brand in the large table. the large product table would of course have a field to state which brand the product was from. the efficiency would be based on users being able to access the database via html browsers using php and also search the database.
View Replies !
How To Apply New Database Structure To An Existing Database, Without Overwriting Data?
I am writing a simple blog software application. We released a version of this software a while ago. It comes with a sql-tables.sql file, that you can just import into your mysql database and it will setup all the tables for you, for a fresh installation. We made the sql file by just doing an export I believe, without exporting the data that was in OUR database. Well, since then several people have downloaded and installed our software, and are using it. We are about to release a new version soon, however. Since it is all still under development, many things have changed in our database structure. We've added new tables, and modified or even removed fields from existing tables. So for the new release, we're going to include a new sql-tables.sql file obviously, so that for fresh installations they can still install it just like normal. However, the question is... how do you "upgrade" all the previous version's users's databases, without losing any of their data? Is there anyway to export just the structure of our current database... then somehow apply it so that it just goes through and changes another database to match the new structure? Or any ideas at all how to do this?
View Replies !
New Database Structure
I am wondering what the best way to setup the following database would be. Say, for example, I wanted to index all the grocery stores in the neighborhood. And then, I wanted to sort them by what variations of different items they sell. For example, I would have one table to handle all the different grocery stores: ID | Store Name | Location 1 | Max's Food Mart | 123 Main Street 2 | Food Time| 444 Bacon Street Now, the goal here is to take a list of different foods that each store sells, but each type of food has certain filters. For example. Bread can either be Wheat or White. Cereal can either be Lucky Charms or Cap'n Crunch The Bread and Cereal would be categories, and the options behind them would be the filters for those categories. So, for example, I could input the data for Max's Food Mart saying whether or not they carry Bread, and then whether or not it comes in white or wheat. What would be the best way to setup these Categories? Would each category get its own table with the different Filters being the fields? Or would there be a Categories Table and a Filters table?
View Replies !
Looking For A Database Structure
I'm making a database of ballets and am looking for an example or at least some inspiration for how to make my database structure. I need to be able to have a web page display the basic story of a ballet and some history, then I need to also be able to have information on all stagings and versions of the ballet from the original staging onwards. The thing with ballets, unlike most plays or musical compositions, is that many people will choreograph, (set the steps), the same story with the same music. However because the choreographer, (and usually set and costume design and stuff), is different it is a very different experience and this is very important to people looking for information about ballets.
View Replies !
Help With Structure Of My Database
i've got complete brain block at the moment I can't seem to get my head around the structure of my database. I'm setting up a product catalogue. When the user first goes to the catalogue they are presented with a list of categories, (category A, category B) etc. When the user clicks on one of the categories they are then taken to a list of category types I.E (white category A, black category A) etc. and from there they are taken to a list of sub categories I.E (white category A 9mm, white category A 16mm) etc. and then finally they go to a list of prodcts under that range. So for some products the structure is as follows: Category -> Category Type -> Sub Category -> Product But, not all products are structured the same way I.E on some categories the user is taken straight through to the range of products: Category -> Products on others there may be three levels Category -> Sub Category -> Products How do I go about structuring the DB, would I need to set up a table for each relationship I.E Category2CategoryType, CategoryType2SubCategory and then a lookup for the products?
View Replies !
Links Database Structure
I'm creating a 'Favourite Links' function on our Intranet, using PHP/MySQL. I'd really like some advice on the most efficient way of setting this up. Users login to the site and are either identified by a cookie or by a session. Their favourite links (ID, Title, URL, Description) will be displayed on their personalised homepage. We have about 230 users. I could set up a a table to hold the links for each user but that seems excessive. I could create the one table of links but with a field that records the owner/users ID but perhaps that would be a very memory-hungry query once the table gets really large.
View Replies !
Blog Database Structure?
I am trying to create a blog using asp.net. but i first need to create a database in sql for my blog. can any1 help me with what i would need to put in my database? like colum names etc?
View Replies !
Database Structure For Articles
I'm building an articles-system based on PHP & MySQL. Now I started and now I have troubles finding out the best database structure for the articles. I will be using 1 table named 'articles', and I'll post the article's contents in the table.
View Replies !
Database Structure For Blogs
What I'm working on right now is a type of blog system. So my current database table has your usually fields: id, title, body, category.... blah. What I'm looking to accomplish is a SEO friendly site using mod_rewrite for my url handling. As a result my blog entries will be in the url form of: mydomain.com/blog/date/<entryReference>.html So I want to include an EntryReference in my database table, something simple like a one word reference instead of displaying the entries 'id' in the url bar. When someone clicks on an entry my database query is going to be using this entryReference as part of it's WHERE clause. So do I want to make an additional table that consists of just entryRef and entryIDs, so that the query can lookup the entry's ID using the index-value EntryRef? Then use the entryID to look up the rest of the entry-information from the main blog table? Would this be faster than having my entryRef within the table that holds all the other blog-entry information and trying to just do one query?
View Replies !
Database/Table Structure
So I have a database of attorneys. All their basic info is in there, minus the areas they practice because we were waiting on them to get that to us. Now we've got it and I'm unsure how to proceed... I initally thought that nothing would need to be changed in attorney database, and I just needed to add a new table 'practice areas' with the following structure (or something like it: Table-practiceareas Fields=practiceareaname, attorneyname And then because 1 attorney could have multiple practice areas this would work fine...but each practice area could also have multiple attorneys obviously so now I'm stuck and don't know which way to proceed Any advice?
View Replies !
Database List Structure
Here is my situation: I have a table of `clients` (id, name, etc..) I have a table of `items` (id, name, etc.. ) each `client` can have 0-Many `items` assigned to them. How would I go about setting up the relation of this? should I create a new table with `item` id's and `client` id's, then query the DB for all `items` with a `client` id?
View Replies !
Database Structure Tutorial
I've bought a handful of books and i'm learning a lot. However, nothing i've found online or offline has left me feeling like I have a good grasp of DB structure and the proper way to build them. Are there any sites out there that have recommended database designs for various uses? The I learn best when i can see an example and emulate it with tweaks to fit my needs.
View Replies !
How To Structure A Image Database
I would like to create a simple php / mysql image database. But I'm a newbie and need some advises on how to structure the database. Functions Add images / select keywords for this picture Add keywords Search for images Structure Table "imageDb" Id added size description.......... Table "keywordDb" Id text Table "conntectDb" Id Idkeyword IdImage And the search could be something like: select * from connectDb there id = 2 left join........... and then I'm unsure....
View Replies !
MySQL Database Structure?
I am trying to wrap my head around how a MySQL database is structured. And I am having a little trouble with it… could someone please explain how it is structured? Or possibly advise me on to how to structure a database for an online store? Or point me to some online resources?
View Replies !
Printing A Database Structure
Does anyone know of a method to print every tables sructure using "DESCRIBE tb_name" without doing it table by table...? I would like to do this but there are about 50 tables and it would be a waste of time if I "DESCRIBE tb_name" for 50 tables and then still have to click on "Print View"... and then still click "File|Print" in the browser. I am sure there is a quick method of preparing a data dictionary. I have done it before but I cant remember how!?
View Replies !
Suggestion On Database Structure
I have two different apps that will be moving to the same server. I would like for both apps to share the same set of users, so that when a user registers for one app, they automatically have an account for the other app as well. My question is how best to handle this. Each app uses its own database. I could move the user table out to its own central database and have both apps access that database for user information when needed. Or, still with the central user database approach, I could have that database push any new data to user tables stored in the separate databases for each app. Or, another possibility, though one I am not fond of, is to make both apps share the same database, and just have the user table in that single common database. In your opinion, how best should I handle this?
View Replies !
Need Suggestions For Database Structure
I have been developing a new website and i need help in deciding the best database structure for it. The site is basically a dating website with various modules like blogs, videos, comments, friends, photos etc. I have created a member table that stores all the basic profile info and created separate tables for friends, photos, messages, blogs etc and MemberID as foreign key. Now on profile page, i want to display all info related to member like his profile info, his photos, friend list, messages etc and i have to execute 7-10 short queries on profile page for this. Also, i think Joins will not be much helpful as there is one to many relationship e.g there can be more than 1 photo for a member and i am saving each photo in a seperate record. Similar is the case for other tables?? So can you please suggest that is it a correct structure or can there be other better alternatives through which i can fetch all information using less queries. I am using mysql 5 database.
View Replies !
Help With Basic Database Structure
Just need a little help or advice on how to set my database up? I want the users to login with there email & password and be linked to a certain date and selection of photos, a great number of users will also be linked to the same date and selection of photos. My main problem is what to index and how best to link these users to the content in the columns? Will I need to create two tables? Or keep it as one? fn & ln are really just for a refrence. My columns: first_name last_name email password photos date
View Replies !
Database Table Structure Help
I am not sure how I need to design the following tables. If someone would provide me with a little bit of guidance I would appreciate it. Here is how I see it now: Host -id -name Guest -id -host_id(which is just the id from the host table) -name -email Sizes -id -guest_id(which is just the id from guest table) -sex -small -med -large -xlarge -xxlarge -requests I believe with the above structure I could have a user select a host, fill in their name, email and sizes and submit. Then using an insert (or update, as needed) statement I would insert the host_id, name and email in the guests table and a second insert would insert the size information. I think it looks right but I am not sure,
View Replies !
More Database Structure Questions
i have a site that has products in categories. products have only 1 category. categories have unlimited sub-categories and have a self join on the categories table. categories can contain products, sub-categories or both. i also have merchants for each product. finally, a user can have a product list (list of products they have selected). A user can select 1,000's of products (this is not a shopping cart). when a user is browsing the categories, merchants and products, i want them to be able to filter their results by merchant, category or if the product is in their list. what i have so far (see below) is working but i want to know if this is the best way to deal with the problem and if it has potential scalability issues if i were to make more filters based on for example, brand or something else related to the product. cat2merchant this table allows me to track the categories a merchant's products exist in because some categories don't have products. more info here. this table would contain a row for every category and sub-category that the product exists in. category_id merchant_id categories id name parent productlist2product this keeps track of which products the user has added to their product list associated with their product list id (another table) productlist_id product_id merchants id name products id name merchant_id category_id cat2productlist2merchant this table helps me to filter results if user wants to just view all cats and merchants that contain their selected products. also, if a product exists here: cat1 > subcat2 > subcat3 > product, this table would have 4 rows (1 for each category). category_id productlist_id merchant_id I have considered combining the cat2productlist2merchant table with the productlist2product table but then that table would be filled with some empty product_id's. i've also considered making a table for each relationship a productlist_id has with other filterable tables such as merchants, categories (possibly brands).
View Replies !
Structure Of Database To Prepare Report
I'm attempting to create a database which will take information from one (perhaps two) tables and utilize that information to return queries to a report designed in Excel. The general idea I have is this: The "primary" or "source" table is easy. This is a trial balance (account number as a primary key, description, amount). I'll need table(s) to denote which account numbers belong to which page/line on the report. I'm not sure how many pages the report will be, but let's say my Excel workbook would have 50 sheets. My question is this: What would you recommend for a table structure. The way I see it, I could have a single table listing each page and each line on each page (each identified by a unique id), then have another table listing each account and each page/line id. The other option I see is to have a separate table for each "page," but I'm not sure I see a benefit in this. My first instinct is to use the first method I mentioned.
View Replies !
Blogger Database Design Structure
I'm trying to host my own blogger site. Will anyone tell me where I can find out some of the database design structure for a blogger website? How do many tables and fields do I need? I realized a lot of these will depend on much information or flexibility I want to allow the user but I need to know the basic requirements. Any help is appreciated.
View Replies !
Database Structure For Online Store
I have several questions about the structure of a database for an online store, so instead of ask each one, if someone could point me to a good tutorial I think that would answer a lot of my questions. My main questions are how to store the information after the customer buys an item. I'd prefer it if the keys were automatically generated, how the primary keys of the customer and item are setup so that if items are in different tables, they won't conflict with each other, etc.
View Replies !
How To Update Database Structure Automatically?
I have made modifications to my local database (adding a few columns here and there) and i would like to reflect the changes to the production database. So far i did it by manually editing sql exports, but i would like to know if there is a mysql command that would allow me to batch update table structures without altering the existing data. Something like UPDATE TABLE `databaseName`.`existingTableName` ( `existingField` TEXT NOT NULL , `newField` INT NOT NULL ) PS: using mysql 5.0.51a
View Replies !
A Database Structure For Dictionary Data
I'm creating a medicine dictionary and wanted to ask for Your help to how to create the logical structure for the data. And how to ask for them. By now with my basic knowledge I'm thinking a structure lie this: #1_table {id1, word} //for the first language #2_table {id2, word} // for the second language #3_table (id1,id2) // for the translations As to each word can be more than one translation I thought that only one table like {word1,word2} would not be the best variant. The data will be about 40 000 words in one language and about as much in the other one. And here are the questions: 1) What structure would You advise me? 2) With what queries should I ask for the translation to the other language (if I know the word, not the id)? 3) Will it slow down the code with 40 000 entries (how about 80k) in one table?
View Replies !
Customized Database Directory Structure
The default database location is usually located on the %datadir%/databasename folder, where datadir is a string inside the my.ini file. the datadir will usually have the value "%Application Data%/MySQL/MySQL Server 5.1/Data/". Since I am thinking about offering a SaaS solution to my customer, I want to have my database directory slightly different. I want to have my database in the folder %datadir%/%companyname%/%databasename%. where the %companyname% is determined from the URL, i.e., %companyname%.mycompany.com is the URL for one of my customer.
View Replies !
MySQL File / Database Structure
I have a question about the file structure of mysql. Where are the users as e.g. root stored? When I make a fresh install of mysql I have the datamysql database in place then I add a secound database e.g. test. Is the root user stored then in both databases or is he only in the mysql database or somewhere else? And if I change the password for the user root with the cmdline tool mysql is it necessary to select a database before with "use XXX" or not? And if I wanna start from scratch on an running mysql server and therefore delet the datamysql database is the root user lost then and how I recreate all I need then?
View Replies !
Database Structure For League Table
I'm about to try and create a league table with php/MySQL to track games and statistics for an online poker group. Obviously, the most important thing to think about is the structure of the database, but I can't seem to find the best way to do this and was hoping someone could help provide a few ideas on how to keep it clean. At the end of the day I should show a leaderboard, but also be able to display the results of past games - and this is what I am having trouble with when thinking about the structure. I was thinking of a 'games' table to show: Tournament ID. Number of players. Date of game. Start time. End time. This information would then be put into the 'games' table and another form would be produced using the 'number of players' figure to specify the number of input boxes, thus, if there were 7 players we would have 7 boxes. Code:
View Replies !
Database Structure And Associating Fields
I've been searching and reading through a few older posts but I still need a hand to make sure I am doing this the best way. I have a product catalog with three tables:brandcategorydescriptionSince my brands and categories are unique I have 2 columns in each of these tables for "id" and "name" (so when I add a new brand it will have it's own ID and unique brand/category name). My "description" table has 4 columns including "description_id", "brand_id", "category_id", and "description"... (the brand and category ids come from their respective tables - is that too repetitive?) I have an update form that I am building for content management with two drop downs (populated by the brand and category data) with a text area for the "description". (my goal was to keep brands and categories consistent by isolating them) At this point I am able to add brands, add categories, and add descriptions (with their respective brand and category IDs) but is this way of setting up the data succifient? When I am displaying dynamic pages now, how do I associate my brand ID and category IDs with the actual brands and categories? This is probably a simple question but it's tripping me up at the moment.
View Replies !
Database Structure Design Question
If you were putting together a system where many agencies could sign up and have their own section, and then within each agency they could have many notes or tasks assigned to their users, would you create a new database for each agency or just use an agency ID and have all entries in one database. For example, agency 1 may have 1000 notes assigned to their staff, agency 2 may have 500 notes assigned to their staff. Would you have all of these notes in 1 database or would you create a separate database for agency 1 and agency 2. There is no limit to the number of agencies that can register, and there is no limit to the number of notes each agency can create, so in theory we could be storing many millions of records. If I put everything into one database am I likely to have too much slowdown when pulling the data using PHP? Also, do MySQL tables have a maximum number of rows, or can I continue adding records until it gets so slow it becomes unusable? I guess I'm asking which setup would be best from both a speed and a security point of view. Any suggestions would be great.
View Replies !
Copying Database Structure And Content To Another (forums)
It's me with another query! I need some help with MySQL. Basically, we are handling two sites and each site has a forum. The 2nd one is new and it isn't even open to everyone else yet. Now, the situation is this... We want the structure/posts/users from the other forum to be imported into the new one. Now, I've tried a database export/import but then an error message comes up saying: #1044 - Access denied for user 'banana'@'%' to database 'mango_forum' Those aren't the real user and database names for obvious reasons. I'm using PHPMyAdmin. Has anyone encountered this before?
View Replies !
Logging Database Structure To Text File
i am trying to help a colleague who has been given the task to provide the structure of a MySQL database. That is, table names and column names within each table. Unfortunately, there are several hundred tables :-( We can easily get the table names using the DESCRIBE MySQL command. However, to use this command on each table to output column names is going to be tedious and lengthy!
View Replies !
Friends/buddy System Database Structure
I'm about to integrate a friends system into my website and I was wondering what the best schema would be for this kind of thing. The friends system will be an invite only system, so a user must send an invite to the user he wishes to add to his friends list before that friend can be added. The invites will be stored in an invites table, but I was wondering on how best to record the friendship between two users (once an invite is accepted). I was going to follow vbulletin's schema of which stores this information in a single field, in the members table. This field consists of the user id of each friend, seperated by a space. My second option is to set up a seperate table which stores every single friendship. Although this would be easier to manage/handle (since each friendship has to be mutual), I'm a bit worried about the excessive amount of records this would create. Would anyone know what the best approach would be? I'd be very grateful for your input.
View Replies !
Command Line More Function? User Friendly Way To Show Database Structure?
In the MySQL command-line interface, is there a way to pause (like the more function in windows) the data? for example, SELECT * FROM TABLE shows a huge list of data that overflowed my command prompt in windows. I can't see the ones on top. Another question is, how do I download or view the tables and its values in a database in a more user/graphic friendly way? I have a huge list of tables amd data in the tables, but using the command-line interface is basically a horrible waay to look at the data structure.
View Replies !
Adding Different Size And Price Options To An Online Store Database Structure
At the moment I have an online store all up and running that sells oriental rugs, but based on the assumption that each design is available at a single size and a single cost. The site has an admin section where the site owner can add new designs using a fairly simple insert form, filling in various details including size and cost, which adds a new record in a simple 'Carpets' table. What would be the best practice to add in the ability to specify different sizes, with different prices? I'm presuming I'm going to need at least one new table, ie split it out into Carpets and Carpet_Sizes perhaps? Carpets Carpet_ID Design_Ref etc Sizes Carpet_ID Size Price With a query Carpet_Sizes : Carpet_ID, Design_Ref, Size, Price 1, ABC1, 6x4, 599 1, ABC1, 2x3, 199 1, ABC1, 5x4, 399 2, ABC2, 6x4, 899 2, ABC2, 2x3, 599 3, ABC3, 5x4, 799 3, ABC3, 6x8, 999 What would be the easist way to allow the owner to add this info when adding new designs? Using checkboxes perhaps?
View Replies !
What's The Most Efficient Way?
I've done a database scheme and wonder what the most efficient way of storing certain entries would be. The site will have news, reviews and tutorials and these will be under the same categories. So I'm wondering what the best way to do design the database scheme would be. Have one table like I've done now in the "content" table with the posibilty to differentiate the entries with the "post_type" field or have three different tables? Below is the table "content". Field Type Null Default post_id int(10) No post_date datetime No 0000-00-00 00:00:00 post_text text No post_title text No post_cat_id int(4) No 0 post_description text No post_name varchar(200) No post_type int(4) No 0
View Replies !
How Efficient Is In() , And How Much Is The Most That You Should Put In An In()?
I am just wondering as to the efficiency of the in() function in MySQL. select field1,field2,...,fieldn from table where id in(1,2,3,4,5,6, ... , n); assuming: id is an indexed field. table is a VERY big table (100 000+ - 1 million+ records) what do you think is the largest number of values you could pass to the in() function without completly flattening your server?
View Replies !
Most Efficient YES/NO...
I) Table_A has fields Data_1, Data_2, Data_3, and Data_4; I need to determine whether value 'X' is present (at least once) in any of the Data_N fields. All I need is a YES/NO answer: YES, 'X' is present (at least once) in one of the Data_N fields of Table_A [Record number(s) not required!], or NO, 'X' is not present in any of the Data_N fields in any of the records in Table_A. II) Similar scenario, but this time Table_A has fields RecID, Data_1, Data_2, Data_3, and Data_4. I need to determine the RecID (none, one, or more) of every record that has value 'X' in any of the Data_N fields. I'm looking for the query that will be fastest and most elegant to implement.
View Replies !
Quickest And Most Efficient
i run an online game. I want to give each user a ranking, based on how high thier score is.If for example, i had 20,000 players, i dont want to have to update them all one by one, it may strain the server and take a long time. Is there another way i can assign a rank number (rank 1 has the top score and so on)
View Replies !
Efficient UPDATE
I have a table with the following structure; CREATE TABLE my_table ( id_1 int(11) NOT NULL , id_2 int(10) NOT NULL , stauts tinyint(1) NOT NULL DEFAULT 0 , PRIMARY KEY (id_1) ) Engine =InnoDB'; The table currently has arround 100,000 entries. When I try to run variations of the following statement it is taking around 4 seconds per query; UPDATE IGNORE my_table SET id_1 = 74240, id_2 = 5 I need it to be running a lot faster than 4 seconds per query as I need to update upwards of 100,000 records a day! My server is fairly beefy, a 3 gig dual core opeteron and is generaly running below 1.0 load.
View Replies !
Efficient Tables.
I am starting out on a project where I need to store GPS information. The data consists largely of a series of "Points" each consisting of a longitude,latitude and elevation. On a typical "route" there could be hundreds of points. My question is how can I efficiently store this information. It does not sound sensible to normalise this and add hundreds of rows to a table for each "route". Sample data is along the lines of : - <trkpt lat="54.016942977905273" lon="-1.4903640747070313"> <ele>82.330078125</ele> <time>2006-09-03T07:35:41Z</time> </trkpt> - <trkpt lat="54.016938870772719" lon="-1.490332055836916"> <ele>0</ele> <time>2006-09-03T07:35:42Z</time> </trkpt>
View Replies !
Efficient Storage Of IP Address
I am establishing a database for the purpose of logging access to my secure webserver and am wanting to make the database as efficient as I can because it will be doing a lot of work when the site goes live. What is the most efficient way in a MySQL table to store remote IP addresses? What data type should I use? Should I just go with a basic VARCHAR(15) to allow for 4 sets of 3 digits with 3 decimal separators, or is there a better way?
View Replies !
Efficient Placement Of Fields
is there any noticable efficiency is ordering the position of the field types?. Like say i place the join keys in a table at the end, and varchars and text at the top of the table does it really matter? Also when i do a query like select * from table where id IN (1), where 1 is usually a primary key int is that quicker than doing where id=1 or is there no difference and is it handling the int as a string or as an int?
View Replies !
|
TRACKER
