Hot answers tagged

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


8

A functional dependency is exactly what the term implies - the output of the function is always determined by the input. If for example we have a function f(), and provide variable x, and we always receive output y, then y is functionally dependent on x. You can think of this like a simple graphing function 2x + 1 = y Plugging some sample values into the ...


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


7

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


6

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


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


6

It seems that you are involved in a project that requires the creation of a temporal database. You may find related information by searching for the terms auditable databases and database history tables. I deem this extraordinary Stack Overflow answer as the top material with respect to these topics. In such post, @PerformanceDBA models an auditable ...


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


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


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

SSDT is comparable to Liquibase/Flyway as it does what they do but by taking a different approach. With SSDT you have the development environment so you get things like go to definition, find references and intelli-sense as well as the ability to compile a project into a dacpac and then deploy that dacpac to a database. The SSDT way (and redgate sql compare ...


4

No, no, no, you should definitely not do this. It is the responsibility of the object creator to grant the necessary privileges. (Although this is a common request, and it would be nice to finally have built-in support for this.) Since you are on 12c, forget SELECT, and grant READ. SELECT is not a read-only privilege. With SELECT granted, the grantee can ...


3

Without knowing exact and whole requirements we cannot design a complete data model. Let us assume the following business rules, it is based on your question. Each Customer can request for one or more Room. Each Room can be requested by one Customer Each Admin can assign one or many Room. Each Room must be assigned by one Admin. Each Room must be belongs ...


3

Strictly speaking, _id fields are as unique as you make them. As you have discovered, the default values for _id are ObjectIDs but you can populate the field with any data you wish. Therefore, for example, you could use a UUID as I have done in this sharding example and then the chances of a collision across collections would essentially be whatever the ...


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

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


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

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


3

Which of the two options is the best? The relational design is clearly superior to the hierarchical design used in the NoSQL example. A database schema designed using relational principles does not favor one access path over another. Each table represents a real world entity type and through the use of relational algebra queries of arbitrary ...


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


2

I have done one structure that may help you. room. It's the main room configuration where you can add any value that refer to the rooms. room_status. It's the room status where you could know which is the room actual status. transaction. Its your main table, here you're going to storage all the activities of the rooms. user. Here you can delimit which ...


2

I will use pseudo code to illustrate; you can adapt to your DBMS's specific syntax. Join availability to transaction on room.id and also on the overlapping intervals i.e. Availability.from <= TimePartOf(transaction.checkout) And availability.til >= TimePartOf(transaction.checkin) If, for example, a room had different rates in the morning and ...


2

I would handle this scenario by means of a (super)type-subtype relationship. You may find this answer valuable as an introductory reference. Approach for this particular scenario If I understand your specifications properly, the following assertions are relevant in your business context: A Party[1] is either one Group or one User. A User (playing the ...


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

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

Off guess you are missing the connect permission. I'd always thought that was created with the user but there may be conditions where it isn't added. Try running USE [MyDatabase]; GO GRANT CONNECT TO [MyDomain\MyAccount]; You could also try changing the default schema of [MyDomain\MyAccount] ALTER USER [MyDomain\MyAccount] WITH DEFAULT_SCHEMA = ...


2

Let's suppose the institution does send me a listing of all the tables and their columns. Even though I don't have the actual data, I can now attempt social engineering attacks to convince insiders into giving me whatever data I want. The request would sound much more legitimate, because I could ask for very specific pieces of information using the actual ...


2

Actually, you can do this with a single collection (news items) and a simple query. No need for references (continent names aren't going to change any time soon) or strange data models here: { _id: new ObjectId(), continent: "Asia" title: "whatever", date: yesterday }, { _id: new ObjectId(), continent: "Africa", title: "other title", date: ...


2

Owned by more than one user: no Accessible by more than one user: yes To give a user (or role) access to the tables in a schema you can use something like this: grant all on schema foobar to role_name; grant all on all tables in schema foobar to role_name; grant all on all sequences in schema foobar to role_name; grant all on all functions in schema foobar ...


2

If you're running Enterprise edition, you can add and remove indices, including clustered indices, with ONLINE. Since your existing PK isn't being used, you can start by dropping it. Then create your new clustered index, and finally drop your old non-clustered index which is now obsolete. ALTER TABLE MyTable DROP CONSTRAINT PK_MyTbl_SrgKey WITH ...



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