CODEWITHSUNDEEP
sqldatabasedatabase-design
James Andrew Smith
James Andrew Smith

Reputation: 1566

Database design for customer to skills

I have an issue where we have a customer table includes name, email, address and a skills table which is qts, first aid which is associated by an id. For example

Customer
id = 1
Name = James
Address =  some address

Skills
1, qts
2, first aid

I am now trying to pair up the relationship. I first came to a quick solution just by creating a skills table which just has customerId and each skill has a true / false value. Then created a go between customer_skills with an customerId to SkillId. But I would not know how to update the records when values change as there is no unique id.

can anyone help on what would be the best way to do this?

thanks....

Upvotes: 0

Views: 651

Answers (2)

GarethD
GarethD

Reputation: 69789

The solution you want really depends on your data, and is a question that has been asked thousands of times before. If you google Entity Attribute Value vs strict relational model you will see countless articles comparing and contrasting the methods available.

Strict Relational Model

You would add additional BIT or DATETIME fields (Where a NULL datetime represents the customer not having the skill) to your customer table for each skill. This works well if you have few skills that are unlikely to change much over time. This allows simple queries to locate customers with skills, especially with various combinations of skills ee.g (Using datetime fields)

SELECT  *
FROM    Customer
WHERE   Skill1 >= '20120101' -- SKILL 1 AQUIRED AFTER 1ST JAN 2012
AND     Skill2 IS NOT NULL  -- HAS SKILL 2
AND     Skill2 IS NULL      -- DOES NOT POSSESS SKILL 3

Entity-Attribute-Value Model

This is a slight adaptation of a classic entity-attribute-value model, because the value is boolean represented by the existence of a record. You would create a table like this:

CREATE TABLE CustomerSkills
(       CustomerID      INT NOT NULL,
        SkillID         INT NOT NULL
    PRIMARY KEY (CustomerID, SkillID),
    FOREIGN KEY (CustomerID) REFERENCES Customer (ID),
    FOREIGN KEY (SkillID) REFERENCES Skills (ID)
)

You may want additional columns such as DateAdded, AddedBy etc to track when skills were added and who by etc, but the core principles can be gathered from the above. With this method it is much easier to add skills, as it doesn't require adding columns, but can make simple queries much more complicated. The above query would have to be written as:

SELECT  Customer.*
FROM    Customer
        INNER JOIN
        (   SELECT  CustomerID
            FROM    CustomerSkills
            WHERE   SkillID IN (2, 3)   -- SKILL2,SKILL3
            OR      (SkillID = 1 AND DateAdded >= '20120101')
            GROUP BY CustomerID 
            HAVING  COUNT(*) = 2
            AND     COUNT(CASE WHEN SkillID = 3 THEN 1 END) = 0
        ) skills
            ON Skills.CustomerID = Customer.ID

This is much more complext and resource intensive than with the relational model, but the overall structure is much more flexible.

So to summarise, it really depends on your own particular situation, there are a few factors to consider, but there are plenty of resources out there to help you decide.

Upvotes: 3

user1429080
user1429080

Reputation: 9166

If you have a table linking the primary keys from two other tables together in order to form a many-to-many relationship (like in you example) you don't have to update that table. Instead you can just delete and reinsert values into it.

If you are editing a custiomer (customerId 46 for instance) and changing the skills for that customer, you can just delete all skills for the customer and then reinsert the new set of skills when storing the changes.

If your "link table" contains some additional information besides just the two primary key columns, then the situation might be different. But from your description it seems like you just want to link the table together using the primary keys from each table. In that case a delete + reinsert should be fine.

Also in this kind of table, you should make the combination of the two foreign key fields be the primary key of the binding table.

Upvotes: 0

Related Questions