<<<
this table contains 10 records ,and I need to insert these records into 2
tables .TBSTUDENT and TBCOURSE.
These two tables TBSTUDENT and TBCOURSE, are the tables I was directing my
comments to... In your last post, you did not mention them at all... Instead
you describe THREE Tables that are destinations for the data.
<<<<<<
...wanted to simplify things here are the
schema of the 3 other tables I am trying to insert into.
CLIENT, PROGRAM_ENROLLMENT, and PERSONAL_INFO...
So now I am triply
. In any event, in order to help, it isnecessary to have some idea about what the schema actually is, and what
abstraction (What real world biusiness "THING") each record in the table
respresents. Normally the name of the table helps with that, but when this
is noot obvious, then we have to guess based on what the joins in the code
that is uploaded for us to look at.
So: What exactly does an individual record in each of these three tables
represent?
And What does each row in the Import table represent ? (Is that the table
with schema F_NAME L_NAME COURSE_ID COURSE_DESC ')
Regards,
Charly
"RayAll" wrote:
> Thanks for your reply.
> No,The schema of the table is not odd ,because this is a buffer table whic
h
> I amusing in a DTS package to read records from a CSV file into this table
> (and do the validation during transformation) and then from here I have to
> distribute the records into the relavant tables.Dose it still seem to be
> odd?
> I made the chema myself ,because I wanted to simplify things here are the
> schema of the 3 other tables I am trying to insert into.
> **************CLIENT:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[PE_CLI_FK]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[PROGRAM_ENROLLMENT] DROP CONSTRAINT PE_CLI_FK
> GO
> CREATE TABLE [dbo].[CLIENT] (
> [INTERNAL_ID] [numeric](12, 0) IDENTITY (1, 1) NOT NULL ,
> <--this is the identity field
> [CLIENT_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ......
> ) ON [PRIMARY]
> GO
>
> ***********PROGRAM_ENROLLMENT:
> CREATE TABLE [dbo].[PROGRAM_ENROLLMENT] (
> [PROGRAM_ENROLLMENT_ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
> [INTERNAL_ID] [numeric](12, 0) NOT NULL , <--This is
> foriegn key to the Client Table
> ...
> ) ON [PRIMARY]
> GO
> ***********PERSONAL_INFO:
> CREATE TABLE [dbo].[PERSONAL_INFO] (
> [FIRST_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [MIDDLE_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ....
> [INTERNAL_ID] [numeric](12, 0) NOT NULL , <--this is also a foriegn
> key to Client
> ....
> [Timestamp] [timestamp] NOT NULL
> ) ON [PRIMARY]
> GO
>
> ******************
> Here is what I'm trying to do:
> I am trying to get each record from my buffer table and insert it into the
se
> three tables,but before inserting into the Programe_enrolment and
> Personal_info ,I have to have the InternalID of the Client Table,then I
> should use the internalID in every other table I insert the rest of the
> record.
> For instance let's say this is my record in buffer table:
> FirstName clientID UserName
> Ray 1233 ray5531 ............
> First I insert this into client and I get the internalID of for instance
> 1000 ,then I need to use this and insert other fields of this record into
> Personal_Info and PROGRAM_ENROLLMENT.
> Same process for all the other records in the buffer table.
>
> Thanks for your time.
>
>
>
>
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:A31711F6-1A30-4DDC-9741-5EF81D96CE62@.microsoft.com...
>
>I'm sorry if I made you
.However I think I am getting close to wht Iwant(I will discuss it later)
Forget about the first post.
Ok,
Client: represents a client of our affiliates it says that this clientID
,with this AfflicateID (unique key on this two)has this Internal_ID
.Internal_ID now represents this person in all other tables.
so far so good?
Personal_Info :represents the personal information about this
guy(Unfortunately it is in another table and it's related to the client by
internal_ID) ,information like spouse name,last date he kissed somebody and
bluh bluh bluh is stored in this table
Program_enrolment :contains information about programs that client has
participated so far,this table contains programe_code ,program_startdate and
bluh bluh bluh and again it's related to the client table using Internal_ID
Is that clear so far?
Now ,each record in CSV file which I later import it to a buffer table
contains all these information in one line.for instance it says that this
guy who belongs to this affiliate and has enroled in this program and his
wife name is "Eve" and last time he kissed somebody was yesterday and bluh
bluh bluh.so for inserting this record into our database I need to sperate
each records into different portions .First I have to insert some fields
into client Table and get our own internalID ,because I need them later in
other Tables.I am not using only surrogate key in client Table because
"ClientID and Afflicate Id" is also unique.
based on this I have some idea ,I'm not sure if it works( I got the idea
from David's post,right above us)
Do you think there is something wrong with this schema up to now?
I insert into the client table ,it gives the record an identity(Internal_ID)
(which I don't care for now).
then I join my import Table with Client Table based on "ClientID and
Afflicate Id" and extract Internal_ID and insert it into two other tables.
How about this?
Thanks
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:8D5178C2-0D79-4B11-A817-59B840C86B04@.microsoft.com...
> Your original post mentioned two tables as destinations for the data,
> <<<
> this table contains 10 records ,and I need to insert these records into 2
> tables .TBSTUDENT and TBCOURSE.
> These two tables TBSTUDENT and TBCOURSE, are the tables I was directing my
> comments to... In your last post, you did not mention them at all...
> Instead
> you describe THREE Tables that are destinations for the data.
> <<<<<<
> ...wanted to simplify things here are the
> schema of the 3 other tables I am trying to insert into.
> CLIENT, PROGRAM_ENROLLMENT, and PERSONAL_INFO...
> So now I am triply
. In any event, in order to help, it is> necessary to have some idea about what the schema actually is, and what
> abstraction (What real world biusiness "THING") each record in the table
> respresents. Normally the name of the table helps with that, but when
> this
> is noot obvious, then we have to guess based on what the joins in the code
> that is uploaded for us to look at.
> So: What exactly does an individual record in each of these three tables
> represent?
> And What does each row in the Import table represent ? (Is that the table
> with schema F_NAME L_NAME COURSE_ID COURSE_DESC ')
> Regards,
> Charly
>
> "RayAll" wrote:
>|||See InLine...
"RayAll" wrote:
> I'm sorry if I made you
.However I think I am getting close to whtI
> want(I will discuss it later)
> Forget about the first post.
> Ok,
> Client: represents a client of our affiliates it says that this clientID
> ,with this AfflicateID (unique key on this two)has this Internal_ID
> ..Internal_ID now represents this person in all other tables.
> so far so good?
No, not good already... If this table represents a client... then unique key
should be on CLientID by itself, not ClientID and AffiliateID... The fact
that your key is on CLientID AND AffiliateID means that mutiple records can
exist for the same ClientID, so this table CANNOT represent Clients, it must
represent ASSOCIATIONS (of what type I don't know) between CLients and
Affiliates. And if it does, then there has to be another two tables
somewhere - one whose records actually do represesnt CLIENTS, and another
table whose records represent AFFILIATES...|||The reason we have Client Table is that each client might belong to
different affiliates ,so there is a single peice of info kept in
Personal_INFO and here in client Table we say this client belongs to this
affiliate and this is our internal_ID if we want more information.
Dose that make sense?
Thanks,
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:B80EA846-73D1-4FDC-81DA-987A1D75A78D@.microsoft.com...
> See InLine...
> "RayAll" wrote:
>
> No, not good already... If this table represents a client... then unique
> key
> should be on CLientID by itself, not ClientID and AffiliateID... The fact
> that your key is on CLientID AND AffiliateID means that mutiple records
> can
> exist for the same ClientID, so this table CANNOT represent Clients, it
> must
> represent ASSOCIATIONS (of what type I don't know) between CLients and
> Affiliates. And if it does, then there has to be another two tables
> somewhere - one whose records actually do represesnt CLIENTS, and another
> table whose records represent AFFILIATES...
>|||Do you see that your CLIENT Table is really a CLIENT-AFFILIATE ASSOCIATION
("belonging") Table, and that your Personal_INFO table is (should be) the
CLient table ?
This is because each record in the CLIENT able represents, not a client, but
an association between a Client and an Affiliate...
And does each record in the Personal_INFO table actually represent a Client?
If it did, the Primary key in the Personal_INFO table woul be ClientID, not
InternalID... The way you have it is entirely possible that 2 records in the
CLIENT Table, for the SAME CLient, (with same ClientD) could "point" to 2
DIFFERENT rows in the Personal_INFO table...
So is there a table in your system that actually IS a Client Table? i.e.,
it has CLientID as it's unique Primary Key, and there is one and only one ro
w
per client in it?
"RayAll" wrote:
> The reason we have Client Table is that each client might belong to
> different affiliates ,so there is a single peice of info kept in
> Personal_INFO and here in client Table we say this client belongs to this
> affiliate and this is our internal_ID if we want more information.
> Dose that make sense?
> Thanks,
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:B80EA846-73D1-4FDC-81DA-987A1D75A78D@.microsoft.com...
>
>|||See inline please
> Do you see that your CLIENT Table is really a CLIENT-AFFILIATE ASSOCIATION
> ("belonging") Table, and that your Personal_INFO table is (should be) the
> CLient table ?
Yes,you are right ,but you should know that there are some semantics which
is beyond this discussion.I think the naming for Client is not good actually
,but the role is palying is right.
> And does each record in the Personal_INFO table actually represent a
> Client?
yes,it dose
> If it did, the Primary key in the Personal_INFO table woul be ClientID,
> not
> InternalID... The way you have it is entirely possible that 2 records in
> the
> CLIENT Table, for the SAME CLient, (with same ClientD) could "point" to 2
> DIFFERENT rows in the Personal_INFO table...
The internal_ID of the Personal_INFO is not an identity ,it's getting its
value from InternalID of Client and there is a one-to-one relationship
between them,how would it be possible to have multiple client_ids there?can
you give me an example?
> So is there a table in your system that actually IS a Client Table? i.e.,
> it has CLientID as it's unique Primary Key, and there is one and only one
> row
> per client in it?
>
No,that's the only table represeting the Client.
Thanks
> "RayAll" wrote:
>|||<The internal_ID of the Personal_INFO is not an identity ,it's getting its
<value from InternalID of Client and there is a one-to-one relationship
<between them,how would it be possible to have multiple client_ids there?can
<you give me an example?
Yes, the PK in the CLIENT table is NOT CLientID, it is ClientID AND
AffiliateID.
As you said, some clients are associated with more than one Affiliate, in
that case, won't there be multiple rows in CLIENT with the same ClientID,
(one for each of the Affiliates the client isassociated with)?
And each of these rows will have it's own unique InternalID, no?
So then, Do these Multiple InternalIDs (ALL FOR THE SAME CLIENT, right?), do
they not point to different rows in the Personal_INFO table'
Multiple rows in Personal_INFO for the SAME CLIENT, then, right ?|||
> Yes, the PK in the CLIENT table is NOT CLientID, it is ClientID AND
> AffiliateID.
> As you said, some clients are associated with more than one Affiliate, in
> that case, won't there be multiple rows in CLIENT with the same ClientID,
> (one for each of the Affiliates the client isassociated with)?
> And each of these rows will have it's own unique InternalID, no?
yes,right
> So then, Do these Multiple InternalIDs (ALL FOR THE SAME CLIENT, right?),
> do
> they not point to different rows in the Personal_INFO table'
yes,right.because in personal_info the key is Internal_ID (which is coming
from Client) and Education_level (which is another field) that make the rows
unique.I think the second field is the key.
> Multiple rows in Personal_INFO for the SAME CLIENT, then, right ?
yes but with different education_level.
>
Thanks for following this up.|||So Personal Infp also has an education_Level as part of the key as well'
That adds another twist to the puzzle... Bottom line, you really, REALLY
need to read a book or article or something about database design, and
database normalization... REGARDLESS Of the level of control you might have
over the database, or opportunity to redesign it properly.
The current design schema of your database is incredibly bad, and
uncorrectted, it will produce a nightmare of coding problems, data
inconsistency issues and maintenance headaches... The more you know about wh
y
and how it is messed up, the better you will be prepared to deal with these
issues, as they arise, even if you have no authority or approval from client
to correct them..
Good Luck !!
Charly
"RayAll" wrote:
>
> yes,right
> yes,right.because in personal_info the key is Internal_ID (which is coming
> from Client) and Education_level (which is another field) that make the ro
ws
> unique.I think the second field is the key.
> yes but with different education_level.
>
> Thanks for following this up.
>
>|||Thanks for your help.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:D2DBA4FE-90AA-48A7-8B1C-E73367A61610@.microsoft.com...
> So Personal Infp also has an education_Level as part of the key as well'
> That adds another twist to the puzzle... Bottom line, you really,
> REALLY
> need to read a book or article or something about database design, and
> database normalization... REGARDLESS Of the level of control you might
> have
> over the database, or opportunity to redesign it properly.
> The current design schema of your database is incredibly bad, and
> uncorrectted, it will produce a nightmare of coding problems, data
> inconsistency issues and maintenance headaches... The more you know about
> why
> and how it is messed up, the better you will be prepared to deal with
> these
> issues, as they arise, even if you have no authority or approval from
> client
> to correct them..
> Good Luck !!
> Charly
> "RayAll" wrote:
>
No comments:
Post a Comment