mohcis

Hello there, The vendor data get updated by an employee of the vendor. Like, whenever there is a sale, an employee will go online, input the information relating to the sale. Yes, it looks like I will need a user management system. Also, each vendor may need a full report on its activity at the end of the month. This is bunch of table. I am not considering graphs at this point, that will be over my head. The format of the report will be the same (about 50 pages) and should generate specifically on the activity of each vendor. There is a need for tracking these reports over time. Will I need a different database to store the reports in pdf format? I need help breaking down this project into doable pieces. Thanks!

The current dataset is updated as vendors process sales. So pretty much it can change anytime. A vendor can be deactivated as well. I forgot to mention that.

You have more experience than me, I have been working with PHP for only 1 month.  Thank you for your input, I hope to get more feedback on this. As for the database size, I have about 12 table with more than 3 millions records. So, I have a minimum of 36 millions records in total. I am not sure if MYSQL will handle it, but I think that with a better database design, it can be done. The whole question is to find the right design. This is what I was previously thinking: Have all 36 millions records in one table. Then create all the possible combinations of customers' profiles by concatenating the fields. For instance, I will concatenate Cust-Id, State, County, zipcode, vendor-Id and product to identify the products sold in a particular zip code by a particular vendor. That way, I can run a select querry from the DB. But if this has to run through 36 millions records each time someone uses the web application, I can anticipate trouble down the road. Thanks for your input. Cisse

Thank you roopurt18, this gives me another perspective. I did not think of creating the relationships as you did. This helps alot. I am not sure how big the purchase history table is going to be because the customer table alone has more than 3 millions records. Is there a potential to slow down mysql if I get this many records in one table? For the querries, should I just do all the background calculations (goals 1 thru 4) and then post the results in one simple table or just let customers to querry the database from the raw data? I think the web application will be faster if I store only the results in a table. But again, the number of reports will be some what limited and the application will not be that flexible. As to your questions, the relationship between a product and a vendor is many to many (a vendor can carry many products and several vendors can carry the same product). Each vendor can operate in many states. Thank you.

Hello there, I am new to PHP and MYSQL, but I have a basic understanding of DB concept and some programming experience with other languages. I am currently working on a project and I need your input as to the best way to design my website. Here is the project description: [u]a vendor rating system.[/u] [u] The process:[/u] There are 9,000 vendors who sell to customers in multiple states. A vendor may or may not operate in all States. -Each customer has different characteristics for instance age, marital status, gender, range, etc. -Each vendor sells a combination of different products (there are about 6 different types of product on the market and these products are mutually exclusive). -A customer can use only one product type at a time. There are 8 different ways each product can be used. For instance, a body soap can be used to take bath and wash hand. It's not frequent to find people who collect different brand of body soap. - Each vendor can have relationship (on a different matter: for instance exchanging information about their customers) with other vendors. - I have the raw data about all the products sold from all the vendors along with all the characteristics previously described. [u]Goals:[/u] I would like to assign a score (based on customer experience with the product) to each vendor. Specifically, I want to accomplish the following: 0- Define the scoring system: for instance, the percentage of specific group of satisfied customers for a vendor, after taking into account whether or not the vendor sold at least 2% of the product to the specified group. 1. The customer comes to the website, inputs the characteristics and the zipcode, then the web application will show the top ranked vendors for the customer. 2. The customer can decide not to give all the characteristics, and I still should be able to provide top vendors. 3. The customer may want to see the top vendors in the county, state or nationwide instead and the web application should be able to give it as well. 4. I also want to show the customer the list of vendors that do business with a particular vendor. Now my questions are: How many tables do I need in this situation? How do I go about designing a consistent database for this? Any help will be appreciated. Thank you Cisse.