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

Your second option is more flexible, but I'm not sure why you're worried about "tons more tables". Usually this would be done with a single table: contact_types ------------- id (PK) name contact_details --------------- id contact_type_id (FK to contact_types.id) value organization_contacts --------------------- id (PK) contact_detail_id ...


6

Always start by identifying your natural keys, in this case key. If these are too complex (too many columns) or not stable enough (changes to often), consider adding a surrogate key (such as the ID column you mention). The criteria I use for keys are: - unique - stable - irreducible - complexity - familiarity In many situations there will be a conflict ...


5

Option 4: Class Table Inheritance This is a design technique used to implement a class/subclass situation. In this situation, attributes often apply only to one subclass, and not to all rows in the table. Visit the tag of the same name over in SO to see a bunch of relevant questions and answers. This has the advantage eliminating NULLS (mostly), ...


5

Date dimensions are pretty standard in a data warehouse, and are highly recommended by Kimball as most facts tie to a date. Typically, the key is an integer. It can be a meaningless surrogate key, or it can be a "smart" key where the integer is in the form yyyymmdd; e.g., the key for August 2, 2014 would be 20140802. Date dimensions provide a set of ...


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


4

I personally like the second option best. This is a fairly common way to solve this problem. Consider adding a "default_contact_type_id" column to your organization table for quicker lookups and/or reporting. Option 3: For the sake of completeness, you could add an XML column to the organization table calles simply "contact_information". An XML blob can ...


4

There is more than one school of thought concerning the naming of columns or fields. One school of thought says that the name should express the intent of the data being conveyed even if that name is separated from the context where it is found. Thus, a name like post_date or another name like comment_date tells the reader not only that this is a date, ...


4

Use liquibase. It supports diff, generating a db from scratch, patching a db, rolling back a db, and a bunch of other stuff. You used to have to write everything in XML with liquibase, but not anymore. You can write 99% of it in the SQL dialect of your choosing. Example: --liquibase formatted sql --changeset neil:1 create table contacts( contact_id ...


4

Execute this for all of the tables: alter schema2 transfer schema1.table1; To programmatically get the statements to execute: declare @SourceSchemaName sysname, @DestinationSchemaName sysname, @AlterStatements varchar(max); set @SourceSchemaName = 'NewSchema'; set @DestinationSchemaName = 'HumanResources'; set @AlterStatements = ''; select ...


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

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


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


3

The below sample structure illustrates how you can do the TasksTags table most efficiently. The Tasks table enforces unique task names. The Tags table enforces unique tag names. The TasksTags table joins these together allowing any combination of Tasks and Tags. USE tempdb; CREATE TABLE dbo.Tasks ( TaskID INT NOT NULL CONSTRAINT PK_Tasks PRIMARY KEY ...


3

Assuming destination schema is empty: DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N' ALTER SCHEMA Schema2 TRANSFER Schema1.' + QUOTENAME(t.name) + ';' FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERE s.name = N'Schema1'; PRINT @sql; -- EXEC sp_executesql @sql; If the destination schema is not empty, ...


3

Site1 and site2 can both be FKs, linked to the ID_site PK. This works on any rdbms that i'm aware of. Example Query: SELECT CT.site1, CT.site2, S1.site_description, S2.site_description FROM circuit_table AS CT INNER JOIN site_table as S1 ON CT.site1 = S1.ID_site INNER JOIN site_table as S2 ON CT.site2 = S2.ID_site


3

My first IT job was in this area - basically I was involved in a "fiddle" on the part of my employer to land a big contract which involved convincing the client that we had a functioning and "intelligent" order picking system. It involved alcholic beverages and there are many complex rules about these (tax reasons). Anyway, I just wish that Open Source had ...


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

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

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


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

Good start so far. Here are some notes: a movie can be released on different dates in different cities please add your primary keys, so we can see them are images for movies? people? why no order column? movies schedules are more complex than you've got so far. You will want to calculate show times based on some recurrence rules (see stackoverflow for ...


2

All current RDBMS' tables can have CONSTRAINTS on columns. These constraints are checked every time data is inserted into the table. It can also check data against other tables. We know that each Plate Type has certain number of Rows and Columns. We can enumerate all Rows and Columns for each Plate Type. So, when data is inserted, the DB can check if a ...


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

Even though 10,000 rows are not a lot, I would go for a slightly different solution that will perform a lot better. Keep you master table the same: id, name. Create a new alias/search table: itemid, alias Create an index on the alias and a foreign key constraint on the itemid. Do not have a delimited list of aliases, rather have a row for each ...


2

A very basic answer would be to let the developer know what exactly they're working with without needing to assign aliases in the event more than one table is being used. For example, if you're joining posts on comments (I'm not familiar with WordPress but just as an example), and the posts table as well as comments table have the columns title, author, ...


2

Recommendations are as follows: For access control use the groups instead of individual users. Create a group called public to which all users belong therefore any graphs assigned to public will be visible. Look to Role Based Access Control (RBAC) for more on this Each graph can be accessed by one or more groups, which allows you to have graphs that are ...



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