I am experienced in Webdesign, Programming, Server Management but I am inexperienced in Database design. For a project I am developing for myself I need to learn to program a database and have read a book and searched thru Internet tutorials and forums. I think I have no problem with the first 2 normalistion forms and know that the 3NF is not always strictly necessary. My planning is nearly at the end and I know how everything should work in the end. I have however noticed that I have a big problem with my database design. The problem is that I do not know which of the 2 ways I am thinking off is the best or if there is even a better way. Let me describe what I want to do.
Database that I want to design will be in MYSQL.
My project will have to track many customers with lots of attributes. This data has to be used for php website display and has to be maintained by staff with an VB6 application (phone support etc.)
For each of those customers I will have to log many events like earned rebate points for different things they do, logins need to be stored with their IPs, earned payments and some other events. To simplify things I leave all the other info out.
No I am thinking of 2 ways to implement this:
1. I create entities CUSTOMER, LOGIN, REBATE, PAYMENTS
In LOGIN, REBATE and PAYMENTS I store the data for all users. Those tables can get very big fast as I expect many customers. From all what I have learned so far this would be the right database design approach.
2. I create an Entity CUSTOMER with limited attributes to lookup the customerID.
I create a dir customerID and basically create a database for each customer seperately in this dir.
I think that this might have an performance advantage when a customer goes to the website and the website is created with just the needed data for that customer.
The problem I have is that as an inexperienced database designer have no idea about any performance issues or any other problems with any of the 2 designs.
I would be very thankful if I can get any opinions to help me make this decision.
The 2. option is mainly done to have the eventlog tables for that customer together at the same place. In option1 they would be in one table for all customers and I might come into the problem of searching thru millions of entires from all customers.
in Option 2 I am seeing this as seperate databases
The limited CUSTOMER database is just to lookup the Customer_ID. This ID is used to open the customer database
in the directory customer_ID/customerdatabasename
Basically it comes down to having 1 huge database or 1 small global database and a database for every customer.
Actually while typing this Option 2 makes less and less sense but it would be nice to know what the difference is performance wise