Tag Info

Hot answers tagged

8

EAV is not evil; like any other tool, it can be implemented poorly and abused. You can find articles trash talking cursors, dynamic SQL, triggers, even SQL Server itself. That doesn't make them bad things. EAV can be an appropriate solution. Whether it's the right answer in your specific case is probably more opinion-based than anything; I'm answering more ...


6

Sorry to resurrect an old question Recently I have been using the 0xDBE Database management tool by JetBrains (Currently in Early Access Program). It supports multiple database engines, in the excellent Jetbrains IDE, and a key feature I have found useful is the ability to diff 2 tables (DEV and PROD). Below is a screenshot of the diff in action (in ...


6

TL;DR The query Visual Studio is executing is being blocked and the query times out. Turns out I've managed to work this out for our situation: timing out due to blocking! I ran a profiler trace with a filter on application name of Microsoft SQL Server Data Tools, Schema Compare to capture the Visual Studio schema compare events. It would reach a ...


5

SELECT set_config('search_path', 'fred,'||current_setting('search_path'), false); The false says it's not a transaction-LOCAL setting. For the bonus question, you can store the value in a custom setting: SELECT set_config('tmp.search_path', current_setting('search_path'), false); From version 9.2 on, you don't even have to define this setting in ...


5

It could be as simple as: GRANT SELECT ON SCHEMA::[sitename] TO [user]; If there are procedures and functions you will also need: GRANT EXECUTE ON SCHEMA::[sitename] TO [user]; The full list of permissions applicable at the schema level is listed in the documentation: GRANT Schema Permissions (Transact-SQL) You can also set the default schema for ...


5

The documentation on psql explains: Whenever the pattern parameter is omitted completely, the \d commands display all objects that are visible in the current schema search path — this is equivalent to using * as the pattern. (An object is said to be visible if its containing schema is in the search path and no object of the same kind and name ...


5

You are on the right track here. Somethings to consider: Users Likely a User can have multiple resumes. Job candidates often tailor a resume to a job opening and want to keep all of those. This means you will need a User table. Having a user table also gives you a place to store attributes of the user such as their name. Sections A resume has lots ...


4

It looks like you'll need to change the user's default database, as it appears this is currently set to DB1, which no longer exists. exec sp_defaultdb @loginame='user1', @defdb='DB2' OR ALTER LOGIN <login_name> WITH DEFAULT_DATABASE = <default_database>


4

TWO TABLES IN THE CURRENT DATABASE If you want to know if two tables are different, run this SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM ( SELECT column_name,ordinal_position, data_type,column_type,COUNT(1) rowcount FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name IN ...


4

Your comment about intensive dropping and creating and the notice you received regarding increasing max_locks_per_transaction hint that you're dropping and creating many objects in the same transaction. Each of those results in a lock, which each requires a small amount of shared memory. Because of this, max_locks_per_transaction limits the number of locks ...


4

Yes you can. Per documentation: Note that the user performing the insert, update or delete on the view must have the corresponding insert, update or delete privilege on the view. In addition the view's owner must have the relevant privileges on the underlying base relations, but the user performing the update does not need any permissions on the ...


4

What you are describing is the EAV (Entity Attribute Value) model which most database professionals would run a mile from. It's also sarcastically called OTLT (One True Lookup Table) and is a classic novice mistake. Your hunch is correct! Here (and here) is the opinion of Joe Celko (a veteran SQL programmer who is/was a member of the SQL standards ...


4

So what I am looking for is a way to store a large number of activities that have almost no fields in common in a way that makes reporting easier. Not enough rep to comment first, so here we go! If the primary purpose is reporting and you have a DW (even if it isn't star schema) I'd recommend attempting to get this into a star schema. The benefits ...


4

It seems that you are involved in a scenario that requires the creation of an auditable database. You may find related information by searching for the terms temporal databases and database history tables. A database is built to store information that is relevant to its users, and this information, of course, can change as time passes. In this way, the ...


3

The answer is a little bit like the old joke: "Doctor, doctor! It hurts when I do this." If you want a foreign key to an entity then that entity needs to have a unique key defined to be referenced. The fact that your Expectations table has a unique candidate key in Expectations.ExpectationId should not prevent you from defining another candidate key that ...


3

They are the same thing in a default installation. pg_catalog.text is the schema-qualified version of the type name text. The search_path always contains pg_catalog automatically. Per documentation: In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables and all the built-in data ...


3

If I understand what you are asking for: A given person can have multiple roles in a team but can only be on one team I would do it like this: By putting the TeamId in the People table you enforce the fact that a Person can only be on one team. You then have a many:many relationship using a cross join table between People and Roles. This allows a person ...


3

I have an EAV for tracking server / database configuration. It's great for getting data in. We can throw any data at it and the loader ensures the "E" and the "A" reflect the data given. Once we got over a few hundred million rows in the Values table, however, getting data out became increasingly problematic. (I think having 400-column PIVOT queries ...


3

There is a very good aggregation of resources on the following link http://www.databaseanswers.org/data_models


3

I think you are over complicating this. Grants and roles govern access by users not what table space and data files where the data is located in. Yes, backup, recover and export are much easier if schemas have their own table space but that doesn't seem to be your question. Separate your business domains by user/schema. Give each user/schema their own ...


3

This happens when not having USAGE privilege on any of the schemas of search_path. By default the pseudo-role public (all users) has this privilege on the publicschema, so this error happens only after revoking it explicitly with: revoke usage on schema public from public; This is required when it's not desirable that people peek into other people ...


3

If I were you I would make sure to separate all of the data. Is this going to be completely controlled in the database, or is there going to be an application? If it is an application I would have a users table and permissions tables for the people using the application. I would do a separate a customers table for your customers. I agree with the ...


3

If you are looking for significant performance improvements to dnoeth's answer, consider using a native C-function and creating the appropriate operator. Here is an example for int4 arrays. (A generic array variant and the corresponding SQL script). Datum _int_sequence_contained(PG_FUNCTION_ARGS) { return DirectFunctionCall2(_int_contains_sequence, ...


2

This can be done. The column default for your serial primary key is typically defined as: ALTER TABLE schema_a.tbl ALTER COLUMN tbl_id SET DEFAULT nextval('schema_a.tbl_tbl_id_seq'::regclass); Two options: 1. Move sequence (my preference) to the public schema - or any schema with sufficient privileges: GRANT USAGE ON SCHEMA public TO public; -- or: ...


2

Your options are: Use roles, and SET search_path on each role so it looks in the appropriate organization's schema. While in many ways the cleanest approach from a database perspective, this is a pain when giving orgs the right to manage their own users. Require users to specify their organisation at login time, probably with user@domain style logins, or ...


2

You can compare checksum of output of SHOW CREATE TABLE product_today # mysql -NBe "SHOW CREATE TABLE sakila.actor"| sed -r 's/AUTO_INCREMENT=[0-9]+/AUTO_INCREMENT=XXX/g' | md5sum # 1bc0d72b294d1a93ce01b9a2331111cc -


2

You cannot drop a database if someone is connected to it. You would need to force everyone off the database first (and to do that you need SYSCTRL authority for the instance, and I'm gonna guess DBADM at the database level if you don't have instance level authority). db2 connect to xydb --the next command forces off the users asynchronously db2 quiesce ...


2

This can be done with DDL alone but it's not at all trivial. The following will ensure that any test will either have 0 or 5 test cases: create table test ( test_id integer not null, constraint pk_test primary key (test_id) ) ; create table test_case ( test_id integer not null, case_no smallint not null, ...


2

What you are doing is impossible because the INFORMATION_SCHEMA database is a database made up of in-memory read-only temporary tables (See my post How is INFORMATION_SCHEMA implemented in MySQL?) You are going to have to drop the triggers and recreate them I wrote a post on that before (Oct 02, 2011 : Can mysqldump dump triggers and procedures?) Here is ...


2

The basic answer is: No. Do you have a record of the 18-20 digit value somewhere? In another location in your database, on a piece of paper on you desk, etc? If so then use the source information to correct your error. You have not indicated which database software you are using, which can make a difference for many issues.



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