DB designing from performance point of view. [message #587557] |
Mon, 17 June 2013 01:26 |
Manoj.Gupta.91
Messages: 239 Registered: March 2008 Location: Delhi
|
Senior Member |
|
|
Hi All,
Need your guidance in designing below application's database.
Currently I'm working on a new project that will be developed for Bus Transportation where a card & money will be used as a medium of transaction rather than money only. As the number of travellors are much more in a day we will be expecting about 5,00,000 transactions in a day. I want to discuss about table design (Partitions, Tablespaces, Indexes etc.) for this considering below points.
I would like to discuss from the point of view of data organization and future performance.
(1) Transaction volume per day 5,00,000.
(2) User has an Account and Card as well as other information like Name, DOB, Gender etc. stored in database master table.
(3) There will be a transaction table that captures daily transactions.
(4) User data can be accessed using Account No. OR Card No. OR Name etc. from transaction table.
(5) User can ask for his last 6 months transaction details.
(6) How to keep historical data i.e. normally in such systems recent 1 year of data is accessed more frequently. Very few requirements are for data which is older than 1 year. This may be in case of some legal inquires etc.
(7) Should I move data older than 1 year to a history table. I mean should we have keep history table for this or we can simply go on adding new partitions to our transaction table and use different tablespaces.
(8 Keeping point 4 in mind how should I create index in transaction table (Local, Global) etc.
I've tried to give a brief idea about to application. Please help me with your ideas in designing DB for this.
Thanks & Regards
Manoj
|
|
|
Re: DB designing from performance point of view. [message #587560 is a reply to message #587557] |
Mon, 17 June 2013 01:37 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Manoj, are you seriously asking for advice regarding a real-world issue? If so, the best I can give is suggest that you hire a project leader who understands the system development life cycle. For example, your points 6, 7, and 8 are relevant to the system design stage. You cannot answer them until you have completed your the business analysis and systems analysis stages.
it looks to me as though you may be trying to start coding before you have determined what you need to achieve and how to do it.
|
|
|
|
Re: DB designing from performance point of view. [message #587568 is a reply to message #587560] |
Mon, 17 June 2013 01:57 |
Manoj.Gupta.91
Messages: 239 Registered: March 2008 Location: Delhi
|
Senior Member |
|
|
Hi John,
Thanks for your prompt response.
I'm seriously asking for advice regarding a real-world issue?
Although our project leader will join us soon. Currently we are into a stage of system analysis and documentation. But from developer we have been asked to study, gather and discuss our views related to points I mentioned. So I'm putting some brief idea here on this forum to collect some expert ideas which will help me to study, understand, plan and address such concerns. Your ideas will work like a pointer for me to learn.
Hi Mike,
Normalization is a very basic thing which anyhow will be done. So I didn't mention it. If you go through the points I mentioned you will realize that what I'm asking is more than Normalization and Simple Partitioning. In such application Partitioning is obvious thing to be done.
(1) How can we plan partitioning in an effective way from performance point of view?
(2) Should we store each partition in a separate tablespace?
(3) Should we go for sub partitioning and store each subpartition in separate tablespace? Or store all subpartitions belonging to one main partition in one tablespace?
(4) If we create table partition on the basis of data (Partition for each month or so) then how will we address user queries coming on the basis of Account Number. Should we go for a global index for account number?
These are few questins I'm going through. Your expert views will help me to know and learn and plan real world scenarios.
Thanks & Regards
Manoj.
|
|
|
Re: DB designing from performance point of view. [message #587569 is a reply to message #587568] |
Mon, 17 June 2013 02:04 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:Although our project leader will join us soon. Currently we are into a stage of system analysis and documentation. But from developer we have been asked to study, gather and discuss our views related to points I mentioned. This is exactly what I meant: you are doing things in the wrong order. When your project manager arrives, if he is any good he stop what you are doing and complete the business analysis. Then he will do the systems analysis (which includes the data analysis and normalization, data flow diagramming, and all the rest). Only then will he start system design, which you are trying to do already. Take it slowly! At the moment you can have no idea if any sort of partitioning necessary. Perhaps you dont even need Enterprise Edition.
|
|
|
|
Re: DB designing from performance point of view. [message #587573 is a reply to message #587572] |
Mon, 17 June 2013 02:17 |
Manoj.Gupta.91
Messages: 239 Registered: March 2008 Location: Delhi
|
Senior Member |
|
|
Hi,
You must understand Hiring is not in my control. I've been hired and till the time project leader join us I don't have much work assigned to me. I just wanted to utilize my free time by studying and experimenting.
That's all I wanted to do.
Thanks & Regards
Manoj
|
|
|
|
Re: DB designing from performance point of view. [message #587578 is a reply to message #587573] |
Mon, 17 June 2013 02:32 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
That changes things, Manoj: this become learning exercise. You could, for example, try to do some of the business analysis. Design a business process, such as buying a ticket (who? how? why? when? where?) Then move on to systems analysis, and model it with entity-relationship and data-flow diagrams. You can't really do any system design work (which is when you answer the sort of questions you are asking) until all the processes have been modeled. If you can present your incoming PM with one perfectly analyzed business process, he will be a happy person.
Enjoy! I am envious of you having time for this. Me, I have to start making some money...
|
|
|