如果我正确理解， log 用于自动记录用户操作的详细信息，个人资料保存用户详细信息。

对于 log 表，您似乎试图保存汇总数据，提交。我建议你只是插入一个新的日志条目[例如。 log_id ， log_date ， profile_id ， amount ]，并将汇总保留到报表。要启用从 log 到个人资料的联接，我们包括 profile_id 。

对于配置文件表，您应该允许 first_name | last_name | dob ，尝试找到另一种方法使它们是唯一的code> profile_id ，或通过电子邮件或护照号）。

我不确定您对 rowone ， rowtwo ， rowthree 。如果你知道他们是什么，然后明确命名他们;如果它们用于重复未知次数的项，请考虑将它们移动到单独的表（即规范化）。

对于您的报告，您将写入一个查询以加入 log 和 （ GROUP BY 与 SUM 和/或 访问计数将是该配置文件的日志条目的 COUNT ，金额将是金额的 SUM 。

I'm currently working on building custom application. My first module already completed and now I'm still figuring out how to create the structure for second and third module to work flawlessly without any error or worst crash the whole database.

Some of the info:-

My first module, data are manually exported as the database is the main structure for my second and third module to work with.

My second module data are logs for report, where the data automatically enter to the database upon button submit click, (something like autosave, I assumed) This module will work as my report listing as in number customer visit of the week, top customer and etc.

Report outcome for visit of the day:

Customer Name | Date of Birth | Visit Count | Amount
   John Doe   |  1948-01-29   |       1     |   10
 Guest Guest  |  1968-12-16   |       1     |   10

My third module will be profile search where the data also automatically enter into the database upon button submit click. This module will work for the user to search the profile and proceed to the first module as well for the report usage listing as above behavior.

My current structure for log

• log_id (A.I)
• dob
• log_datetime
• log_count
• amount

And structure for profile

• profile_id (A.I)
• dob
• rowone
• rowtwo
• rowthree
• first_name
• last_name

Now I'll need to get the profile to attached to log when I use my second module for reporting whereby the report will show the detail from log table: dob, log_datetime, log_count, amount and profile table: first_name, last_name

If I add profile_log_id to log table and get lastinsertID, there is a problem that the user may not input the first_name or last_name: therefore I'll use Guest for first_name and last_name columm.

Also, I'll use On Duplicate to update the table if the details are the same, e.g. if the first_name, last_name and dob combination already exist, it will update the existing profile table.

So what is the way for me to get a better database design structure to ensure that the data are not conflict with each other and I still can make the report and profile search module work flawlessly?

Sorry for my long and complicated question, as this is my first application building and lack of experience in database design. Appreciate for everyone who help me to suggest and advise the better structure.

Additional note:

1. The first module which calculates the dob are stored a table with column of dob_id, dob, rowone, rowtwo and rowthree I couldn't explain more about this as this module is write based on my mom daily usage. All the data are manually entered to database to act as the main data pull over.
2. The second module is the log which record the daily log for reporting purpose. The column dob represent for daily customer dob while the log_count represent the visit time and amount represent the amount they pay for each visit.
3. The third module is the profile the user opted to fill in the customer name or not during the dob search from the first module which is a form for the user to enter the date, month and year (required field) and first name with last name as optional. As the query and insert are made separately, therefore the main data extract the data first and the end of the script will be the insert where the data extracted from main table (dob, rowone, rowtwo, rowthree) are enter into the profile table and log table (only dob). The log_count and amount are set so it will return the value to the database each time of insert.
4. Because of the first and last name is an optinal value, therefore I'll replace an empty of first and last name to Guest Guest` for recording purposes.
5. I need a search bar where the user can directly search the profile table for the first and last name and list out all the data of first_name, last_name, dob, rowone, rowtwo, rowthree that match the search criteria. This is use for regular customer only, to ease the search by recording their profile instead of keep asking for their dob everytime they visit and search by dob. That's why I'll use update the existing record instead of keep on insert the same data if dob first_name and last_name is match to avoid profile search showing duplicate data.

---

Live case scenario.

For the month of January: There are total of 20 customers visit.

• 4 customers visit are recorded and as the same person (John Doe - DOB: 1948-12-19).
• 3 customers visit are recorded and it is different person as previous 4 visit (John Doe - DOB: 2000-07-31)
• 5 customers visit are recorded as the same person (Vin Diesel - DOB: 1959-03-22)
• 5 customers visit are recorded as the same person (Teresa John - DOB: 1988-05-18)
• 1 customers visit are recorded (Guest Guest - DOB: 1977-11-17)
• 1 customers visit are recorded (Guest Guest - DOB: 1962-09-28)
• 1 customers visit are recorded (Guest Guest - DOB: 1977-11-17)

Guest Guest are not allowed to combine the visit:

The report outcome which join the table of logand profile

Customer Name | Date of Birth | Visit Count | Amount
   John Doe   |  1948-12-19   |       4     |   40
   John Doe   |  2000-07-31   |       3     |   30
  Vin Diesel  |  1959-03-22   |       5     |   50
 Teresa John  |  1988-05-18   |       5     |   50
 Guest Guest  |  1977-11-17   |       1     |   10
 Guest Guest  |  1962-09-28   |       1     |   10
 Guest Guest  |  1977-11-17   |       1     |   10

Total Customer Visit: 20

Total Earn: 200

So I need the log to record the data by every form submission but profile I need to insert if the criteria of first_name, last_name, dob didn't match or if match, then it will update the record. If the report is for the year then the visit count and amount will added up by the first time log or profile recorded.

For the profile search, the user will only enter a name and return the value the match the search, for example the user enter John and the query will find from table first_name and last_name should return the following outcome:

Customer Name | Date of Birth | Visit Count | Row One | Row Two | Row Three
   John Doe   |  1948-12-19   |       4     |   3     |    8    |     12
   John Doe   |  2000-07-31   |       3     |   7     |    1    |     6
 Teresa John  |  1988-05-18   |       5     |   2     |    10   |     8

The visit count will calculate from the first day of profile created until current.

It's quite complicated, as I see it need to inter-link with each other table, but I hope there is a better way to design the log and profile table rather than crashing the whole database. I'm not asking for do the code for me but I'm looking for answer for a better database design to cater the desired outcome.

Side Note: At the moment, the application, doesn't have user login, but will implement it after I get this thing work out right. The user login only restricted to admin and user for the app purpose. The visitor or customer doesn't have the authority to login as the registeration is made in admin panel.

Hope this info will gave every member over here a clearer picture what is my intention and desired outcome.

If I understand this correctly, log is for automatically recording details of user actions, and profile holds the user details.

For the log table, you seem to be attempting to hold summarized data, that would need updating with each form submission. I would suggest you just insert a new log entry [e.g. log_id, log_date, profile_id, amount] on submission and leave the aggregation to the report. To enable the join from log to profile, we include profile_id.

For the profile table, you should probably allow for duplicate entries of first_name|last_name|dob and try and find another way of making them unique (either by using profile_id, or by email, or passport no).

I'm not sure of your intentions for rowone, rowtwo, rowthree. If you know what they are, then name them explicitly; if they're for items that repeat an unknown number of times, consider moving them to a separate table (i.e. normalise).

For your report, you will then be writing a query to join the log and profile tables together, with aggregation (i.e. GROUP BY with SUM and/or COUNT on the necessary fields. "Visit Count" would be the COUNT of log entries for that profile, "Amount" would be the SUM of the amount.