TR Forums

So, I've read the documentation and this subject left my head spinning. What purpose does a schema serve and why would I want to use it?

Ironically I had a page long writeup for you but GG I refreshed the browser by mistake. This time around, I'm going to keep it short.

Back in the day, programs were large unwieldly things, Kline (Thousand Line) was the billing style. To keep track of what these large complex programs did, Flowcharts were born. This allowed you to trace the path of information from start to finish in a program. For projects it looked great and was a nice visual to keep everyone on track. It was also supremely helpful when troubleshooting problems because you could see all the points the data followed and track at which point it got screwed up.

Fast forward to 2008 and Flowcharts are largely dead from a programming perspective. Object Oriented systems use a variety of new types of Flowcharts, that while perform the same function in practice, allow for the use of the Object Oriented style of programming.

Schema is specific to database, but I have seen people shoehorn a Schema to cover a web page system and it wasn't pretty. Let's keep it simple. Schema is Database, and Database only. A schema is similar to a flowchart but for a database. All of your tables in your database will be prepresented in your Schema. Each table will have its name, and all the fields listed in it. Sometimes these fields will also have their datatype listed on the Schema, but not always. The Schema will also provide lines to indicate which fields are linked to each other. These links tell you that Field 4 from Table 6 is linked to Field 1 in Table 14, and what kind of link it is. This is important for Projects because before any code is written or even the Database itself built, a Schema must exist. Without a Schema most database projects would be too unwieldly to comprehend where everything connects. Instead your Engineers and Architects sit around and build the Schema, and the Schema serves as the blueprint when all the tables are being written, setup, built, and put into the database. It also tells you which fields to link and where to link them. It also tells you what need to come first because primaries need to be built before secondaries and foreign fields. If Table 1 has a Primary Key that is referenced in Table 6, 7, and 8. Then Table 1 needs to be built before Table 6, 7, or 8, otherwise they would be unable to reference the Table 1 Primary Key.

Schema's also allow the coding and creation tasks to be sent to another team that does nothing but write code and build tables. In some cases, there are modern tools that you feed the Schema into and it outputs the code to the most part, which cuts down on the time required and possibility of stupid syntax errors when creating the database system.

For a visual of a schema see http://en.wikipedia.org/wiki/File:Media ... schema.svg

This is quite possibly the best birthday present ever. (As of February 23, I'm 28 years old.)

I have ofew more questions for you:
why would I want more than one schema in a single database? (I'm not talking the system as a whole, but the individual database that the system would hold.)

When I initially read the documentation, it made it sound like schemas were akin to ACLs. Did I read it wrong?

That's all I can think of for the moment.

There's two different definitions of what schemas are (as they pertain to databases). Firstly, a database schema can refer to the logical structure of the database, defining table structures and relationship, as Kenobi defined. The second is a named group of objects inside a database. For example, you have a group of tables in a database that all deal with one thing, and a second group that deal with another, though they do have some interconnected links. You could put the tables into two different schemas. As an example, the AdventureWorks sample database that SQL Server 2005+ uses has several schemas in it.


The PostgreSQL Manual details why you might want to seperate your tables into schemas:

There are several reasons why one might want to use schemas:

• To allow many users to use one database without interfering with each other.
• To organize database objects into logical groups to make them more manageable.
• Third-party applications can be put into separate schemas so they cannot collide with the names of other objects.

Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.

Schemas can encompass more than tables, also including things like Views and Procedures.

Okay, as I walked away after reading your post, emorgoch, to get another cup of coffee, a light flashed in my head. I might understand this now. Looking at it now, I don't know why I was have such a hard time understanding this. I guess I just needed a few pictures.

In my own words here, I'm going to relate how I understand the topic. Schemata are not necessary to have a fully functional database. They do, however, make things more organized from a conceptual standpoint, neater from a privilege standpoint, and reduce the possibility of collisions. So, rather than having a long list of tables, they can be grouped together based on how closely related they are -- i.e. these tables are all accounting related and these tables are all warehouse related -- even though all the tables may relate to one another. Instead of granting an individual user access to several tables, I can grant that same user access to a single schema. The use of schemata will allow multiple identical table names to exist. For example, using the accounting and product schemata, accounting can have table named products and warehouse can have a table named products. Finally, doing things in such a way allows the guy who comes in behind me, assuming I was lucky enough to be the guy who designed the fictitious database in the first place, to easily see what's going on.

So, can I get a job as a DBA now?

P.S.: I found out that schemata is the preferred plural form of schema.