-
Jun 19th, 2018, 04:13 PM
#1
Thread Starter
Addicted Member
Normalization for SQLite database
I have some tables in my database and there are duplicate records which I want to fix. These tables contain duplicate names across themselves: tbl_compiler, tbl_notation_person, tbl_preparer, tbl_source_person, tbl_taken_from. Here's the db structure:
SQL Code:
CREATE TABLE "tbl_artist" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "txt_artist" VARCHAR(256));
CREATE TABLE "tbl_category" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "txt_category" VARCHAR(256));
CREATE TABLE "tbl_compiler" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "txt_compiler" VARCHAR(256));
CREATE TABLE "tbl_measure" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "txt_measure" VARCHAR(256));
CREATE TABLE "tbl_notation_person" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "txt_notation_person" VARCHAR(256));
CREATE TABLE "tbl_preparer" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "txt_preparer" VARCHAR(256));
CREATE TABLE "tbl_press" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "txt_press" VARCHAR(256));
CREATE TABLE "tbl_region" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "txt_region" VARCHAR(256));
CREATE TABLE "tbl_sheet" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "int_press_id" INTEGER, "int_category_id" INTEGER, "txt_repertory_no" VARCHAR(256), "int_taken_from_id" INTEGER, "txt_revision_date1" VARCHAR(256), "txt_revision_date2" VARCHAR(256), "txt_revision_date3" VARCHAR(256), "int_region_id" INTEGER, "txt_research_date1" VARCHAR(256), "txt_research_date2" VARCHAR(256), "txt_research_date3" VARCHAR(256), "int_source_person_id" INTEGER, "int_measure_id" INTEGER, "txt_time" VARCHAR(256), "int_compiler_id" INTEGER, "txt_compile_date" VARCHAR(256), "int_notation_person_id" INTEGER, "txt_mp3_path" TEXT, "int_artist_id" INTEGER, "txt_song_name" VARCHAR(256), "txt_lyrics" TEXT, "image1" BLOB, "image2" BLOB, "image3" BLOB, "image4" BLOB, "image5" BLOB, "image6" BLOB, "image7" BLOB, "image8" BLOB, "image9" BLOB, "image10" BLOB, "image11" BLOB, "image12" BLOB, "image13" BLOB, "image14" BLOB,
FOREIGN KEY (int_category_id) REFERENCES tbl_category(id),
FOREIGN KEY (int_taken_from_id) REFERENCES tbl_taken_from(id),
FOREIGN KEY (int_compiler_id) REFERENCES tbl_compiler(id),
FOREIGN KEY (int_notation_person_id) REFERENCES tbl_notation_person(id),
FOREIGN KEY (int_press_id) REFERENCES tbl_press(id),
FOREIGN KEY (int_region_id) REFERENCES tbl_region(id),
FOREIGN KEY (int_source_person_id) REFERENCES tbl_source_person(id)
FOREIGN KEY (int_measure_id) REFERENCES tbl_measure(id)
FOREIGN KEY (int_artist_id) REFERENCES tbl_artist(id));
CREATE TABLE "tbl_source_person" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "txt_source_person" VARCHAR(256));
CREATE TABLE "tbl_taken_from" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "txt_taken_from" VARCHAR(256));
Do I have to merge them in 1 table for normalization? If so will I need to create more than 1 data tables in C#? I mean how can I fix this?
I'm not a man of too many faces
The mask I wear is one
-
Jun 19th, 2018, 05:59 PM
#2
Re: Normalization for SQLite database
If those tables all represent people then you can create a single Person table with a PersonId column as primary key and then create foreign keys to that in other tables that need to refer to people.
As for what you should do in your C# code, we can't really tell you because we don't know what your C# code does.
-
Jun 20th, 2018, 03:53 AM
#3
Thread Starter
Addicted Member
Re: Normalization for SQLite database
Hi, thanks for the reply. I mean should I do something like:
Code:
+----+-----------+------------+----------+-------------+
| id | sometable | othertable | name | surname |
+----+-----------+------------+----------+-------------+
| 1 | 1 | 1 | anyname1 | anysurname1 |
+----+-----------+------------+----------+-------------+
| 2 | 1 | 1 | anyname2 | anysurname2 |
+----+-----------+------------+----------+-------------+
| 3 | 1 | 1 | anyname3 | anysurname3 |
+----+-----------+------------+----------+-------------+
| 4 | 2 | 2 | anyname4 | anysurname4 |
+----+-----------+------------+----------+-------------+
| 5 | 2 | 2 | anyname5 | anysurname5 |
+----+-----------+------------+----------+-------------+
| 6 | 2 | 2 | anyname6 | anysurname6 |
+----+-----------+------------+----------+-------------+
| 7 | 2 | 3 | anyname7 | anysurname7 |
+----+-----------+------------+----------+-------------+
| 8 | 2 | 3 | anyname8 | anysurname8 |
+----+-----------+------------+----------+-------------+
| 9 | 2 | 3 | anyname9 | anysurname9 |
+----+-----------+------------+----------+-------------+
I'm not a man of too many faces
The mask I wear is one
-
Jun 20th, 2018, 05:50 AM
#4
Re: Normalization for SQLite database
No. You would only have the first, fourth and fifth columns. The other tables would then have foreign keys to that id column.
-
Jun 20th, 2018, 06:31 AM
#5
Thread Starter
Addicted Member
Re: Normalization for SQLite database
I messed up everything sorry
I'm not a man of too many faces
The mask I wear is one
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|