Database

Oracle Multiple schemas Vs single schemas

4-Mar-11 12:49

Then I found a simpler way when I actually worked the query.

SELECT TOP 2
  contentID,
  RC  
FROM
  (SELECT
    contentID,
    SUM(readsCounter) RC
   FROM
    Book1 AS B
   --WHERE ReadDate BETWEEN this AND that
   GROUP BY
    contentID) D
ORDER BY RC desc

Note the where clause will be required when the data set is larger

Never underestimate the power of human stupidity
RAH

V.2-Mar-11 20:11

2-Mar-11 20:11

We're building a new system (consisting of multiple applications) from scratch and use Oracle as a database.
The problem is that we don't know how big it will grow and what new decisions (from managers) we will have to go through in the future.

My opinion is that we just use one schema to start out with and see where we get from there. My colleague (coming from SQL-Server background) opted the idea to use multiple schemas. One for each different application in the system plus a config and common (=for all applications) schema. His main reason being security.
(But what if a table that starts belonging to one application later needs to be shared among applications?)

Another option would be to seperate tablespaces, but use the same schema.

I'm not sure what the best design is (and I'm no Oracle expert either). We don't know were we'll go with this system, it might grow internationally, it might die next year. My experience tells me that you should design very well, but without overkill.

Do you have experience with designing databases and what is your take on this? (Pro's/Con's). Google does have some examples, but mostly they are when different companies need to access the same database. This is not our case.

Many thanks in advance.

List of common misconceptions

Jörgen Andersson2-Mar-11 21:08

Jörgen Andersson

2-Mar-11 21:08

V. wrote:
His main reason being security.

Good reason.

V. wrote:
But what if a table that starts belonging to one application later needs to be
shared among applications?)

Then you have to set the access rights accordingly and add the schema to the tablename when accessing it.
Like this: Select * from schemaname.tablename

V. wrote:
Another option would be to seperate tablespaces, but use the same
schema.

Thats something completely different. That's a logical storage space[^].

Mycroft Holmes2-Mar-11 22:17

2-Mar-11 22:17

I'm pretty sure tablespaces have nothing to do with your segregation of data, it is only for logical storage.

We have just run across a real need for additional schemas, we want to move the production data (ETLd from other systems) to the dev server, if the data was in different schemas this would be simple, now we have to reengineer the database schemas to meet that requirement.

Security is another issue so I would go for the additional schemas from the start. As for moving from 1 schema to anoter I believe there are tools to help (Toad) and it is just the price you pay for getting it wrong (or changing requirements).

Never underestimate the power of human stupidity
RAH

PIEBALDconsult3-Mar-11 1:56

PIEBALDconsult

3-Mar-11 1:56

The only place I worked with Oracle we had one big happy database and it was fine. Keep it simple.

Wendelius4-Mar-11 10:28

4-Mar-11 10:28

Hi,

Somekind of list of thoughts on this issue:
- security is a good point. Although security can be enforced on many different levels you can use owner permissions for example in packages. This helps to handle common security problems.
- logical design. If you separate the objects to different schemas based on the data they hold, it's easier to understand the database design (and usage) especially in bigger databases.
- backups and other DDL operations. Many of the operations can be done schema based. For example you can export a certain schema so this helps in administrative tasks
- usage in applications. This is two folded, you can either define the schema in applications (like SELECT ... FROM schema.table) or you can use public synonyms to hide the structure. Public synonyms are bad in situations where you actually need for example a table with the same name twice in the database but otherwise they should simplify the use in the apps.
- isolation. Using different schemas you can isolate different portions of the database more easily (a bit same as the security) but again this can be done in multiple ways.

V. wrote:
Another option would be to seperate tablespaces, but use the same schema

This is a totally different issue. Tablespace is used to define the actual storage place for an object regardless of the schema. A simple situation is that you have a table and an index on it. In optimal case they are stored on different disks so they would be stored on different tablespaces even though they both would be defined in the same schema. So tablespace is just a way to define physical storage place without defining the actual file (since tablespace can contain multiple files).

The need to optimize rises from a bad design.My articles[^]

V.4-Mar-11 10:48

4-Mar-11 10:48

This is in fact what we did finally. Created schema per application and a common one holding configuration data and parameters etc. For that schema we made public synonyms.

After everything was moved I had to change very little to my prototypes to make them work Smile | :)

thanks for the advice.

Wendelius4-Mar-11 10:54

How to write a SQL query to present values horizontally sql server 2005

4-Mar-11 10:54

No problem Smile | :)

The need to optimize rises from a bad design.My articles[^]

Tridip Bhattacharjee1-Mar-11 20:10

Tridip Bhattacharjee

1-Mar-11 20:10

Suppose I have one table called Jobs:

