Hot answers tagged

74

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


65

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


64

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


42

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


26

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


22

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


19

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


18

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


16

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


16

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


14

impdp will create the user if it's not present yet, so you don't have to worry about it unless that's not what you want. Do not run impdb or expdp as sysdba, only do that if Oracle support requests it in specific circumstances. Use an ordinary user for that - one that has been granted the dba role for instance. (There are the [IMPORT|EXPORT]_FULL_DATABASE ...


13

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


13

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


13

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 in the same database. See One ...


13

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


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

If you want users to select from the view, why are you granting to the table? By "revoke" do you mean explicitly revoke/deny? Deny will override grant so there's your problem... you should be able to accomplish this by adding grant to the view and not doing anything either way on the tables. Here's a quick example where SELECT has not been explicitly ...


11

If you create a database without specifying a file size, the size is mapped from model. You can see what this is by running: EXEC model.sys.sp_helpfile; -- on my system, size of primary data file -> 3264 KB Now, if I create a database just using CREATE DATABASE, I get an exact replica of those files. CREATE DATABASE [floob1]; GO EXEC ...


11

Connect to the psql command --> psql --u {userName} {DBName} then you can type the below command to check how many schemas are present in the DB DBName=# \dn Else you can check the syntax by the below steps easily- After connecting the the DB, press DBName=# help You will get the below options: You are using psql, the command-line interface to ...


11

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


10

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


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

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

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


9

This is more accurate answer: select tc.table_schema, tc.table_name, kc.column_name from information_schema.table_constraints tc, information_schema.key_column_usage kc where tc.constraint_type = 'PRIMARY KEY' and kc.table_name = tc.table_name and kc.table_schema = tc.table_schema and kc.constraint_name = tc.constraint_name ...


9

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


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

If you have a database that is using the EAV structure, it is possible to query the data a variety of ways. @Simon's answer already shows how to perform a query using multiple joins. Sample Data Used: CREATE TABLE yourtable ([ID] int, [Metric] varchar(6), [Value] int); INSERT INTO yourtable ([ID], [Metric], [Value]) VALUES (1, 'Ht_cm', 190), (1, ...



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