Jedna duża tabela czy kilka mniejszych?

Witam,

Mam takie pytanie. Załóżmy że mam zgrupować pewne dane. Powiedzmy że jest to około 35-40 pól. Dane będą wyświetlane w ten sposób, że użytkownik widzi tylko podstawowe rekordy, a klikając na widok szczegółowy może oglądać zawartość pozostałych.

I teraz pytanie jak najlepiej to zaplanować. Czy lepiej wrzucić wszystko do jednej tabeli o tylu polach ile jest danych - wtedy jedno zapytanie by służyło tylko do wybrania podstawowych danych i wypełnienia nimi tabelki widzianej przez użytkownika. Następnie użytkownik klika na szczegóły i kolejne zapytanie pobiera szczegóły wybranego rekordu (czyli wszystkie pola).

A może lepiej rozbić to na kilka tabel, powiązanych kluczem - jedna tabela zawierałaby tylko podstawowe dane a druga dodatkowe.

Które wyjście jest lepsze? W tej chwili używam SQLite i wydaje mi się, że klucze obce nie są obsługiwane - ale chce wiedzieć jaka jest teoria na ten tamat (może zresztą zmienię używaną bazę danych).

Zakładamy, że baza może być dosyć duża.

SQLite nie implementuje mechanizmu kluczy głównych, przez co pozbawiasz się integralności referencyjnej. Jeżeli to ma być coś na wzór tabeli master-detail to polecam się rozbić na dwie tabele. Jedna zawiera nagłówki, przez co jest mała i szybka. Druga zawiera dane szczegółowe. Musisz jednak zapewnić integralność danych z poziomu aplikacji. Nie jest to o tyle trudne co upierdliwe. Niemniej jednak ja jeżeli tylko widzę korzyści z dodatkowej tabeli to robę takową. Tak więc jeżeli o mnie chodzi to podzieliłbym to na mniejsze porcje.

Jeżeli chodzi ci o primary key to sqlite3 jest. W jaki sposób najlepiej zapewnić integralność danych (chodzi mi sam pomysł, nie o kod).

Bo sprawa wygląda tak: jest osoba - głowne wartości to imię, nazwisko, data urodzenia (przykładowo). To jedna tabela.

Druga tabela to informacje pozostałe. Czyli każdy wiersz tabeli głównej odpowiada wierszowi tabeli ze szczegółami.

Czy jedyny sposób powiązania tego ze sobą to pilnowanie, żeby przy każdym zapytaniu dodawać/usuwać/zmieniać te same wiersze dwóch tabel? Czy wystarczy wyszukać rekord o takim samym primary key (ID) w tabeli ze szczegółami? Chodzi mi o to czy jest pewność że w obydwu tabelach klucz będzie taki sam w odpowiadających wierszach, skoro nie są one ze sobą a żaden sposób powiązane.

No i jakie (konkretnie) korzyści daje rozbicie na 2 tabele - wzrost wydajności, szybkości działania?

Co do kluczy, nie chodzi mi o autoinc primary key tylko o foreign key. Co do pilnowania integralności. Tu z pomocą przychodzi transakcja. Skoro masz dwie tabele, których nic nie pilnuje to musisz mieć pewność aby oba zapytania do obu tabel się wykonały lub żadne. Najważniejsze jest usuwanie. Nie możesz usunąć pola master, wcześniej nie usuwając wszystkich wierszy detail bo wówczas będziesz mieć ID nie odpowiadające niczemu. Ja robiłem to na zasadzie (jakby co to Delphi):

CREATE TABLE Master (
  IDMaster INTEGER PRIMARY KEY NOT NULL,
  MasterField VARCHAR(10)
);

CREATE TABLE Detail(
  IDDetail INTEGER PRIMARY KEY NOT NULL,
  MasterID INTEGER NOT NULL,
  DetailField VARCHAR(10)
);

BeginTransaction();
try
  INSERT INTO Master (IDMaster, MasterField) VALUES (NULL, 'Kapusta');
  GetLastInsertedRow() // u mnie API SQLite posiada takową funkcję
  // Ważne aby pobrać ID które właśnie wstawiłeś - tu właśnie jest kontrola integralności. Tak jak uzbroisz procedurę 
  // pobierania ID tak będziesz miał odporność na błędy
  INSERT INTO Detail (IDDetail, MasterID, DetailField) VALUES (NULL, PobranyOstatniID, 'Wincenty');
  Commit();
except
  RollBack();
end;

To samo z usuwaniem. Masz wybrany jakiś rekord do usunięcia czyli znasz jego ID.

BeginTransaction();
try
  DELETE FROM Detail WHERE MasterID = IDMaster;
  DELETE FROM Master WHERE MasterID = PobranyMasterID
  Commit();
