Analysis: How to put this in tables?

Hi,
I have some question:
How do I put the folowing stuff into tables:
210004000231023551079690000463 1000000000010000230204841010060
2302040370100
23000400020000000000000000000000 BANQUE ACCORD
59110 LA MADELEINE FR 000
210004000331023551079690000463 1000000000022000230204841010060
2302040370100
23000400030000000000000000000000 BANQUE ACCORD
59110 LA MADELEINE FR 000
210004000431023551079690000463 1000000000008000230204841010060
2302040370100
23000400040000000000000000000000 BANQUE ACCORD
59110 LA MADELEINE FR 000
210004000531023551079690000463 1000000000008400230204841010110
2302040371100
23000400050000000000000000000000 BANQUE ACCORD
59110 LA MADELEINE FR 001
310004000631023551079690000463 84101000100430003 01100 00001408804 11
0 1
310004000731023551079690000463 841010001001BANQUE ACCORD
0 1
310004000831023551079690000463 841010001001RUE JEANNE MAILLOTTE 4/6
0 1
310004000931023551079690000463 84101000100159110 LA MADELEINE FR
0 1
310004001031023551079690000463 841010001002COM 2EME SEM
0 1
310004001131023551079690000463 841010001005SOGEFRPPLLM
0 1
310004001231023551079690000463 841010000 Pi ce justificative en annexe
Votre ordre de paiemen t de 1 0
3200040012 22.000,00 EUR R f rences de l'op ration:
H1040223K007969 Frais du ba nquier correspon 1 0
3300040012dant 1 0,00 EUR Commission de
paiement 0 1
It's really simple:
- 21-records: these are records beginning with "21": they are the
'main'-records containing the information
- 22 and 23-records starting with 22 or 23: details of the 21-record
- 31, 32, 33-records: also details of the 21-records.
positions in 21-record:
- On position 35 I have an indiciation of Credit/Debit
- 83-90: Coding of the 21-record
- 91: Strucutred or Non-Structured.
-> I need these 3 things to identify in some way the 21-record.
Now my question is: I have to put all this stuf in tables, and I need to be
able to do easily and fast query's with it.
I guess the most performant way would be to put every record in it's own
kidn of table: so I would create an tbl21, tbl22, tbl23, tbl31, etc Liek
that I would be able to put the Credit/Debit, Coding etc in there own Column
and than I can do some fast query's on it.
But on the other hand: When I put everything in different tables, it will be
more difficult to have a nice overview of the whole file (21 + 22 + 23 +
... ), and to be able to know which cmae after which one etc.
So I thought about putting everything in one table.
like this:
CREATE TABLE [dbo].[tblExtractChild128] (
[ExtractID] [int] NOT NULL , -> unique ID
[ParentID] [int] NULL , -> indicates which is
the
Parent of the 22, 23, 31-record (so the ID of the 21-record)
[Identification] [varchar] (2) COLLATE French_CI_AS NULL , ->
"21",
"22", "23", "31", "32", "33": to easily see which type of record it is
[Text] [varchar] (128) COLLATE French_CI_AS NULL , ->
contains the whole Line ("210004000231023551079690000463
1000000000010000230204841010060
2302040370100"
[CD] [bit] NULL
-> position 35 of the 21-line: Credit/Debit
[Amount] [decimal](18, 0) NULL
-> Amount = value in EURO of the record
[Coding] [varchar] (8) COLLATE French_CI_AS NULL , ->
83-90:
Coding of the 21-record
[Structured] [bit]
-> 91: Strucutred or Non-Structured.
) ON [PRIMARY]
GO
In which I put all the records, and for the 21-records I use the Columns CD,
Coding and Structured, for the others I don't.
Like this I can do query's:
SELECT DISTINCT r21.*
FROM tblExtractChild128 r21 LEFT JOIN tblExtractChild128 r23 ON (r21.ID =
r23.ParentID)
LEFT JOIN tblExtractChild128 r31 ON (r21.ID = r31.ParentID)
WHERE (r21.Identification = '21')
AND (r21.Coding= '84101006')
AND (r22.Text LIKE '%BANQUE ACCORD%')
AND (r31.Text LIKE '%RUE JEANNE MAILLOTTE%')
Ok, this works: but I have my doubts if this is really the best way to do
all this stuff. Is there a way which will have much more performance? Work
nicer, more easily for maintenance etc?
For those who read this untill the end and try to formulate an answer:
thanks a lot! hehe :-)
PieterPieter,
You may want to start explaining what this data is all about. Then start
identifying what the entity types, their attributes and relationships among
them. Without such information, it is hard to provide any worthy
suggestions.
Anith