The essence of the following, you need to make a database of accounting of customer calls and accounting of orders. Manipulation of the database will occur through PHP. Customers call and ask for the price of construction work.
The manager records all these requests (calls) from the client in the table of the next structure.

--Таблица заявки -- -id -Имя -Фамилия -Телефон -адрес -тип работы (id_type - внешний ключ с таблици типы) -Статус(Принято,отказано,заказано,дорого,клиент думает) -Дата заявки

It is necessary to do so when the status is "Ordered", then create a new customer (in the customer table) based on the data from the application table (name, phone number, address, and a couple more data). While the status is different, nothing to do. The problem is this: the client can call again after a while, and this application needs to be fixed, respectively, if the client’s status is “Ordered” again, we will put it in the clients table, and duplicate one client will be obtained.

What is the best with the design. And even if the status of the application is "Ordered", then some data will be entered, up to the calculation of the cost of work. And I think whether it is necessary to enter them into the application table (by default, fill in the fields with zeros), or create a new one.

  • 1) When adding a new client to the table of clients, check for the existence of a client with the same, say, last name + phone. Adding a client is best done with a trigger, of course. 2) It depends on what data - Matty
  • And why do you need two tables of clients? - Akina
  • The table will be one for customers. The main essence of the customer table: this is for the statistics of viewing orders, plus to facilitate filling in the data of the Order (automatic loading by phone for example) - depredator

2 answers 2

The question lies not so much in the field of design, as in the field of administration. I will explain: the table of clients is so-called. master data that is critical for duplicates. Whatever system of checks to organize, malicious users will find a way to start something like:

  • PE Pupkin
  • ICP Pupkin
  • PBOYUL Pupkin
  • Pupkin PE
  • PE Pupkind
  • Ch.P. Pupkin
  • Well, etc.

More or less proven pattern here is this:

  1. We throw new clients in the intermediate table, for example tmp_customer
  2. We assign Svetka Sokolova (ideally, in large organizations there is a Master Data Department ) to organize daily unloading of new records from tmp_customer to a customer with an appropriate check for duplicates.
  • and if the day will be registered 50,000 users? - L. Vadim
  • Then instead of Svetka Sokolova there will be a whole department - Barmaley
  • That's just the point, Svetka Sokolova will be one to enter data. The department is a lot) - depredator

and you get duplication of the same client.

The easiest way to solve this problem is to create a client at the first call, as part of the procedure for fixing the call. At the same time, as the operator enters the information, the input data is verified with the existing customer base, and if the information entered is sufficient to select a small (no more than 4–5), then information about them is displayed with a suggestion to choose from the existing (which is not excludes the continuation of filling out the form and entering the client as new). It is possible that after the selection you still have to adjust it (for example, the client has changed the phone number since the last orders). If there are many matches, only the number of matches is displayed.