July 4, 2008 at 12:12 am
Hi All,
my co-worker came to my seat Couple of days back. He has showed one database model diagram and pointed out some tables and explained the need of that tables.
Table structure:
CustNumber
ManagerID
CustName
custAge
ShareAmt-Investment
FixedIncomeAmt-Investment
CashAmt-Investment
EnteredUserID
EnteredDatetime
The above table will capture the particluar customer's investment related amounts.
Sample record:
1001
10
Raj
50,000$
25,000$
0
Mark
02/July/2008
He asked me 'is there any thing that we need to change in the design', Simply he asked my suggestion about the DB MODEL.
I suggested to include InvestmentIndicator column, becuase,in future,if we want to know the following details
who are all invested in share and fixedincome ?
who are all invested in all components ?
who are all invested in share component only ?
If our InvestmentIndicator column capture the details in comma seperated value , we can get the answer for the above question very easily.
Say for example,
InvestmentIndicator
S,FI,C
S
S,FI
we can form our query like
select * from where InvestmentIndicator = 'S,FI,C'
select * from where InvestmentIndicator = 'S,FI'
select * from where InvestmentIndicator = 'S'
suppose if we don't have this column
we need to write our query like
select * from
where ShareAmt > 0
and Fixed-IncomeAmt > 0
and Cash > 0
As i though, the above my suggested method is good in performance than the second method,i suggested to include InvestmentIndicator column.
Whether my suggestion is correct or not.
As i am new to Database design, i am expecting your suggestions & feedback eagerly.
karthik
July 4, 2008 at 12:32 am
Two of the rules of database design and normalisation that you need to consider here are:
- All columns should contain atomic entries
- You should not store redundant data in a table.
The suggestion of a comma-delimited list in a single column violates the first. It it often suggested as a 'solution', but tends to cause mor problems than it solves.
we can form our query like
select * from where InvestmentIndicator = 'S,FI,C'
select * from where InvestmentIndicator = 'S,FI'
select * from where InvestmentIndicator = 'S'
And what if you want to find all the people who have the FI investment? Suddently your queries become a lot more complex.
Also, the addition of the column violates the second rule, as you have two places in one table that store the same info. What happens if you get a case where a record has an InvestmentIndicator = 'S,FI', but has Fixed-IncomeAmt = 0? Does the person have a Fixed Amount Income or not? One column says he does, one column says he doesn't.
Further comment:
I would actually suggest (not knowing anything about the rest of the design) that you break the investment amounts out into another table. Consider what will happen currently if a new investment type is added. You'd have to change the design of the table and all code that refrences it.
consider the splitting your current table into 2 as follow:
Customer
CustNumber
ManagerID
CustName
custAge
EnteredUserID
EnteredDatetime
CustomerInvestments
CustNumber
InvestmentType
InvestmentAmount
--
Make any sense?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2008 at 12:45 am
Gila,
The suggestion of a comma-delimited list in a single column violates the first. It it often suggested as a 'solution', but tends to cause mor problems than it solves.
how ?
what will happen currently if a new investment type is added
It is a constant one, No more changes will come in future.
karthik
July 4, 2008 at 1:24 am
karthikeyan (7/4/2008)
Gila,The suggestion of a comma-delimited list in a single column violates the first. It it often suggested as a 'solution', but tends to cause mor problems than it solves.
how ?
Think how easy/hard it will be to indicate that a person has taken a new investment type. What happens if the values are listed in the 'wrong' order? Suddenly your equality match no longer works. Querying for people that have a specific (but only one) type of investment now required string manipulation rather than a simple predicate.
If you want an example, given the comma-delimited list you suggested, please tell me how you would find the following:
All people who have either a FixedIncomeAmt-Investment or a CashAmt-Investment
As a second example, you have a customer that has a Share investment and a cash investment (so the InvestmentIndicator looks like 'S, C'. now he's taken a fixed income investment as well. How would you update the InvestmentIndicator column?
what will happen currently if a new investment type is added
It is a constant one, No more changes will come in future.
No offense, but I've heard that one before, usually followed a few weeks later by someone asking how to add a new item that they had said would never be added.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2008 at 12:41 am
Hi Karthik,
I would like to strongly agree with Gail Shaw suggestions with respect to splitting into 2 tables.
and as per you if any new investment type will add, That will be added in your Master table not in your trans table( if you have the master table)
As of now, this table structure looks like a transaction table. So there will be no issue in your future.
and As per your suggestion: -
we can form our query like
select * from where InvestmentIndicator = 'S,FI,C'
select * from where InvestmentIndicator = 'S,FI'
select * from where InvestmentIndicator = 'S'
I would suggest take 3 columns rather than comma type of value.
Two of the rules of database design and normalization that you need to consider here are:
- All columns should contain atomic entries
- You should not store redundant data in a table.
The suggestion of a comma-delimited list in a single column violates the first. It it often suggested as a 'solution', but tends to cause mor problems than it solves.
For below question.
you may follow this concepts for a better design.
who are all invested in share and fixedincome ?
who are all invested in all components ?
who are all invested in share component only ?
add 3 column as share (S), fixedincome(FI) , components(C) ,
and make entries as 1 and 0 values,
if 1 then present and 0 for Not present,
It will give you below advantage:
1- Lets assume, you wants to find only the customer invested in share or fixedincome , components? and viceversa too?
2- Its also give you an opportunity to add one new Investment type with out touching your previous structure. you can add in the last columns..
3- If data is in int type better to compare and fetch the data.
etc...
Please let me knows if any concerns...
Cheers!
Sandy.
--
July 16, 2008 at 7:57 am
"add 3 column as share (S), fixedincome(FI) , components(C) ,
and make entries as 1 and 0 values,
if 1 then present and 0 for Not present,"
Don't add columns for different data. Think relationally like Gila.
Read this.
July 16, 2008 at 8:51 am
on an aside - why store age as a column - would DOB be better?
MVDBA
July 16, 2008 at 10:32 am
Karthik;
It is impossible to properly assess the correctness of a particular database design absent the myriad requirements, external constraints, and complete business rules applicable to the conceptual entities under consideration. Take with a grain of salt any design advice based on the scant information you have provided in your opening post. See http://www.dbdebunk.com/page/page/3042693.htm.
If you have been thrust into a position of being responsible for database design without experience (as your post seems to indicate), I would urge you to lobby your supervisor to obtain professional training in the discipline. It's interesting to me that we would never expect, for instance, an orthopedic surgeon to be able to competently perform an emergency triple-bypass operation absent experience in cardiac surgery, but software managers expect application programmers to be able to do database design without formal training.
TroyK
July 16, 2008 at 11:00 pm
umailedit,
Don't add columns for different data. Think relationally like Gila.
Read this.
I have just gone through this links, and I think for the above DB design situation its not required. Because Karthik has already specified that there is no more investment types will be added in future.
2nd, If somebody will use as comma as separator in value, then its not a good practise to use it. Instead of that he can use a XML column type and stored value which will be more preferable than comma data.
just think about search condition and time of DML operations, I think you may find the difference. I already worked on this, thats why I suggested like this, Initially I used comma like this in one of my project, but I faced a lot problem in the time of enhancement as well as time of querying of data.
I just specified this as a pre - permanent DB design solution with out more normalization. somebody can refer as in the URL too.
Cheers!
Sandy.
--
July 17, 2008 at 12:14 am
Sandy (7/16/2008)
I have just gone through this links, and I think for the above DB design situation its not required. Because Karthik has already specified that there is no more investment types will be added in future.
I had that exact situation a few months back. A table that the system architect designed had 5 different models in it (each in a different column). I suggested that he break it out into a separate table.
"No need," he said, "We'll never add another model"
Two weeks later, he's at my desk asking for advice on adding another model to the table.
It's a suggestion. Take it, leave it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 17, 2008 at 12:47 am
Gila
------------------------------------------------------------------------------------------
I had that exact situation a few months back. A table that the system architect designed had 5 different models in it (each in a different column). I suggested that he break it out into a separate table.
"No need," he said, "We'll never add another model"
Two weeks later, he's at my desk asking for advice on adding another model to the table.
I can only say, Its purely depends on the application requirements and the way you are going to use this.
I can give you some situation where you may find the difficulty with this design.
Just imagine, if you don't have user interface to add the master data.
and you are going to add one extra transaction table for this investment type details and when you make a insert in the 1st transaction table you need to insert all the child table too.
It's a suggestion. Take it, leave it.
we are here to share our best knowledge and experience, not the question of taking it or leaving it, we need to accept the correct one.
I appreciate you for your quick and correct answer and also I am learning from you too. I will be more happy if you correct me if i am wrong anytime.
Cheers!
Sandy.
--
July 17, 2008 at 7:44 am
No need for people to get snippy. The take away here is that:
1) If a design is normalized, relational schema, the design can still be valid if the requirements change
2) Sql is designed to be efficient in processing atomic values, not concatenated strings to be parsed
3) No matter how much you stress that something won't change, it will at some point
4) If the data is normalized, it is easier to extract information out of the data
That being said, sometimes it is more difficult or prohibitively slow to process data in related tables than in a flat table or data stored as a concatenated value. You should be aware of the specific limitations you impose on the structure by de-normalization.
There are always trade offs. In my experience, if you are designing something that will have a known long or indefinite lifespan, you (or application programmers) will experience significant pain or a needless re-factoring if you do not build on normalized data.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply