Results 1 to 5 of 5

Thread: Normalization for SQLite database

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    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:
    1. CREATE TABLE "tbl_artist" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "txt_artist" VARCHAR(256));
    2. CREATE TABLE "tbl_category" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "txt_category" VARCHAR(256));
    3. CREATE TABLE "tbl_compiler" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "txt_compiler" VARCHAR(256));
    4. CREATE TABLE "tbl_measure" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "txt_measure" VARCHAR(256));
    5. CREATE TABLE "tbl_notation_person" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "txt_notation_person" VARCHAR(256));
    6. CREATE TABLE "tbl_preparer" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "txt_preparer" VARCHAR(256));
    7. CREATE TABLE "tbl_press" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "txt_press" VARCHAR(256));
    8. CREATE TABLE "tbl_region" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "txt_region" VARCHAR(256));
    9. 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,
    10.                 FOREIGN KEY (int_category_id) REFERENCES tbl_category(id),
    11.                 FOREIGN KEY (int_taken_from_id) REFERENCES tbl_taken_from(id),
    12.                 FOREIGN KEY (int_compiler_id) REFERENCES tbl_compiler(id),
    13.                 FOREIGN KEY (int_notation_person_id) REFERENCES tbl_notation_person(id),
    14.                 FOREIGN KEY (int_press_id) REFERENCES tbl_press(id),
    15.                 FOREIGN KEY (int_region_id) REFERENCES tbl_region(id),
    16.                 FOREIGN KEY (int_source_person_id) REFERENCES tbl_source_person(id)
    17.                 FOREIGN KEY (int_measure_id) REFERENCES tbl_measure(id)
    18.                 FOREIGN KEY (int_artist_id) REFERENCES tbl_artist(id));
    19. CREATE TABLE "tbl_source_person" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "txt_source_person" VARCHAR(256));
    20. 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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    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.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    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

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    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.

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Oct 2013
    Posts
    200

    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
  •  



Click Here to Expand Forum to Full Width