How to model a custom type in a relational database?

Question

I'm fairly new to database design, but I understand the fundamentals. I'm creating a relational database and I'd like to do something similar to creating a reusable type or class. For example, let's say I have a Customer table and a Item table. Customer and Item are related by a standard 1-to-many relationship, so Item has a column called CustomerId.

I'd also like to have multiple "notes" for each Customer and each Item. In an normal OOP model, I'd just create a Note class and create instances of that whenever I needed. Of course, a relational database is different. I was thinking about having a Note table, and I'd like a 1-to-many relationship between Customer and Note, as well as Item and Note. The problem then is that the Note table will have to have a column for each other table that wishes to use this "type". (see example below)

I also thought that instead, I could create an intermediate table between Note and Customer/Item (or others). This would allow me to avoid having extra columns in Note for each table referencing it, so note could remain unchanged as I add more tables that require notes. I'm thinking that this is the better solution. (see example)

How is this sort of situation usually handled? Am I close to correct? I'd appreciate any advice on how to design my database to have the sort of functionality I've described above.

Answer 1

I think you can best add a Note field to the Customer table and the Item table. In this note field (foreign key) you can store the id of the nota that belongs the the Customer / Item. To make sure you can attach multiple notes to a Customer or Item you could choose to add a Notes table and attach single Notes to the "Notes" table that you can attach to your Customer / Item table.

Answer 2

Yes, your concluding example is correct, and should be the way to go.

You model a "complex type" in relational databases by creating tables. You can consider the table as a class: in fact ORM solutions often map a class directly to a table. An instance of the custom type is a row in its table, and the instance can be referenced by the value of the primary key.

You can use your custom "complex type" for fields in other tables by using the same data type as the primary key of the "complex type", and enforcing the relationship with a foreign key constraint:

Let's build a complex type for "countries":

CREATE TABLE countries (
   iso_code     char(2)       NOT NULL,    
   name         varchar(100)  NOT NULL,
   population   bigint       
   PRIMARY KEY (iso_code)
);

And let's add a couple of "country" instances:

INSERT INTO countries VALUES ('IE', 'Republic of Ireland', 4470700);
INSERT INTO countries VALUES ('US', 'United States of America', 310403000);

Now we're going to use our complex "countries" type in a "users" table:

CREATE TABLE users (
   id          int            NOT NULL,    -- primitive type
   name        varchar(50)    NOT NULL,    -- primitive type
   age         int,                        -- primitive type
   country     char(2),                    -- complex type
   PRIMARY KEY (id),
   FOREIGN KEY (country) REFERENCES countries (iso_code)
);

With the above model, we are guaranteed that the country field of the users table can only be a valid country, and nothing but a valid country.

In addition, using a junction table, as you suggested, is also a suitable approach to deal with that kind of polymorphic relationship. You may be interested in checking out the following Stack Overflow posts for some further reading on this topic:

• How can you represent inheritance in a database?
• Possible to do a MySQL foreign key to one of two possible tables?