Problem solve Get help with specific problems with your technologies, process and projects.

Connecting tables within a database

I am making a database for inventory. The different catagories are Books, Videos, CDs, and Tapes. My problem is that I can't find a proper way of connecting the different tables in my database. I would think that I would want to keep all the catagories separate, since each works for a different product. But I also need to find a way to link them together for the database to connect. Thus my dilemma. Could you please give me your advice on how I should go about connecting this database? Thank you for your time.

I'd create a couple of tables, and link the tables together.

CREATE TABLE t_types ( 
   type_id                INT                INDENTITY 
      PRIMARY KEY (type_id) 
,  name                    NVARCHAR(30) 

CREATE TABLE t_authors ( 
   author_id                INT                IDENTITY 
   CONSTRAINT XPKt_authors 
      PRIMARY KEY (author_id) 
,  last_name                NVARCHAR(30) 
,  first_name                NVARCHAR(30) 

CREATE TABLE t_items ( 
   item_id                INT                IDENTITY 
      PRIMARY KEY (item_id) 
,   type_id                INT                NOT NULL 
   CONSTRAINT XIF01t_items 
      FOREIGN KEY (type_id) REFERENCES t_types (type_id) 
,  name                    NVARCHAR(50) 

CREATE TABLE t_item_authors ( 
   item_author_id                INT                IDENTITY 
,  author_id                INT                NOT NULL 
   CONSTRAINT XIF01t_item_authors 
      FOREIGN KEY (author_id) REFERENCES t_authors (author_id) 
,  item_id                INT                NOT NULL 
   CONSTRAINT XIF02t_item_authors 
      FOREIGN KEY (item_id) REFERENCES t_items (item_id) 

By creating rows in the t_types table, you can have books, tapes, CDs, and whatever else suits you in your tables. Add new types as you need them.

The t_item_authors table allows you to synthesize a many-to-many relationship from pairs of one-to-one relationships. An author may create many items. An item may have many authors. Each row in t_item_authors describes the relationship between one item and one author. In this table, any given author may have zero or more rows, and any given item may also have zero or more rows.

For More Information

This was last published in April 2001

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.