Database session 1

Customer

primary (tinyint/boolean)

This handles duplicate customer records. A single person (email) can have multiple customer records in the database — for example, one created via the webshop and one synced from the NAV/POS system (in-store). The primary flag marks which record is the canonical one for that email address.

• Set automatically on save: self::countAllByAttributes(['email' => $this->email]) <= 1 (line 212)
• setAsPrimary() (line 1256) sets one record as primary and all others with the same email as non-primary
• Secondary customers have names like __ (line 1301) and are linked via the primary's nav_id
• The primary record is the one used for the API, member points, Copernica syncing, and the external customer account app

New Solution

We will not use this in the new database setup. We will try to prevent several accounts with the same email.

The email will need to be unique, and we will store the unique ID's from the NAV/POS system as a seperate (nullable?) column.

This way the customer will always only have 1 "account".

type

the customer type is an column that can put a customer in a specific group of customers. e.g. (partikulier, zakelijk)

rand_id

Rand ID seems to have been used to hide the real ID when it needed to be shown somewhere publicly, like in the URL.

This ID is in a non guessable format, unlike the real ID which is sequential.

New Solution

We discussed this and decided on just encrypting and decrypting the ID when it needs to be shown publicly, this way the database ID is easy to use, and we can prevent bad actors from guessing ID's.

in-store survey

For now we skipped the in-store survey in the database. This should be investigated in the future. ┌────────────────────────────┬─────────────────────────────┬──────────────────────────────────────────┐ │ Model │ Table │ Purpose │ ├────────────────────────────┼─────────────────────────────┼──────────────────────────────────────────┤ │ Customer_display │ customer_display │ In-store survey questions │ ├────────────────────────────┼─────────────────────────────┼──────────────────────────────────────────┤ │ Customer_display_option │ customer_display_options │ Survey answer options │ ├────────────────────────────┼─────────────────────────────┼──────────────────────────────────────────┤ │ Customer_display_result │ customer_display_results │ Survey responses │ └────────────────────────────┴─────────────────────────────┴──────────────────────────────────────────┘

Discount

We switched the discount system to an rule system with classes and conditions in the database.

We should have 2 type of rules. - Activation rule (what conditions does this discount have) - discount rule (how much is the discount)

The old table has the following fields:

Table: discounts

Full discount/voucher code system.

┌───────────────────────────┬──────────┬────────────────────────────────────────────────────┐ │ Column │ Type │ Purpose │ ├───────────────────────────┼──────────┼────────────────────────────────────────────────────┤ │ id │ int │ PK │ ├───────────────────────────┼──────────┼────────────────────────────────────────────────────┤ │ name │ varchar │ Description (e.g. "mailing", "buro scanbrit 2024") │ ├───────────────────────────┼──────────┼────────────────────────────────────────────────────┤ │ amount │ decimal │ Fixed discount amount (EUR) │ ├───────────────────────────┼──────────┼────────────────────────────────────────────────────┤ │ rate │ decimal │ Percentage discount (alternative to amount) │ ├───────────────────────────┼──────────┼────────────────────────────────────────────────────┤ │ min_amount │ decimal │ Minimum cart value required │ ├───────────────────────────┼──────────┼────────────────────────────────────────────────────┤ │ code │ varchar │ Unique alphanumeric code (10 chars, no I/O/0) │ ├───────────────────────────┼──────────┼────────────────────────────────────────────────────┤ │ startingdate / endingdate │ datetime │ Validity window │ ├───────────────────────────┼──────────┼────────────────────────────────────────────────────┤ │ use │ tinyint │ Whether the code has been used │ ├───────────────────────────┼──────────┼────────────────────────────────────────────────────┤ │ single_use │ tinyint │ One-time use only │ ├───────────────────────────┼──────────┼────────────────────────────────────────────────────┤ │ restant_vervalt │ tinyint │ If true, leftover value is forfeited │ ├───────────────────────────┼──────────┼────────────────────────────────────────────────────┤ │ productgroup_id │ varchar │ Restrict to specific menu categories │ ├───────────────────────────┼──────────┼────────────────────────────────────────────────────┤ │ article_series │ varchar │ Restrict to specific article barcodes │ ├───────────────────────────┼──────────┼────────────────────────────────────────────────────┤ │ type │ int │ 1 = discount code, 2 = giftcard discount │ ├───────────────────────────┼──────────┼────────────────────────────────────────────────────┤ │ invoicing / invoiced │ tinyint │ For partner invoice tracking (Scanbrit/Voigt) │ ├───────────────────────────┼──────────┼────────────────────────────────────────────────────┤ │ ledger_type │ int │ 1 = discount, 2 = gift (accounting) │ └───────────────────────────┴──────────┴────────────────────────────────────────────────────┘

The new table has the following fields ``` table discounts { id int [primary key]

customer_id int [null]

code string type string // enum for review, copernica or manual

created_at datetime updated_at datetime } ```

Discount types

Discount Types

The type column maps to K3's DiscountType constants:

┌──────┬────────────────┬─────────────────────────────────┬────────────────────────────────────┐ │ Type │ Constant │ Meaning │ How value is used │ ├──────┼────────────────┼─────────────────────────────────┼────────────────────────────────────┤ │ 0 │ DealPrice │ Fixed deal price for the bundle │ value = total bundle price │ ├──────┼────────────────┼─────────────────────────────────┼────────────────────────────────────┤ │ 1 │ DiscountPerc │ Percentage off total bundle │ value = percentage (e.g. 10 = 10%) │ ├──────┼────────────────┼─────────────────────────────────┼────────────────────────────────────┤ │ 2 │ DiscountAmount │ Fixed amount off total bundle │ value = EUR discount amount │ ├──────┼────────────────┼─────────────────────────────────┼────────────────────────────────────┤ │ 3 │ LeastExpensive │ Percentage off cheapest item │ value = percentage off cheapest │ └──────┴────────────────┴─────────────────────────────────┴────────────────────────────────────┘