MySql: The customer has multiple addresses only one is primary-CodePudding

I don't think I planned and organized the table well, so please consider and advise me how to solve this.

In my application I have to ensure that customers can have multiple addresses only one of those addresses must be marked as primary. The primary address is used as the delivery address, etc.

I have customer table and i create new relation table customer_addresses.

CREATE TABLE `customer_addresses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `custemer_id` int(11) DEFAULT NULL,
  `primary_adddrss` tinyint(1) DEFAULT NULL,
  `address` varchar(225) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `custemer_id_UNIQUE` (`custemer_id`),
  UNIQUE KEY `primary_UNIQUE` (`primary_adddrss`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

This table must contain all the addresses of all customers but only one address from a specific customer must be primary and customer can have only one primary address. That is reson whay i put two unique index.

If i remove custemer_id unique only one primary address can be in whole table for all customers.

Only one primary address per customer other non-primary addresses are null.

Table

id    |   customer_id    | primary_address   |  address
--------------------------------------------------------
1             1                 1               Test

This above customer have only one primary address. Now i want to insert for the some customer more non-primary addresses.

When i try to insert

  INSERT INTO `table`.`customer_addresses`(`id`,`custemer_id`,`primary_adddrss`,`address`)
    VALUES (1, null ,'Test 2');

I get error

1062: Duplicate entry '1' for key 'primary_UNIQUE'

If i remove primary_UNIQUE than all cusomers can have only one primary. I need one customer only one primary.

How to redesign table to slove this?

Thanks

CodePudding user response：

If the customer may have a lot of different addresses then you have 1:N relation. Create separate addresses table which refers to customers table. Add a column which marks primary address and restrict with only one mark per customer_id.

Schematically (no syntax):

CREATE TABLE customer ( customer_id PRIMARY KEY, ... );

CREATE TABLE address ( address_id PRIMARY KEY,
                               -- reference to customer
                       customer_id NOT NULL REFERENCES customer (customer_id),
                               -- can be either 1 or NULL
                       is_primary BOOLEAN NULL CHECK (is_primary = 1),
                               -- allows one 1 and many NULL per customer
                       UNIQUE (customer_id, is_primary),
                       ... );

DEMO

CodePudding user response：

Option 1 :

CREATE TABLE `customer_addresses` ( `id` INT NOT NULL AUTO_INCREMENT ,
`customer_id` INT NOT NULL ,
`primary_address` TEXT NOT NULL ,
`addresses` TEXT NOT NULL ,
PRIMARY KEY (`id`)) ENGINE = InnoDB;

you can save primary address in primary_address column, and other addresses is saved in addresses column as array

Option 2 :

CREATE TABLE `customer_addresses` ( `id` INT NOT NULL AUTO_INCREMENT ,
`customer_id` INT NOT NULL , 
`is_primary` BOOLEAN NOT NULL , 
`addresses` TEXT NOT NULL , 
PRIMARY KEY (`id`)) ENGINE = InnoDB;

just save all address as usual, and marks the primary address with is_primary=1 and the non-primary address with is_primary=0