Home » RDBMS Server » Performance Tuning » DB designing from performance point of view. (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0)
DB designing from performance point of view. [message #587557] Mon, 17 June 2013 01:26 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #587564 is a reply to message #587557] Mon, 17 June 2013 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Well you ask us to do for free the job for what you are paid.
I'll give you hints:
- take care of Normalization
- partition on date your data

Regards
Michel

[Updated on: Mon, 17 June 2013 01:40]

Report message to a moderator

Re: DB designing from performance point of view. [message #587568 is a reply to message #587560] Mon, 17 June 2013 01:57 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #587572 is a reply to message #587568] Mon, 17 June 2013 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If you go through the points I mentioned you will realize that what I'm asking is more than Normalization and Simple Partitioning.


So you should hire a skilled person to investigate and design (for instance me Smile ).
Forum is not the right place for this.

Regards
Michel

Re: DB designing from performance point of view. [message #587573 is a reply to message #587572] Mon, 17 June 2013 02:17 Go to previous messageGo to next message
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 #587575 is a reply to message #587573] Mon, 17 June 2013 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, use your free time as you want but you must understand it is not possible in a forum to answer your question as there are so many things to ask, so many things to think, so many things to... that you will use OUR free time.

regards
Michel
Re: DB designing from performance point of view. [message #587578 is a reply to message #587573] Mon, 17 June 2013 02:32 Go to previous message
John Watson
Messages: 8922
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...
Previous Topic: Tuning a query with an EXISTS clause
Next Topic: Tuning a SQL query
Goto Forum:
  


Current Time: Fri Mar 29 09:53:18 CDT 2024