except
  RollBack();
end;

To na razie na tyle. Jakby co to pisz śmiało na GG. Jestem po 15.

Dzieki za odpowiedzi - gdybym mial jeszcze jakies watpliwosci to nie nie omieszkam spytac ;-)

Osobiscie korzystam z wrappera sqlite3:
http://www.codeproject.com/database/CppSQLite.asp?df=100&forumid=34722&exp=0&select=1030939

Wglebiac sie w api sqlite jakos mi sie nie chcialo (wiecej dlubania, malo czasu - język C a nie C++), ale kto wie moze warto.

[edit]
Poszperalem troche w dokumentacji - dla sqlite3. API sqlite3 zawiera funkcje sqlite3_last_insert_rowid, ale nie potrzebuje jej uzywac, bo wrapper (nawiasem mowiac, bardzo wygodny jak dla mnie) zawiera metode CppSQLite3DB::lastRowId().

Jeszcze jedno. Jeżeli zamierzasz zainsertować w pętli sporą ilość danych również opakuj w transakcję. Ta baza ma specyficzną cechę a mianowicie do każdego inserta otwiera plik, sprawdza indeksy, tworzy backupy i bóg wie co jeszcze. Wówczas inserty w pętli trwają wieki. podczas opakowania w transakcję plik otwierany jest raz i wszystkie czynność administracyjne również wykonują się raz. Dzięki temu inserty zamiast 20s trwają 20 ms. To taki mały Off-Topic. Swojego czasu długo nad tym siedziałem jak obejść ten problem.

Ok, czyli w moim przypadku wystarczy

execDML("begin transaction;"); 
execDML("commit transaction;");

z klasy CppSQLite3DB.

P.S. Do czego sluza funkcje Commit(); i RollBack();? Widze ze tez nie podajesz tu bezposredniego api do sqlite, tylko masz jakiegos wrappera - domyslam sie ze Commit to potwierdzenie zapytania.

Tak, Commit() to Zapisz, RollBack() to cofnij z jakichś przyczyn. Ja mam to zrealizowane na klasach

  TSQLiteDatabase = class
  private
    fDB: TSQLiteDB;
    fInTrans: boolean;
    procedure RaiseError(s: string; SQL: string);
  public
    constructor Create(const FileName: string);
    destructor Destroy; override;
    function GetTable(const SQL: string): TSQLiteTable;
    procedure ExecSQL(const SQL: string);
    function GetTableValue(const SQL: string): int64;
    function GetTableString(const SQL: string): string;
    procedure UpdateBlob(const SQL: string; BlobData: TStream);
    procedure BeginTransaction;
    procedure Commit;
    procedure Rollback;
    function TableExists(TableName: string): boolean;
    function GetLastInsertRowID: int64;
    procedure SetTimeout(Value: integer);
    function version: string;
  published
    property isTransactionOpen: boolean read fInTrans;
  end;

  TSQLiteTable = class
  private
    fResults: TList;
    fRowCount: cardinal;
    fColCount: cardinal;
    fCols: TStringList;
    fColTypes: TList;
    fRow: cardinal;
    function GetFields(I: cardinal): string;
    function GetEOF: boolean;
    function GetBOF: boolean;
    function GetColumns(I: integer): string;
    function GetFieldByName(FieldName: string): string;
    function GetFieldIndex(FieldName: string): integer;
    function GetCount: integer;
    function GetCountResult: integer;
  public
    constructor Create(DB: TSQLiteDatabase; const SQL: string);
    destructor Destroy; override;
    function FieldAsInteger(I: cardinal): int64;
    function FieldAsBlob(I: cardinal): TMemoryStream;
    function FieldAsBlobText(I: cardinal): string;
    function FieldIsNull(I: cardinal): boolean;
    function FieldAsString(I: cardinal): string;
    function FieldAsDouble(I: cardinal): double;
    function Next: boolean;
    function Previous: boolean;
    property EOF: boolean read GetEOF;
    property BOF: boolean read GetBOF;
    property Fields[I: cardinal]: string read GetFields;
    property FieldByName[FieldName: string]: string read GetFieldByName;
    property FieldIndex[FieldName: string]: integer read GetFieldIndex;
    property Columns[I: integer]: string read GetColumns;
    property ColCount: cardinal read fColCount;
    property RowCount: cardinal read fRowCount;
    property Row: cardinal read fRow;
    function MoveFirst: boolean;
    function MoveLast: boolean;
    property Count: integer read GetCount;
    // The property CountResult is used when you execute count(*) queries.
    // It returns 0 if the result set is empty or the value of the
    // first field as an integer.
    property CountResult: integer read GetCountResult;
  end;