I am developing a portal using php and mysql. I am facing a problem in the database design because my database is going to take million of records. I am not very much experienced in database design. So I request some experienced people to come forward for my help. I am not sure my database design is correct or not and is this database schema capable to take millions of records.
My portal basically consists of three types of search.
1. Business or category search
2. Company’s name search
3. Person’s name search.
If someone clicks on any of these three, it opens a panel, in which there are two fields,
1. Select city - ( I want to start with at least 20 cities and then extending it to over 200 or more)
2. Enter query. - ( As you type a character it starts showing hints. )
My database schema is like this.
Categories Table
1. Category Id
2. Category Name
3. Related Companies (companies id).
Companies Table
1. Company Id
2. Company Name
3. Address 1
4. Address 2
5. Address 3
6. Address 4
( I am thinking to split city pin and state in different tables as these fields are going to repeat. )
7. City
8. Pin
9. State
10. Phone 1
11. Phone 2
12. Mobile 1
13. Mobile 2
14. Fax 1
15. Fax 2
16. Email 1
17. Email 2
18. Website 1
19. Website 2
20. Contact person 1
21. Contact person 2
22. Designation
23. Business details
So the problem lies in business or category search as I want to relate companies with the categories in which they fall, when user makes a business search it shows hints from the category list and the user can either select from the hints or type in his own query, after which the search function tries to find out the companies related to that particular category, display them and moreover it shows the related categories list.
What I am doing is I am putting the ids of the companies related to a particular category in the related companies fields.
For example:
Category Id | Category Name | Related Companies
1 | Web development | 1 2 3 12 10
So problem is the hints which appears when user types includes categories which doesn’t contains companies of that particular city but have companies from other cities. i mean i user selects city 'abc' and enters category computer but there is no company in abc city in realtion with computer, so i want hint shall not show, if the category don't have a company from the city abc.
shinejones
0
Newbie Poster
shinejones
0
Newbie Poster
hi everyone out there..
is there someone to help me out..
or my question is not clear..?
please reaply...
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.