SQLTeam.com | Weblogs | Forums

Structre to link 2 tables and use the link for multiple tables

#1

Hi, I'm using SQLite to create a musical sheet database. I have one table for the sheet and one table for names of people.

-- Create sheet table
CREATE TABLE "tbl_sheet" ( 
  "id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL, 
  "int_category_id" INTEGER, 
  "txt_repertory_no" VARCHAR, 
  "int_taken_from_id" INTEGER, 
  "int_region_id" INTEGER, 
  "int_prepared_by_id" INTEGER, 
  "int_source_by_id" INTEGER, 
  "int_measure_id" INTEGER, 
  "txt_time" VARCHAR(5), 
  "int_compiled_by_id" INTEGER, 
  "dt_compilation_date" DATE, 
  "txt_lyrics" VARCHAR, 
  FOREIGN KEY(int_category_id) REFERENCES tbl_category(id), 
  FOREIGN KEY(int_taken_from_id) REFERENCES tbl_person(id), 
  FOREIGN KEY(int_region_id) REFERENCES tbl_region(id), 
  FOREIGN KEY(int_prepared_by_id) REFERENCES tbl_person(id), 
  FOREIGN KEY(int_source_by_id) REFERENCES tbl_person(id), 
  FOREIGN KEY(int_measure_id) REFERENCES tbl_measure(id), 
  FOREIGN KEY(int_compiled_by_id) REFERENCES tbl_person(id), 
)

-- Create person table
CREATE TABLE "tbl_person" ( 
  "id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
  "txt_person" VARCHAR NOT NULL  UNIQUE 
)

How would I create a table for notation person? There may be one then one notation person. Below code has multiple rows with duplicates:

-- Create notation person table
CREATE TABLE "tbl_l_notation_person" ( 
  "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
  "int_sheet_id" INTEGER, 
  "int_person_id" INTEGER, 
  "int_notation_person_count" INTEGER 
)

How can I fix this?

#2

Your question isn't clear. Is this table to assign a person to a sheet? Can a sheet be assigned to more than one person? Can a person be assigned to more than one sheet? My guess is you are trying to assign sheets to people and vice versa (many to many). You can add a unique index on SheetID and PersonID to this table or make those 2 columns your primary key. Not sure why you would have count as a column or what it represents.