creation of records with respect to the delimited data from a column of base table [message #633695] |
Tue, 24 February 2015 05:28 |
|
saralay
Messages: 1 Registered: February 2015 Location: Pune
|
Junior Member |
|
|
Hi,
I want to create a Child table with respect to the data from the base table.
For eg, I have a base table called employee in which the data is as shown below,
BAse table Employee:
EmpId EmpGroup EmpDept
1 A;B;C Oracle
2 X;Y;Z DBA
Child Table Employee_1;
EmpName EmpDept
A Oracle
B Oracle
C Oracle
X DBA
Y DBA
Z DBA
I have to create a child table, in which the number of records are taken with respect to data(delimited) from a column(EmpGroup) of basetable(Employee).
Please help me with the query.
Thanks in advance!!!!
|
|
|
|
|
|
|
Re: creation of records with respect to the delimited data from a column of base table [message #633823 is a reply to message #633804] |
Wed, 25 February 2015 07:31 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
There are really only two kinds of Relational Database Designs:
1. Designs that follow Normalization Theory.
2. Designs that due to the nature of the data, do not from a practical perspective allow us to follow Normalization Theory. One (though not the only) response to this problem is the "data as columns" solution that some people use.
Said another way:
1. do we store data as "modeled"
2. or "un-modeled".
In the end this depends upon how you want to use it.
In a database design where data has been previously modeled, everthing must be "addressable". That means, every piece of information must be locatable. Thus every piece of information has a unique name, and ties back to an unambiguous KEY. Your first step in design is to identify these names and keys.
In a traditional database design, every key maps back to one entity. This one entity can be implemented as multiple entities if necessary; one obvious example of this being SUPER-TYPE / SUB-TYPE designs, but it is still one entity because there is only one key and all sub-types map back to that one key but just as a specialized version of the master entity type. It is not THIS simple due to the possibility of overlapping sub-types (?) (did they call this multiple-inherentance?), but we try to avoid the more complicated design theories unless they really provide some significant value to the end product.
You said there was only ONE key for all the data you were getting.
If this is REALLY true, then there is only one entity in your design (which sounds totally bogus but who knows) and all data you have is a part of that entity. This brings us to the nullable and/or super-type/sub-type question we asked before to which you said "no nulls" (no nulls is not really the end of super-type/sub-type design of course but we ignore this for now).
However, the ONLY ONE KEY remark, suggests that you are simply not analyzing the data sufficiently. What is this ONE key? Is it artificial (a surrogate)? If so then let me point out another fact.
Keys can be SYSTEM IDENTIFIERS or BUSINESS IDENTIFIERS. When you design a data model, you design using BUSINESS IDENTIFIERS. SYSTEM KEYS are almost exclusively sequence numbers and are not real keys to data. SYSTEM KEYS are technically added later as part of an access or physical implementation strategy and each database may do them differently.
So let me ask you this:
What are the BUSINESS KEYS to your data?
Is there more than one? If not what is the one?
Have you taken the time to actually model your incoming data? Sounds like maybe not yet.
You remakred something like "we do not know all the data till after we have analyzed it". Well that is design. Your comment suggests that you have not modeled anything. So that leads to another question taking us back to the begining.
Do you want to store your data MODELED or UN-MODELED?
Un-modeled data cannot have SQL used against it. Since it is un-modeled, it is basically just a blob unknown stuff until it is used. This leaves applications using this data to have to analyze it every time they access it. There are some systems that do this but they are generally very specialized in nature and they may not work very fast.
Which leads us to one more question group.
How will you use this data if it is not modeled?
What processes are you expecting to support?
Who are the end users that will use what you create?
What are the problems ehse end users are trying to solve?
What kind of application are you expecting to create to help them solve these problems?
These questions help you understand what you are working withing with and thus when data can be modeled or un-modeled.
Kevin
|
|
|