i'm a beginner in SQL world and am just starting to learn MySQL. i know how to call out the data using SELECT but i have some troubles in calling out data from multiple tables. below are the tables in one database:
1. category [cat_id | cat_name] //i have a total of 163 categories now.
2. company [client_id | cat_id | company_name | telephone] //thousands of clients will be put into the database.
3. contact [client_id | contact] //person to contact.
4. address [client_id | street | postcode | city | state] //company address
5. fax [client_id | fax] // fax number
6. cellphone [client_id | cell_num] //cellphone number
7. URL [client_id | url] //company web site
8. email [client_id | email] // company email address
one thing i don't like is that the data given are very inconsistent. every company had put at least a company name and a telephone number in the papers, that is for sure. however, some just didn't give their information any further, like contact person, address, fax number, cellphone number, URL and email. but i have to get those extra data listed in the database if i have them in the papers. btw, some even have more than two addresses and two telephone numbers as they have different branches, but under the same company name.
what do you think about the data types distributed into the above eight tables? do i need to correct anything before i go on with query to call out the data? as far as i know, there will be data redundancy in the "address" table because postcode, city and state are not unique. do i need to create another three tables just for them?
now comes the hardest part, the query either return thousands of repetitive records or do not show anything at all!
i'm pretty sure there must be a big problem in my query. what i wanna do is to show all the records ordered by category or company name or city, no matter how many pieces of data the company have.
for your information, i'm helping in creating an online version of yellow pages.
please advice. your help is much ppreciated.
1. category [cat_id | cat_name] //i have a total of 163 categories now.
2. company [client_id | cat_id | company_name | telephone] //thousands of clients will be put into the database.
3. contact [client_id | contact] //person to contact.
4. address [client_id | street | postcode | city | state] //company address
5. fax [client_id | fax] // fax number
6. cellphone [client_id | cell_num] //cellphone number
7. URL [client_id | url] //company web site
8. email [client_id | email] // company email address
one thing i don't like is that the data given are very inconsistent. every company had put at least a company name and a telephone number in the papers, that is for sure. however, some just didn't give their information any further, like contact person, address, fax number, cellphone number, URL and email. but i have to get those extra data listed in the database if i have them in the papers. btw, some even have more than two addresses and two telephone numbers as they have different branches, but under the same company name.
what do you think about the data types distributed into the above eight tables? do i need to correct anything before i go on with query to call out the data? as far as i know, there will be data redundancy in the "address" table because postcode, city and state are not unique. do i need to create another three tables just for them?
now comes the hardest part, the query either return thousands of repetitive records or do not show anything at all!
i'm pretty sure there must be a big problem in my query. what i wanna do is to show all the records ordered by category or company name or city, no matter how many pieces of data the company have.
for your information, i'm helping in creating an online version of yellow pages.
please advice. your help is much ppreciated.