Tag Info

New answers tagged

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.


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


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


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


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


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


0

schemas is used for organizational structure and namespacing. By namespacing I mean that you can have similarly named table in few places. And another feature of using schema is additional access control for that schema. You will have joins depending on your data model. You are using schemas implicitly already. There is a "search path" defined in PostgreSQL ...


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


0

As the maximum number of characters in a single symbol is 48 and the Technical Spec for aggregators (http://www.gs1.org/sites/default/files/docs/gs1-source/TSD_v1_1_Technical_Implementation_Guide_for_Aggregators.pdf) indicates the file can come in as UTF-8. UTF-16, or UTF-32, the safest bet would be nvarchar(48)


0

If you are ready to first create a temporary database from the old dump you could use a different tool, which can create dumps that are nice to compare: It is called pydumpy https://code.google.com/p/pydumpy/ written by https://github.com/z1lv1n4s (github mirror: https://github.com/miebach/pydumpy)


3

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


0

I see it as 2 steps: Build tables with just the latest signal (or noise) for each device JOIN or UNION the two tables. Step 1 is a variant of groupwise max: SELECT device_id, stats_time, status, noise -- The desired columns FROM ( SELECT @prev := '' ) init JOIN ( SELECT device_id != @prev AS first, -- `device_id` is the ...



Top 50 recent answers are included