Tag Info

Hot answers tagged

5

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


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


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


2

This should give you a start. It assumes that you've already cloned all the relevant objects and that all of your permissions are at the object or schema level (if you have column-level permissions, the script gets a little more complex, but not too bad). DECLARE @oldschema SYSNAME = N'schema1', @newschema SYSNAME = N'schema1_dev'; DECLARE @sql ...


2

You can do it with Native dynamic SQL ( http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS011 ) using the EXECUTE IMMEDIATE statement. Here's an example: DECLARE v_sql varchar2(128); v_username varchar(30); cursor c1 is select username from dba_users where username like ('PATT%'); BEGIN for REC in c1 loop v_sql := ...


2

Rearranging / rephrasing your relationships a bit: 1 instructor has 0, 1 or n meeting_points. 1 meeting_point is linked to 1 instructor. This means there is a simple 1:n (1::0-n) relationship between them. The foreign key should be in meeting_points, referencing instructors. 1 booking has 1 meeting_point. 1 meeting_point is linked to 0 or 1 booking. This ...


1

This schema would make sense to me: tblCompany ( Company_ID PK Company_Name ) tblProduct ( Prod_No PK Company_ID FK_tblCompany_CompanyID Prod_Name Prod_Mark_No Prod_Catalogue Prod_Type Prod_Expiry_Date Prod_Verify_Date Prod_Comments ) You could have separate tables for product type and product catalogue to get to 3NF but that's a ...


1

An owner of a database object has full rights on it automatically and unrevokably. But of course you can give an user all rights to a schema without making him the owner.


1

From the rule 3 you remove 0 meeting point in rule 1 And you don't need any pivot table for that. This is your sample database for PostgreSQL: CREATE TABLE instructor ( id SERIAL PRIMARY KEY ); CREATE TABLE booking ( id SERIAL PRIMARY KEY ); CREATE TABLE meeting_point ( id SERIAL PRIMARY KEY, booking_id integer NOT NULL, meeting_id ...


1

This is a first pass based on MySQL (don't have a running PostgreSQL system - use SERIAL instead of AUTO_INCREMENT for example). Add UNIQUE INDEXes where necessary. mysql> show create table instructor\G *************************** 1. row *************************** Table: instructor CREATE TABLE `instructor` ( `id` int(11) NOT NULL ...



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