2

I have a database of members of an organization.

The data is really messy - the leader had a catastrophic medical issue, so I'm trying to make a sensible database out of Claris Filemaker Pro.

I'm trying to look for duplicates. There are some - apparently on purpose. For example, a couple could join 'once' but get two badges, so in the database we have one member ID (so no unique on it) and two different names. Not only that, libraries are in the database and several of them have the same email address, so no joy there.

I was trying to make a unique compound index of last_name, first_name, email_address, and member_id, but I found it allows duplicates.

I know NULL's aren't in indexes, but I was surprised that a compound index, if there is a single NULL in it, apparently allows duplicates of the non-null fields. The MySQL docs say:

A UNIQUE index permits multiple NULL values for columns that can contain NULL.

Sure, but what about the other 3 fields?

I didn't see a mention in the MariaDB docs.

Database:

CREATE TABLE `test` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `last_name` VARCHAR(46) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `first_name` VARCHAR(46) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `email_address` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `member_id` VARCHAR(40) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    UNIQUE INDEX `index_dupecheck` (`last_name`, `first_name`, `email_address`, `member_id`) USING BTREE,
    INDEX `index_id` (`id`) USING BTREE
)
COMMENT='Test version'
COLLATE='utf8mb3_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=3
;

test

id last_name first_name email_address member_id
1 Bob Johnson [email protected] \N
2 Bob Smith [email protected] 1
3 Bob Johnson [email protected] \N

I want to ensure that row 3 could not be inserted if row 1 is already there.

It sounds like this is due to NVL comparison to NVL being 'not equal'? (Postgres, but this sounds like the problem How can this unique index allow duplicate rows? )

except that syntax doesn't seem to work with MariaDB.

Is there another, better way of doing it? I could probably do the checks programmatically, but whoever put this data in, will probably put it in again, so I'd like to have a server side restriction for obvious reasons.

I'm also thinking of just saying member-ID has to be unique, and generate some way to link 'family' accounts together, or a 'smart number' like 123-A, 123-B for Alice and Bob (so no one's #1)

Thank you

3
  • 1
    A simple workaround would be to declare the columns as not null default something. Something like this example Jul 18, 2023 at 7:55
  • Ah, great example. Thanks. I was trying to avoid having blank, but not null fields, but it might be the easiest way out of this conundrum, at least for a few specific fields.
    – J. Gwinner
    Jul 18, 2023 at 22:40
  • @ErgestBasha if you made this an answer I'd upvote for sure.
    – J. Gwinner
    Jul 18, 2023 at 22:42

1 Answer 1

1

Except for obscure situations, you can consider MySQL == MariaDB. I would include NULLs in UNIQUE definitions. And, yes, 3 columns with any column(s) having NULL acts that way.

Do note that the PRIMARY KEY does not allow Nulls, but is otherwise like Unique.

It is up to you to decide if the business logic is happy with this to distinguish one user from another:

(`last_name`, `first_name`, `email_address`, `member_id`)

But, since that is clunky, etc, I would shift to id being the PRIMARY KEY. How is id assigned? Manually? Could it be AUTO_INCREMENT instead? This would allow the table to assign ids.

What needs to happen if customer without a member_id (IS NULL) gets a member_id. This will cause grief because of what id. For that reason, maybe you should not make it AUTO_INCREMENT.

Can Bob Smith get a second member_id? Etc. etc. You need to think through all the odd-ball cases. Then decide on what the PRIMARY KEY needs to be.

5
  • "Except for obscure situations, you can consider MySQL == MariaDB." Sure, but a few months ago someone got pretty mad and deleted an answer when I hadn't specified MariaDB, so I try to be clear now, but I appreciate the comment.
    – J. Gwinner
    Jul 18, 2023 at 22:36
  • This is an existing table, otherwise I'd have used a unique id being the PRIMARY KEY. They had no primary key. So yes, there are several times you have the same last name, first name, but no email address, and the same member id, or the same email address but no member id. Those records we are trying to find and/or prevent from updates, but the unique index isn't helping with that. We're trying to clean the table up and prevent more updates like that. I can't reassign the member_id, there are already 4,000 members with ID's ... some of which are duplicated (families)
    – J. Gwinner
    Jul 18, 2023 at 22:37
  • > "What needs to happen if customer without a member_id (IS NULL) gets a member_id." Normally, yes, but this is a mailing list of potential members who haven't paid yet. So they get a 'null' member_id. What makes it worse is some member ID's are duplicated, i.e. families pay one price, so bob and jane both get the same member ID. (otherwise I'd make it unique). I don't want to denormalize into a linked member ID back to the members table, non-technical people will be using this.
    – J. Gwinner
    Jul 18, 2023 at 22:45
  • @J.Gwinner - If there is a UI frontend for handling membership, etc, I would build into it the various checks you described. If all is good, then the UI would simply do whatever update is needed. If there are NULL(s), dups, etc, display the info and let the agent decide which record to update (or decide that a new record is needed). The UI would have buttons for the various exception cases. The Supervisor (you) would be called in for cases that the agent does not understand. Maybe all cases can eventually be coded.
    – Rick James
    Jul 19, 2023 at 2:06
  • @rick.james Thanks - it's an existing table. No UI. I'm trying to import it to a rational database. The guy that ran the 9K member database did it all in Filemaker by Claris, no other UI.
    – J. Gwinner
    Aug 5, 2023 at 22:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.