Re: Design help
Neil, Am 21.04.2013 08:47, schrieb Neil Tompkins: Using joins I can obtain which country each city belongs too. However, should I consider putting a foreign key in the CITIES table referencing the countries_id ? Or is it sufficient to access using a join ? It depends. Adding a reference to countries into the cities table would break normalization and would require you to maintain the correct reference (e.g. through the use of ON UPDATE triggers). It might be beneficial to do so if you have a high number of queries for cities filtering for countries - having a direct reference obviously would spare you a JOIN execution and at least two index lookups. In your current example however, the data set will typically be small enough (in the order of 1,000 - 10,000 cities) so the query performance certainly would not be that much of an issue to justify the denormalization[1]. [1] http://en.wikipedia.org/wiki/Denormalization -- Denis Jedig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Design help
Many thanks for your response. Can yo u offer any advice with regards usage of country_codes eg gb and regions, cities etc ? I've been reading up on http://en.wikipedia.org/wiki/ISO_3166 etc. Should I be looking to use a Surrogate key for countries ? Or the country code like fr for France ? Same with regions/states and cities and districts ? On Sun, Apr 21, 2013 at 9:28 AM, Denis Jedig d...@syneticon.net wrote: Neil, Am 21.04.2013 08:47, schrieb Neil Tompkins: Using joins I can obtain which country each city belongs too. However, should I consider putting a foreign key in the CITIES table referencing the countries_id ? Or is it sufficient to access using a join ? It depends. Adding a reference to countries into the cities table would break normalization and would require you to maintain the correct reference (e.g. through the use of ON UPDATE triggers). It might be beneficial to do so if you have a high number of queries for cities filtering for countries - having a direct reference obviously would spare you a JOIN execution and at least two index lookups. In your current example however, the data set will typically be small enough (in the order of 1,000 - 10,000 cities) so the query performance certainly would not be that much of an issue to justify the denormalization[1]. [1] http://en.wikipedia.org/wiki/**Denormalizationhttp://en.wikipedia.org/wiki/Denormalization -- Denis Jedig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Design help
Hello, Many thanks for your response. Can yo u offer any advice with regards usage of country_codes eg gb and regions, cities etc ? I've been reading up on http://en.wikipedia.org/wiki/ISO_3166 etc. Should I be looking to use a Surrogate key for countries ? Or the country code like fr for France ? Same with regions/states and cities and districts ? I do not think you need surrogate key for country. Country code is 2 letters, so if you use char(2) charset ASCII you only need 2 bytes for that. That is ok and your queries would be easier to read. You do not need surrogate keys for US states also but if you speak about states in general you may need key (many countries over the world have states) The same is about cities: city name is too big to be used as primary key, and there may be many cities with similar names. Ilya. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Design Help Needed
HI Sudheer, THIS DESIGN IS BASED ON MY MANY-TO-ONE DIRECTION PATTERN OF DATABASE NORMALIZATION DESIGN... PLEASE MODIFY/CORRECT IT ACCORDING TO YOUR TASTE. AS A JAVA DEVELOPER I'M USING HIBERNATE FOR MY CREATE-UPDATE-DELETE(CUD) AND DIRECT JDBC FOR MY QUERIES(R). YOU MAY VARY. HERE, HOPE THIS WILL GIVE YOU AN IDEA. LEGEND: X-TABLE, L-LONG, S-STRING/CHAR(?), T-DATETIME _ID-PRIMARY_KEY, REF-FOREIGN_KEY X:CONTACTS_AND_ADDRESSES L:CONTACT_AND_ADDRESS_ID S:WEBSITE S:EMAIL S:MESSAGING S:TELEPHONE S:MOBILE S:LAND_ADDRESS T:CREATED/MODIFIED X:INDIVIDUALS L:INDIVIDUAL_ID S:USERNAME S:PASSWORD S:DISPLAY_NAME S:PICTURE_URI S:SECURITY_QUESTION S:ANSWER T:CREATED/MODIFIED X:INDIVIDUALS_CONTACTS_AND_ADDRESSES L:INDIVIDUAL_CONTACT_AND_ADDRESS_ID L:REF_INDIVIDUAL_ID L:REF_CONTACT_AND_ADDRESS_ID T:CREATED/MODIFIED X:ESTABLISHMENTS L:ESTABLISHMENT_ID S:BUSINESS_NAME T:CREATED/MODIFIED X:ESTABLISHMENTS_CONTACT_AND_ADDRESS L:ESTABLISHMENT_CONTACT_AND_ADDRESS_ID L:REF_ESTABLISHMENT_ID L:REF_CONTACT_AND_ADDRESS_ID T:CREATED/MODIFIED X:ESTABLISHMENTS_OF_INDIVIDUALS L:ESTABLISHMENT_OF_INDIVIDUAL_ID L:REF_ESTABLISHMENT_ID L:REF_INDIVIDUAL_ID T:CREATED/MODIFIED X:CUSTOMERS L:CUSTOMER_ID L:REF_INDIVIDUAL_ID T:CREATED/MODIFIED X:PARTNERS L:PARTNER_ID L:REF_INDIVIDUAL_ID T:CREATED/MODIFIED X:EMPLOYEES_POSITIONS L:EMPLOYEE_POSITION_ID S:ROLE S:SPECIFIC_RULE S:HOWTODO T:CREATED/MODIFIED X:TIME_SCHEDULES L:TIME_SCHEDULE_ID S:APPLIED_TASK T:APPLIED_TIME L:REF_EMPLOYEE_POSITION_ID T:CREATED/MODIFIED X:EMPLOYEES L:EMPLOYEE_ID L:REF_INDIVIDUAL_ID L:REF_EMPLOYEE_POSITION_ID T:CREATED/MODIFIED GOOD LUCK, HOPE THIS HELPS. -Melvin Sudheer Satyanarayana [EMAIL PROTECTED] wrote: Hi, I'm creating an application for my web site. I want help in designing database tables. Currently I'm starting with user management system. The web site would have these types of users 1. Customer account 1a. Individual account. This user would be an individual with username, password, billing address, account security question, answer and few more fields. 1b. Business account. Each business account would have many users. Currently I have not decided the number of users for this type of account. It may be 10 users in the beginning. I want to keep an option to increase the number of users for business accounts. The business account will have, business name, billing address, account security question, answer, and few other business details. Each user within the account will have username, password, first name last name, mobile number and other personal details. 2. Partner account. These are similar to 1b business account type. 3. Internal account. These are employee accounts. Each user will have username, password, first name, last name, department, phone number and few other fields. Ideally how many tables should I create? What are the types of keys(primary and foreign) Other modules of the application I would be developing in the future are, contact management, shopping cart, mailing lists, customer support, etc. I have MySQL 4.1 on the server. Hope my question is clear. PS: I'm new to databases. Thanks for the help, Sudheer. S Binary Vibes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV.
RE: Design Help Needed
Hi Sudheer, First of all there a number of ways to design this database. You will need to choose the one that you feel suites your needs best. Here one possible design. Because you have different type of users/accounts, it looks like ACCOUNT_TYPE table is needed ACCOUNT_TYPE table Account_type_id - autoincrement, PK Account_type - varchar(25). ( Values in this field are: Individual,Business,Partner,Internal) Because you have user information to store: USER_INFO table User_id - autoincrement, PK Account_type_id - integer, FK (foreign key to ACCOUNT_TYPE) Fields of personal/Business information to follow - (First, Last Name, .) Because you have web site security info to store: WEB_SITE_SECURITY_INFO table User_id - integer, FK (foreign key to USER_INFO) Security_question Security_question_answer Fields of security information to follow You can grow the database as you add module, but as much as you can foresee and design ahead it would be better Mikhail Berman -Original Message- From: Sudheer Satyanarayana [mailto:[EMAIL PROTECTED] Sent: Thursday, June 14, 2007 1:50 AM To: mysql@lists.mysql.com Subject: Design Help Needed Hi, I'm creating an application for my web site. I want help in designing database tables. Currently I'm starting with user management system. The web site would have these types of users 1. Customer account 1a. Individual account. This user would be an individual with username, password, billing address, account security question, answer and few more fields. 1b. Business account. Each business account would have many users. Currently I have not decided the number of users for this type of account. It may be 10 users in the beginning. I want to keep an option to increase the number of users for business accounts. The business account will have, business name, billing address, account security question, answer, and few other business details. Each user within the account will have username, password, first name last name, mobile number and other personal details. 2. Partner account. These are similar to 1b business account type. 3. Internal account. These are employee accounts. Each user will have username, password, first name, last name, department, phone number and few other fields. Ideally how many tables should I create? What are the types of keys(primary and foreign) Other modules of the application I would be developing in the future are, contact management, shopping cart, mailing lists, customer support, etc. I have MySQL 4.1 on the server. Hope my question is clear. PS: I'm new to databases. Thanks for the help, Sudheer. S Binary Vibes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Design Help Needed
Because you are a novice to data base design, you have fallen into a common trap. If you think about an array, you don't want to store multiple users in a row, you want to store them in a column. In other words, you want to have one table that stores businesses (once per business, probably) and another table that stores the associated users. You use a unique business ID to find all of the users for a particular business. Once you start thinking that way, you'll start to get the hang of it. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Sudheer Satyanarayana [mailto:[EMAIL PROTECTED] Sent: Thursday, June 14, 2007 1:50 AM To: mysql@lists.mysql.com Subject: Design Help Needed Hi, I'm creating an application for my web site. I want help in designing database tables. Currently I'm starting with user management system. The web site would have these types of users 1. Customer account 1a. Individual account. This user would be an individual with username, password, billing address, account security question, answer and few more fields. 1b. Business account. Each business account would have many users. Currently I have not decided the number of users for this type of account. It may be 10 users in the beginning. I want to keep an option to increase the number of users for business accounts. The business account will have, business name, billing address, account security question, answer, and few other business details. Each user within the account will have username, password, first name last name, mobile number and other personal details. 2. Partner account. These are similar to 1b business account type. 3. Internal account. These are employee accounts. Each user will have username, password, first name, last name, department, phone number and few other fields. Ideally how many tables should I create? What are the types of keys(primary and foreign) Other modules of the application I would be developing in the future are, contact management, shopping cart, mailing lists, customer support, etc. I have MySQL 4.1 on the server. Hope my question is clear. PS: I'm new to databases. Thanks for the help, Sudheer. S Binary Vibes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: design help
Hi dn, thanks for replying. I suspect you're right about my 'problem' - I'm probably misunderstanding some simple SQL construct :-) Let's say I have the following tables: parts (partid int, partname varchar(255)) cats(catid int, catname varchar(255)) parts_cats (id int, partid int, catid int) To track which categories are assigned to which parts. I can use the following query to return parts and associated categories: SELECT partid,catid,partname,catname FROM parts_cats LEFT JOIN parts ON parts_cats.partid=parts.partid LEFT JOIN cats ON parts_cats.catid=cats.catid However, as I won't know how many categories are assigned to a given part, how can I: a) Form a query that only returns a list of parts that match a specifc set of categories (i.e. parts_cats has records where catid=1, 3 and 7 for any given partid) b) Know how many unique records I'm dealing with. Using the SQL above, if a part has, say, 3 categories then 3 rows would be returned. 4 categories and 4 rows are returned, etc. - how can I tell how big my result set is so that I can page through it using LIMIT? Hope I've explained myself clearly! - thanks in advance, james ps Completely OT question - how do you find your HomeChoice service? Been thinking about getting it for a while. j. At 09:39 10/02/2002 +, you wrote: The separate table idea is standard relational theory. The tbl schema might involve (1) a unique Id column (with AUTO_INCREMENT), (2) a 'copy' of whatever data from the existing tbl(s) to sufficiently identify the particular response/select box, and (3) one of the select box return values - there would then be as many rows as there were responses in the select box (n=3 in this example). The second of these must be sufficient information to uniquely identify which table entry in the original table relates to the particular select-response - these are called Foreign Keys. To retrieve all of the select-responses, you would indeed issue a SELECT...JOIN and thus be able to reassemble the HTML select command, for example. Yes you are correct (in your example) such a SELECT will produce a resultset of 3 rows. This will allow you to search on a single, or multiple (concurrent) select-responses. As mentioned, this is SOP. You say you want multiple responses but not multiple entries, but not why. Perhaps most of the 'problem' lies hidden there? =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php James Carrier Bullet Online :: Aim Higher [http://www.bulletonline.com] 41b Beavor Lane, London W6 9BL Tel +44 (0) 20 8834 3442 Fax +44 (0) 20 8741 2790 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
