I'm designing my first SQL Server database and wonder if there's a better way to accomplish what I'm trying to do.
The goal is to be able to create one of 14 documents based on 200+ different document sections (titles, headings, paragraphs, lists, etc). Each document section is part of 1 or more documents.
My application does a single database lookup for a particular document and retrieves the data stored in the 50 text fields.
To do this, I first stored each unique document section in a "sections" table, giving each section a unique identifier (sectionID) and made the identifier a primary key, for example:
dbo.sections
sectionID(pk) sectionText
iv1 this is the text for the first section
AHv1 this text is for another section
APv2 more text to include
.
.
.
EFv3 another text section
GHv2 this is the last section text in the table
I then created a second table called "documents" to store each document name and the sections that belong to it. There are 51 columns in this table. The first column is the document name and the other 50 columns store the id's of the sections (they're named section1, section2, ...) that make up that particular document. Each of the section columns are foreign keys that reference the primary key in the "sections" table, for example:
dbo.documents
docID section1(fk) section2(fk) ... section50(fk)
option1 iv1 AHv1 ... GHv2
option2 iv1 APv2 ... EFv3
All of this seems straightforward to me. However, in order to get the text for each document to be part of a given record, I have to create a view that does 50 joins of the sections table. By doing that, each document id and its text are stored in one row of a table.
Is there a better way to get the same end result? Or a better design? It seems like there may be a lot of overhead to join the data between tables.
Any input would be greatly appreciated!
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire