Hot answers tagged schema
45
It's called Entity-Attribute-Value (also sometimes 'name-value pairs') and it's a classic case of "a round peg in a square hole" when people use the EAV pattern in a relational database.
Here's a list of why you shouldn't use EAV:
You can't use data types. It doesn't matter if the value is a date, a number or money (decimal). It's always going to be ...
17
I think the best method is to have the database generated as part of your build process. Keep all of the scripts in source control with the rest of the code, and everyone is responsible for their own environments.
Failing that, RedGate has a tool to integrate source control into SSMS and SQL Compare is useful for comparing/synchronizing MS SQL Server ...
11
I started using NORMA (link fixed Oct 2011) which is useful to capture the relationships and constraints in plain English. See Object Role Modelling too.
This will generate XSDs and SQL scripts.
I hope never to see an ERD ever again...
11
Schemas in postgres are used mostly for namespacing and sometimes for security.
Namespacing because two objects can have the same name in different schemas, and are then referenced by schema.object notation - especially useful in conjunction with search_path (many contrib modules do this, eg xml2). Security because you can now do grant ... on all tables in ...
11
It may be a good practice because when you have other users using the database you want to be able to limit their access with schemas. For example in a database you have the following tables.
HR.Payhist
HR.Payscale
HR.Jobdesc
IT.username
IT.useraccesslevel
ENG.jobsite
ENG.trainings
As the HR director I am able to access anything in the HR schema, as the ...
11
Entity Attribute Value (EAV)
It is considered to be an anti-pattern by many, including me.
Here are your alternatives:
use database table inheritance
use XML data and SQLXML functions
use a nosql database, like HBase
11
Normalization absolutely is used in the real world... and hopefully you know that 3NF is only the third one of... what is is now, 8? But 3NF should be an easy target.
However... I would venture to say that there could not be such a tool.
Normalization, technically, is an attribute of each table. Within a given database, different tables may have ...
10
Rolling your own referential integrity checks has the following disadvantages:
Speed - Your own checks will never be as fast as database internal checks.
Completeness- There is always the possibility when you roll your own that you will miss something.
The speed issue is acceptable if the high performance of the database engine can make up for the loss, ...
10
You almost have your answer already:
Create the new structure in parallel
Start writing to both structures
Migrate old data to the new structure
Only write and read new structure
Delete old columns
As for step 3, use something like this (in one transaction):
Insert what is not there yet:
INSERT INTO new_tbl (old_id, data)
SELECT old_id, data
FROM ...
10
What you are describing is a data warehouse. The live, normalized, read-write system is OLTP (online transaction processing) and the denormalized read-only snapshot is a data warehouse. The structure of the data warehouse could be a Star Schema, especially if it's highly denormalized. Data warehouses often have summarization in addition to ...
10
Joel Brown has summed up the nature of a data warehouse. I'll add something here about reporting requirements.
What is a data warehouse good for
A data warehouse is good for analytical reports where you want to calculate aggregates, trends or other statistical or financial metrics over a large volume of data. Generally a periodic load is best for this ...
9
What is the cost of data quality issues down the line from this system? Do those costs outweigh the benefits of using this system over some other system that actually enforces referential integrity?
Unfortunately, there are plenty of applications out there (particularly those of the "database agnostic" variety) that implement their own constraints rather ...
9
eiefai already mentioned A table that should exist in all projects with a database. This is a great blog post, but IMO it only goes part of the way to a working solution for database revision control. I think any attempt to "answer" this question in the real world needs to consider some of the other information about VCS and databases:
The Agile Data ...
9
I'll start by saying don't consider schemas as namespaces or object domains in the OO sense. Schemas are essentially permission containers with some added value (see below)
Also, "separate schemas" or "separate databases" are 2 different concepts. Data that needs to be transactionally and referentially consistent needs to be the in the same database. See ...
8
We use them
to separate permissions per client (eg we have desktop, WebGUI etc schemas)
for logical grouping eg (Data and Staging schemas for our tables).
Useful and practical observations past the white paper mentioned by Marian:
GRANT on the schema: no more permissions per object. So a new proc in the WebGUI schema automatically has the permissions ...
8
Option 1
There are several reasons for this, which I'll explain below. First, here's how to do it.
Use your choice of standard RDBMS platform.
Set up your schema with several user-configurable fields, and make your application facilitate the configuration on a per-tenant basis.
From the per-tenant metadata, you can create a per-tenant view of their data, ...
7
Why don't you COMMENT ON the various SCHEMA components, that way your comments are in the schema, and will get dumped.
COMMENT stores a comment about a database object.
To modify a comment, issue a new COMMENT command for the same object. Only one comment string is stored for each object. To remove a comment, write NULL in place of the text string. ...
7
I think that an answer can be found in this MSDN article: SQL Server Best Practices – Implementation of Database Object Schemas.
Quote:
"This white paper discusses the opportunities for improvements in the security administration of a user database, and it outlines some best practices around using schemas to manage database objects in development and ...
7
Computed columns are your friend. Use them to capture simple calculations you're going to do anyway, and to guarantee that the computed values are correct.
Persist the results if you want to index or filter on them; don't persist them if you just need to pull the value once in a while.
Capture all data constraints using CHECK constraints.
Here is a ...
7
Agree with @gbn. I've architected solutions using schemas for separation and databases for separation, and I find using databases is much more practical. A couple of reasons:
Having your app connect to a context-specific database and then running the same queries is much easier than having your queries inject <schema> in front of all the object ...
7
As far as I know there are no "nosql" databases that promise ACID transactions, so for banking purposes they are a non starter. Referential consistency support is not usually in their key feature sets either.
mySQL claims ACID transactions when using innodb tables, but I believe there are some caveats around that which may be show stoppers (any mix of other ...
7
What you're seeing is mostly one user interface's way of displaying the structure of a PostgreSQL database.
If you were using pgAdminIII, which is just another administrative interface, each database would usually show two "catalogs": information_schema and pg_catalog. It would also have a schema named "public".
Database objects named "pg_*" are system ...
6
There are a couple different angles to approach this question, I think. The "tool-first" angle, I believe, is going to vary based on platform and personal preference. Case in point: I'm using a Database Project in MS Visual Studio, but I'm not sure that this is a great solution for MySQL. I also know people who are pretty sold on their favorite tools from ...
6
ALTER AUTHORIZATION has a more general use for changing ownership of databases, types, keys etc. There is an ENDPOINT example n this MSDN link
ALTER AUTHORIZATION can be used to change the ownership of any entity that has an owner
ALTER SCHEMA does exactly one thing
Transfers a securable between schemas
In this special case you've ...
6
More for ER than for prototyping (never heard it in the context of databases): If you are on MySQL, try MySQL Workbench. Although it generates MySQL specific code, small adjustments are easily made.
For PostgreSQL, there is a nice list in their wiki, PGDesigner lookes very capable. Furthermore, there will be a Designer in future versions of pgAdmin.
6
Quick answer: For developers, you can GRANT CONTROL on that schema.
Background:
To see what can be GRANTed: GRANT Schema Permissions
To see what each permission means: Permissions Naming Conventions
The intersection of these two give the schema permission meanings:
CONTROL implies the rest and is the highest permissions of any securable
SELECT, ...
6
You can use CONNECT THROUGH to allow Dogbert to connect as Wally:
alter user wally grant connect through dogbert;
grant create session to dogbert;
To connect in sqlplus:
sqlplus dogbert[wally]/scottadams@DATABASE
Some tools may not support these proxy logins.
6
Short answer: it depends.
Long answer: the answer depends on several factors. Here are a few:
Does your 700K row table get updated infrequently? If so, I would lean toward pre-calculating the means. If they are not static but change in a regular, predictable interval (e.g., monthly or yearly), pre-calculating may still be a solid answer. If they ...
6
Most database management systems have a hard limit on either the number of columns you're allowed to use, the number of bytes in a row, or both. So your single table won't work in the general case, because you'll eventually either end up with either too many columns or too many bytes in a row.
To find out what the limitations are for your platform, Google ...
6
The problem with your first example is the tri-link table. Is that going to require one of the foreign keys on either report or recommendations to always be NULL so that keywords link only one way or the other?
In the case of your second example, the joining from the base to the derived tables now may require use of the type selector or LEFT JOINs ...
Only top voted, non community-wiki answers of a minimum length are eligible
