First normal form and attachement questions??

wasaan · July 16th, 2003, 10:52 AM

Greetings,
I'm designing a database, and I'm running through the following problems:
1. I need to have multi fields for attachements in a table that has other different fields, so I called the fields attachement1, attachement2, attachement3,...,attachement5. This obviously violates the First Normal Form. By reading "SQL Server 2000 Database Design" book for Louis Davidson (Wrox), he stated that "To overcome all of this, we would create a child entity to hold the values int he malformed entity". Does that mean that I need to create a separate ONE child table for the attachements fields only? Any suggestions?
2. Is there a way in SQL server or ASP to handel attachements? I'm trying to avoid attachements by forcing the user to enter hyperlinks to documents rathter than attachement, but I was wondering if there is any way that I can design the table to have attachement?
Your help is really appreciated. Thanks.

Jeff Mason · July 16th, 2003, 12:51 PM

You would create a table which contains the attachment(s). This child table would have information to identify the attachment, the attachment itself (or better a reference to the attachment which itself is stored elsewhere) and information which relates the attachment back to your main table, i.e. the child table contains a foreign key relationship back to your main table. I'm making some gross assumptions about the nature of your data, since you supplied no information about that:

Code:

CREATE TABLE YourMainTable(
    MainTableKey varchar(10) NOT NULL PRIMARY KEY,
    ...);

CREATE TABLE Attachments(
    AttachmentKey varchar(10) NOT NULL PRIMARY KEY,
    MainTableKey varchar(10) NOT BULL REFERENCES YourMainTable(MainTableKey),
    Attachment varchar(xx) NOT NULL,
    ...);

What is the nature of the "attachments" you refer to? If they are large, or contain binary data (an image, for example) then many people (me included) think it is better to store the attachment in the filesystem someplace, and only store the path to the attachment in the database. It is possible to store large images or large text (up to 2GB each) in the database (see the 'text' and 'image' datatype in BOL), but I personally don't recommend that. Others have disagreed and say they have had success storing the "attachment" in the database directly without incident. YMMV.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

wasaan · July 17th, 2003, 09:43 AM

Jeff,
Thank you very much for replying to my question. The attachment documents can be of any Microsoft type document, e.g., Word, Excel, PowerPoint, etc. Thanks.