CREATE TABLE [Jobs]
(
[JOBID] [int] IDENTITY(1,1) NOT NULL,
[PARTDESC] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrderPlacedBy] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SpecialistName] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Priority] [int] NOT NULL,
[Symptoms] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomerNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShopNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[JobType] [nvarchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[JobState] [nvarchar](32) COLLATE Latin1_General_CI_AS NULL,
[JobAddedDate] [datetime] NOT NULL,
[JobStartedDate] [datetime] NULL,
[JobFinishedDate] [datetime] NULL,
[JobShippedDate] [datetime] NULL,
[RecievedDate] [datetime] NULL
)

I want to see the specialist name and his jobs IDs horizontally.

ANA 201,502,605,701,774
BEN 102,103,051
JEN 705,401,402,509,409,408

A specialist may have n jobs. Suppose specialist ANA has 10 jobs where BEN has 5 jobs.

In this way I want to show specialist his jobs horizontally where the number of jobs may vary per specialist.

How can I do this in SQL?

tbhattacharjee

Re: How to write a SQL query to present values horizontally sql server 2005

Jörgen Andersson1-Mar-11 20:27

Jörgen Andersson

1-Mar-11 20:27

Make a search on Pivot[^].

List of common misconceptions

Re: How to write a SQL query to present values horizontally sql server 2005

Mycroft Holmes1-Mar-11 21:49

Re: How to write a SQL query to present values horizontally sql server 2005

1-Mar-11 21:49

There are some excellent articles [^]here on CP (blatant self promotion here)

Never underestimate the power of human stupidity
RAH

Wendelius4-Mar-11 10:37

Re: How to write a SQL query to present values horizontally sql server 2005

4-Mar-11 10:37

Must say that you found an excellent article for this issue ! Smile | :)

The need to optimize rises from a bad design.My articles[^]

PIEBALDconsult2-Mar-11 2:18

PIEBALDconsult

2-Mar-11 2:18

With Sql Server, you can write your own aggregate function[^].

And you spelled received wrong.

database design

wjbjnr1-Mar-11 9:27

wjbjnr

1-Mar-11 9:27

i am recently new to database designs..i have been working on samples but never designed a sql database...please help with a tutorial or recommend a book so that i can design a database and use linq in c# to access it...by designing a database .simply meaning creating plain tables and manipulating data in it..i have sql server management studio 2008 and have visual studio 2010 ultimate also...please guide towards a simple and easy way to get started..

Mycroft Holmes1-Mar-11 21:52

1-Mar-11 21:52

You have asked one huge question. Try doing some research.

Heres some Google foo[^] for you

Then try a C# tutoral and a Linq tutorial. You have the word you just need to do the research.

Never underestimate the power of human stupidity
RAH

thatraja2-Mar-11 12:32

thatraja

2-Mar-11 12:32

Also this will help you, start with this.

Library of Free Data Models from DatabaseAnswers.org[^]

thatraja

My Tip/Tricks
My Dad had a Heart Attack on this day so don't...

V.2-Mar-11 20:14

2-Mar-11 20:14

If you have the budget, hire a senior consultant that will explain you in-depth (teach you) the workings of SQL-Server. I had a two day Oracle session not long ago and it gave me some insight into do's/don'ts. It remains difficult to design it still, but it will give you more confidence in what you're about to get yourself into.

Wendelius4-Mar-11 10:34

Can't get my query to work on a form!

4-Mar-11 10:34

It's good that you have excellent tools to administer the database but in order to use the tools correctly you must understand what happens "under the hood", so one of the "must have" books is: http://www.amazon.com/Introduction-Database-Systems-8th/dp/0321197844[^]

Also see the section "Customers Who Bought This Item Also Bought..."

The need to optimize rises from a bad design.My articles[^]

venomation1-Mar-11 3:06

venomation

1-Mar-11 3:06

I am not very good at Microsoft Access's queries, they seem different than standard SQL.
I have the following query:

SELECT COUNT(*) AS Total
FROM CDCopy
WHERE CatalogueNumber=[Please enter the catalogue number to find out how many copies are available];

If I double click the query it shows an input box and the rest works as intended.

The problem is that I don't know how to use it in a form!
I have tried :

MsgBox([GETCDCopyCount]![Total]![CatalogueNumber])

Where "[CatalogueNumber]" is a textbox as I want to pass the text information from that box into the query.

Keep getting error messages, can someone help me out? Big Grin | :-D

Thanks!

if database crashes how to recover in sql server 2008

vinu.111128-Feb-11 21:15

vinu.1111

28-Feb-11 21:15

Hi..

i am using sql server 2008..

if suddenly database crashes then how to recover it?

what about data?

Re: if database crashes how to recover in sql server 2008

Mycroft Holmes28-Feb-11 23:01