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) note_relation1

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) note_relation2

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.

Question: Does a note ever apply to multiple entities; i.e. can the same note apply to both a customer and an item? Or can the same note apply to multiple customers or multiple items? — Adam Musch, Oct 04 '10 at 13:28
No, each note would be only for a single Customer, Item, or whatever. — Benny Jobigan, Oct 04 '10 at 13:34
Nothing in your database design precludes it. You should move the TEXT and DATE fields from NOTE into CustomerNote and ItemNote to properly map that each note can be associated with at most one Customer or Item. As a result, the NOTE table would be eliminated altogether. — Adam Musch, Oct 04 '10 at 16:15
I don't want to eliminate the Note table, however. The whole point of this is to generalize the idea of a "note" into something I can reuse over and over. My ultimate aim, then, is to create a single "Note Editor" control in my program that I can reuse on all the different forms for data that might have "notes", such as the Customer Editor form. — Benny Jobigan, Oct 04 '10 at 16:32
I don't disagree with what you want; I'm pointing out the side effects of the design -- specifically that there's nothing that precludes associating the same note with multiple customers, items, or any combination thereof. If you must have a unified NOTE table, implement the first with a check constraint that mandates that one and only one of the various CustomerID/ItemID columns can be non-null; it's not elegant, but it would be correct. If you add a new table requiring "notes", you're going to have to make database changes anyway, to add a new EntityNote table. — Adam Musch, Oct 04 '10 at 16:39
Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, Nov 26 '18 at 07:06

score 4 · Accepted Answer · edited Nov 26 '18 at 06:37

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:

hmm, I suppose that when I create a junction table like "CustomerNote", I could model that as an inheritance relationship in an ORM (I read that's the multi-table way to do inheritance--in fact, I've already designed my "Customer" table to be a sub-class of another "Contact" table). Even though my goal wasn't to create inheritance for notes, in essence I'm creating sub-classes of notes (CustomerNote...) for each table I want to reference "Notes" from (eg Customer and Item). When I model this in an ORM, would it be better to create only a single "Note" class or to model inheritance? — Benny Jobigan, Oct 04 '10 at 13:21
@Benny: That's an interesting question, but I think an answer depends on the ORM solution. Unfortunately I don't have much experience using ORMs to add more insight. — Daniel Vassallo, Oct 04 '10 at 14:14

score 0 · Answer 2 · answered Oct 04 '10 at 12:37

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.

How to model a custom type in a relational database?

2 Answers2