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 CONSTRAINT XPKt_types 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 CONSTRAINT XPKt_items 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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Database Design Web Links: tips, tutorials, scripts, and more.
- Have a Database Design tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Database Design questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our Database Design guru is waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Related Q&A from Pat Phelan
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.