1

Let's say I have a table Cars (with columns such as model, make, year, etc.), a table Books (with columns such as name, author, ISBN, etc.) and a table Wines (with columns such as name, appellation, region, vintage, etc.)

Now, even if these tables don't seem related at all, I want to make a collection of all these things called "My favorites things in the world". In this list, I will find cars, books and wines.

If I only wanted to have lists of cars, I would create a table called Car_Lists with id and name + a table called Car_List_Items with id, car_list_id (foreign key to the id in the table Car_Lists) and car_id (foreign key to the id in the table Cars).

Here, as I want to have a list of different types of things, I don't really see how this could be done.

So my question: How would I represent that with a relational database?

1 Answer 1

1

There are many ways to do it, depending on the requirements. That being said, this sounds like a homework question which is designed to make you think about it.

However, one way to do it would be to create a table that contains columns that occur in all the different types. Then create a table for each unique type that contains information specific to that type.

Then in your list table you can just create a foreign key to the base table (in this example "things")

create table "things"
(
    id int identity not null primary key
    -- add shared columns here
);
create table "cars"
(
    id int not null,
    -- add car details here
    constraint fk_cars_id foreign key (id) references "things" (id)
);
create table "myfavoritethings"
(
     id int not null,
     constraint fk_myfavoritethings foreign key "things" (id)
);

I prefer this approach because you can maintain referential integrity. Also, you are able to create a nice view from it like so knowing that the id's are unique across all type tables:

create view "vw_myfavoritethings"
as
select t.id
     , -- add car fields here
  from "things" as t
  join "cars" as c on c.id = t.id
  -- additional joins to type tables
  join "myfavoritethings" as mft on mft.id = t.id;

Another way you could do it would be to just create your individual tables and then just store the id's and type in your list table like so:

create table "myfavoritethings"
(
    id int not null primary key
);

If you use this design it would be recommended to create a sequence and then use that to generate your primary key id's across all type tables to ensure that the id value is unique. You can of course use auto increments/identities on each table but then you would need to work out what id = 1 refers to in your list yourself.

1
  • @Vinch no problem - remember to mark the question as answered if this helped you. Dec 7, 2015 at 11:10

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.