Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 16th, 2003, 10:52 AM
Registered User
 
Join Date: Jun 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default First normal form and attachement questions??

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.
 
Old July 16th, 2003, 12:51 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old July 17th, 2003, 09:43 AM
Registered User
 
Join Date: Jun 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
form with conditional questions jcrowley PHP How-To 0 January 30th, 2007 12:02 AM
Form email Questions John10Yes ASP.NET 2.0 Basics 4 September 15th, 2006 10:58 AM
3rd normal form dBbob SQL Server DTS 0 June 8th, 2005 11:29 AM
Access Form Questions sario777 Access 2 April 12th, 2005 06:38 PM
Javascript Form Questions The_Jackal Javascript How-To 0 August 24th, 2004 12:50 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.