What's the proper name for an "add-on" table?

Question

I have a table a with primary key id and a table b that represents a specialized version of a (it has all the same characteristics to track as a does, plus some specific to its b-ness--the latter are all that are stored in b). If I decide to represent this by having b's primary key be also a foreign key to a.id, what's the proper terminology for b in relation to a?

A real world example might be a person table with student and teacher add-on tables. A student might also be a teacher (a TA for example) but they're both the same person.

I would call it a 'child table' of a but I already use that as a synonym for 'detail table', like lines on a purchase order, for example.

Answer 1

Ok this is sort of off topic but first things first, why does B have all of A's columns? It should only have the added columns, ESPECIALLY if you are referencing A with a foriegn key.

"Add on" records are usually called "Detail(s)"

For example, lets say my Table A is "Cars" my Table B would be "CarDetails"

Answer 2

As Neil N said, you shouldn't have the columns in both places if you're referencing table A in table B through a foreign key.

What your describing sounds a bit like a parallel to inheritance in object oriented programming. Personally, I don't use any specific naming convention in this case. I name A what it is and I name B what it is. For example, I might have:

CREATE TABLE People
(
     people_id      INT             NOT NULL,
     first_name     VARCHAR(40)     NOT NULL,
     last_name      VARCHAR(40)     NOT NULL,
     ...
     CONSTRAINT PK_People PRIMARY KEY CLUSTERED (people_id)
)
GO

CREATE TABLE My_Application_Users
(
     people_id          INT             NOT NULL,
     user_name          VARCHAR(20)     NOT NULL,
     security_level     INT             NOT NULL,
     CONSTRAINT PK_My_Application_Users PRIMARY KEY CLUSTERED (people_id),
     CONSTRAINT UI_My_Application_Users_user_name UNIQUE (user_name)
)
GO

This is just an example, so please don't tell me that my name columns are too long or too short or that they should allow NULLs, etc. ;)

Answer 3

Your design sounds like Concrete Table Inheritance.

I'd call table B a concrete table that extends table A.

The relationship is one-to-one.

---

Other answers have suggested storing only the columns specific to the extended table. This design would be called Class Table Inheritance.

Answer 4

what's the proper terminology for b in relation to a?

Table B is a child of Table A (the parent), because in order for a record to exist in the child, it must first exist in the parent.

Tables should be modeled based on either having one-to-many or many-to-one relationships depending on the context, and of those options they can be either optional or required. Tables that link two sets of lists together will relate to other tables in a many-to-one fashion for every table involved. For example, users, groups, and user_groups_xref - the user_groups_xref can support numerous specific user instances of a user records, and the same relationship to the groups table.

There's no point in one-to-one relationships - these should never be allowed to exist because it should only be one table.