Converting to Sql Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Converting to Sql Server

Author

Topic

Fishy
Starting Member

5 Posts

Posted - 2009-01-29 : 12:40:14

Hi,

I'm converting a program that uses different types of files to store data. IE. Flat files, random access files, MS Access files. Many of the files stores record numbers of other files to access.

It's quite a mess

but I think I can convert the to Sql Server 2008.

One problem I have is that the user can save and load individual files and if the file they load in causes inconsistancies the program still works, it just doesn't find certain pieces of information.

So, has anybody else had to deal with this type of problem?

Or, do you have any ideas that would help me structure the database to work correctly?

Thanks,

Fishy.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2009-02-12 : 13:31:19

quote:
Originally posted by Fishy

Hi,

I'm converting a program that uses different types of files to store data. IE. Flat files, random access files, MS Access files. Many of the files stores record numbers of other files to access.

It's quite a mess but I think I can convert the to Sql Server 2008.

One problem I have is that the user can save and load individual files and if the file they load in causes inconsistancies the program still works, it just doesn't find certain pieces of information.

So, has anybody else had to deal with this type of problem?

Or, do you have any ideas that would help me structure the database to work correctly?

Thanks,

Fishy.

So, are you intending to use the filestrewm data type to store this data?

quote:
One problem I have is that the user can save and load individual files and if the file they load in causes inconsistancies the program still works, it just doesn't find certain pieces of information.

what programs? Sounds like there should be validation of the files, by the programs?

There nneds to be a LOT more information to even begin to think about how we could help design a DB for this?

*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

Fishy
Starting Member

5 Posts

Posted - 2009-02-13 : 11:39:36

First of all, thank you for replying. The Filestream data type looks very interesting and it may be usefull for getting around the 4gb db limit of Express.

So, a little background. I'm converting a vb6 program to vb.net 2008. Currently, he uses a varity of methods to store data. He has Jet, flat files, and index files. Also, he stores many of these files as zip files which the vb6 program zips and unzips.

The program is used for designing funiture. An example of what the user can do is as follows:
He can design a piece of furniture and select from a list of cabinet door types. Those cabinet door types are stored in a file. So, the cabinet design has a reference to the door type that is to be used. The user can at any time restore a file that contains different door types. If the door type in the restored file is not found then, the vb6 program uses a default 'unknown' type. So, there is not really referential integrity at that point. So, how would I want to design that portion of the database? Can you setup a FK that may not have an entry in the PK table?

Thanks,

Fishy

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2009-02-16 : 04:53:19

You can't set up an FK that doesn't have an entry in the PK, EXCEPT for the use of NULL:


create table PK (pk_i int not null primary key)

create table FK (fk_i int identity (1,1), pk_i int null)

alter table FK 
	add constraint FK_PK foreign key (PK_I) references PK (Pk_i)

insert into PK (pk_i) values (1)

insert into FK (pk_i) values (1)
insert into FK (pk_i) values (1)
insert into FK (pk_i) values (NULL)
insert into FK (pk_i) values (1)
insert into FK (pk_i) values (NULL)
insert into FK (pk_i) values (1)

select * from PK
select * from FK

--results
fk_i	pk_i
1	1
2	1
3	NULL
4	1
5	NULL
6	1

insert into FK (pk_i) values (2)
--results:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_PK". The conflict occurred in database "master", table "dbo.PK", column 'pk_i'.
The statement has been terminated.

Does that help?

*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources