Tag Info

Hot answers tagged

58

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 ...


36

just a couple of minutes ago I was checking this: A table that should exist in all projects with a database, seems simple enough to put in practice, check it out: It’s called schema_version (or migrations, or whatever suits you) and its purpose is to keep track of structural or data changes to the database. A possible structure (example in MySQL) is: ...


27

To lists all schemas, use the (ANSI) standard INFORMATION_SCHEMA select schema_name from information_schema.schemata More details in the manual: http://www.postgresql.org/docs/current/static/information-schema.html alternatively: select nspname from pg_catalog.pg_namespace; More details about pg_catalog in the manual: ...


20

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 ...


20

When using the psql command line, you may list all schema with command \dn.


15

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


14

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 ...


14

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 ...


14

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 ...


12

The whole point of NoSQL is that it doesn't use the same type of datastructures as an SQL database. It requires a whole different way of thinking and approach with your app, so there isn't something to handily copy the schema over because it is going to be unique to your solution.


12

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 ...


12

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 ...


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 ...


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, ...


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 ...


9

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, ...


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

In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account. See this post on Stack Overflow: difference between a User and a Schema in Oracle? for more details and extra links. You create users ...


9

As you've identified, storing the price on the order makes the technical implementation easier. There are a number of business reasons why this may be beneficial though. In addition to web transactions, many businesses support sales through other channels, e.g.: Over the phone Sales agents "on the road" At a physical location (e.g. shop, office) In ...


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

In PostgreSQL, one very good way to deal with EAV structures is the additional module hstore, available for version 8.4 or later. I quote the manual: This module implements the hstore data type for storing sets of key/value pairs within a single PostgreSQL value. This can be useful in various scenarios, such as rows with many attributes that are ...


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

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 ...


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

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, ...



Only top voted, non community-wiki answers of a minimum length are eligible