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?