473,695 Members | 1,685 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

design question

I've got a file I need to load into a table. The file contains
modifiers to a fast food order. For example, if you ordered a burger
and asked for no onions, the no onions request would be in the modifier
file.

So, the problem is there are "fake" duplicates in the file. What I
mean is this:
The file contains a Header ID, Detail ID and Modifier ID. Sometimes
the Modifier ID is duplicated for a certain detail record. But...it's
not a true duplicate. The record also contains what is modified. So,
the "No Onions" record might have a modifier ID of 1. For the same
detail line, there might be another Modifier record of "Add Cheese".
But that will also have the Modifier ID of 1. It doesn't happen very
often. And yes, it should be fixed in the program that creates the
files. But that's not happening.

The primary key on the table (I'm not allowed to change it) is the
Header ID, Detail ID, Modifier ID, Store #, and Business Date.

What I've done is moved the duplicates to a temporary file and inserted
the rest of the records. Next, I'm assigning new Modifier IDs to these
"duplicated " records, and then inserting them. This is working fine
for me up to a point. The problem is when the file is loaded twice.
These "duplicated " records are inserted again. And now they really are
duplicates, even though they have different modifier IDs. And no, I
don't expect the file to be loaded twice, but you just never know.
This is bugging me and I'm not really sure of a way to get around it.
I thought I'd throw it to the group here to see if anyone has run into
this before. Any ideas are appreciated.

Thanks,
Jennifer

Jan 2 '07 #1
2 1130
Jennifer wrote:
I've got a file I need to load into a table. The file contains
modifiers to a fast food order. For example, if you ordered a burger
and asked for no onions, the no onions request would be in the modifier
file.

So, the problem is there are "fake" duplicates in the file. What I
mean is this:
The file contains a Header ID, Detail ID and Modifier ID. Sometimes
the Modifier ID is duplicated for a certain detail record. But...it's
not a true duplicate. The record also contains what is modified. So,
the "No Onions" record might have a modifier ID of 1. For the same
detail line, there might be another Modifier record of "Add Cheese".
But that will also have the Modifier ID of 1. It doesn't happen very
often. And yes, it should be fixed in the program that creates the
files. But that's not happening.

The primary key on the table (I'm not allowed to change it) is the
Header ID, Detail ID, Modifier ID, Store #, and Business Date.

What I've done is moved the duplicates to a temporary file and inserted
the rest of the records. Next, I'm assigning new Modifier IDs to these
"duplicated " records, and then inserting them. This is working fine
for me up to a point. The problem is when the file is loaded twice.
These "duplicated " records are inserted again. And now they really are
duplicates, even though they have different modifier IDs. And no, I
don't expect the file to be loaded twice, but you just never know.
This is bugging me and I'm not really sure of a way to get around it.
I thought I'd throw it to the group here to see if anyone has run into
this before. Any ideas are appreciated.

Thanks,
Jennifer
You could use DTS or Integration Services or some other ETL tool to
manipulate the data as it is loaded. Or you could load the data to a
working table and then populate your actual table using an INSERT
statement. Those are the two most common options for complex data
loads.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Jan 2 '07 #2

"Jennifer" <J.**********@g mail.comwrote in message
news:11******** **************@ n51g2000cwc.goo glegroups.com.. .
I've got a file I need to load into a table. The file contains
modifiers to a fast food order. For example, if you ordered a burger
and asked for no onions, the no onions request would be in the modifier
file.

So, the problem is there are "fake" duplicates in the file. What I
mean is this:
The file contains a Header ID, Detail ID and Modifier ID. Sometimes
the Modifier ID is duplicated for a certain detail record. But...it's
not a true duplicate. The record also contains what is modified. So,
the "No Onions" record might have a modifier ID of 1. For the same
detail line, there might be another Modifier record of "Add Cheese".
But that will also have the Modifier ID of 1. It doesn't happen very
often. And yes, it should be fixed in the program that creates the
files. But that's not happening.

The primary key on the table (I'm not allowed to change it) is the
Header ID, Detail ID, Modifier ID, Store #, and Business Date.

What I've done is moved the duplicates to a temporary file and inserted
the rest of the records. Next, I'm assigning new Modifier IDs to these
"duplicated " records, and then inserting them. This is working fine
for me up to a point. The problem is when the file is loaded twice.
These "duplicated " records are inserted again. And now they really are
duplicates, even though they have different modifier IDs. And no, I
don't expect the file to be loaded twice, but you just never know.
This is bugging me and I'm not really sure of a way to get around it.
I thought I'd throw it to the group here to see if anyone has run into
this before. Any ideas are appreciated.

Thanks,
Jennifer
Three possibilities:
1: Pre-process the entire file before loading, re-numbering any duplicates
found in a predictable manner so a second load would generate the same key
values and all duplicates would be rejected.
2. Use the "Modifier" text as part of a virtual primary key during the
secondary load.
3. Learn to live with your duplicates and use select distinct to "roll up"
multiple 'Add Cheese' requests.
Jan 3 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
674
by: Don Vaillancourt | last post by:
Hello all, Over the years as I design more database schemas the more I come up with patterns in database design. The more patterns I recognize the more I want to try to design some kind of generic design patterns that can be used and shared amongst many sub-schemas. For example, the grouping of entities. I may have the following tables: employee, product and client. These tables have no direct relationship with each other. But...
9
2930
by: sk | last post by:
I have an applicaton in which I collect data for different parameters for a set of devices. The data are entered into a single table, each set of name, value pairs time-stamped and associated with a device. The definition of the table is as follows: CREATE TABLE devicedata ( device_id int NOT NULL REFERENCES devices(id), -- id in the device
2
2441
by: Test User | last post by:
Hi all, (please excuse the crosspost as I'm trying to reach as many people as possible) I am somewhat familiar with Access 2000, but my latest project has me stumped. So, I defer to you experts. I've been asked to create a Daily Log sheet to be distributed to some of our clerks. For each day, the clerk is to log tasks worked on for the day, (i.e worked on the johnson account).
6
2113
by: rodchar | last post by:
Hey all, I'm trying to understand Master/Detail concepts in VB.NET. If I do a data adapter fill for both customer and orders from Northwind where should that dataset live? What client is responsible for instantiating the orders class? Would it be the ui layer or the master class in the business layer? thanks,
17
2700
by: tshad | last post by:
Many (if not most) have said that code-behind is best if working in teams - which does seem logical. How do you deal with the flow of the work? I have someone who is good at designing, but know nothing about ASP. He can build the design of the pages in HTML with tables, labels, textboxes etc. But then I would need to change them to ASP.net objects and write the code to make the page work (normally I do this as I go - can't do this...
17
4845
by: roN | last post by:
Hi, I'm creating a Website with divs and i do have some troubles, to make it looking the same way in Firefox and IE (tested with IE7). I checked it with the e3c validator and it says: " This Page Is Valid XHTML 1.0 Transitional!" but it still wouldn't look the same. It is on http://www.dvdnowkiosks.com/new/theproduct.php scroll down and recognize the black bottom bar when you go ewith firefox(2.0) which isn't there with IE7. Why does...
6
2134
by: JoeC | last post by:
I have a question about designing objects and programming. What is the best way to design objects? Create objects debug them and later if you need some new features just use inhereitance. Often times when I program, I will create objects for a specific purpose for a program and if I need to add to it I just add the code.
0
2074
by: | last post by:
I have a question about spawning and displaying subordinate list controls within a list control. I'm also interested in feedback about the design of my search application. Lots of code is at the end of this message, but I will start with an overview of the problem. I've made a content management solution for my work with a decently structured relational database system. The CMS stores articles. The CMS also stores related items --...
19
3167
by: neelsmail | last post by:
Hi, I have been working on C++ for some time now, and I think I have a flair for design (which just might be only my imagination over- stretched.. :) ). So, I tried to find a design certification, possibly that involves C++, but, if not, C++ and UML. All I could find was Java + UML design certifications (one such is detailed on http://www.objectsbydesign.com/tools/certification.html). Although UML is expected to be language independent,...
8
2225
by: indrawati.yahya | last post by:
In a recent job interview, the interviewer asked me how I'd design classes for the following problem: let's consider a hypothetical firewall, which filters network packets by either IP address, port number, or both. How should we design the classes to represent these filters? My answer was: class FilterRule {
0
8623
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8977
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8822
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7656
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6488
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4339
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4577
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2997
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
1971
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.