Tag Info

Hot answers tagged

9

As you've identified, storing the price on the order makes the technical implementation easier. There are a number of business reasons why this may be beneficial though. In addition to web transactions, many businesses support sales through other channels, e.g.: Over the phone Sales agents "on the road" At a physical location (e.g. shop, office) In ...


7

The basic concept is actually quite simple: you generate a script from sys.objects and sys.schemas that builds ALTER SCHEMA TRANSFER statements. So for example, you have three objects in the dbo schema, and you want to move all of them to the blat schema: Table: dbo.foo Table: dbo.bar View: dbo.vFooBar The following code: DECLARE @sql NVARCHAR(MAX) = ...


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


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


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


4

Do I understand correctly: you scripted tables only, created the database manually and then ran the script to create the tables? You have to either script the whole database and create the new database using that script (change the DB name if you'll execute it on the same instance) or at least include schemas in the list of database objects to script ...


4

You don't need to drop the dependent views, but you do need to alter them to (temporarily) not be schemabinding. If any of them are indexed, this means you will need to re-create the indexes. SQL Server doesn't have some weighting system for schemabinding: you can't change the object. Period.


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

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>


3

Your design looks a bit like the "supertype/subtype" pattern. Search for that and for "table inheritance". It needs quite a lot of work to be able to enforce integrity constraints though. You are missing a generic Fruit table (that's the "supertype") and a FruitType table to store the alllowed fruit types: FruitType fruit_type PK Fruit fruit_type ...


3

Aha! Thanks to @ypercube's comment about "inheritance", I managed to make a logic/context leap and found some concrete examples that give the name "Polymorphic Associations" to this kind of schema design. http://www.slideshare.net/billkarwin/practical-object-oriented-models-in-sql (slide #24) Under what conditions are polymorphic associations used? What ...


3

Why are you using ##global temp tables instead of #local? It is possible that when you are changing the procedure there is already a global temp table in existence with the same name - they're global so it could have been created by anyone. Use a local temp table and that problem will go away. Also, please don't use the sp_ prefix. Stored procedures ...


3

how to create "empty" or "blank" databases I would use pg_dump with the --schema-only option to create the dump for an empty database: pg_dump mydb -s > /mypath/myfile.sql Using Linux, you could also pipe the output to psql and create a new, empty database in the same (or any other) database cluster right away. Example for the same cluster: ...


3

SERIAL is an alias datatype for an INTEGER type column with a defult coming from a sequence. If you defined your table as having a SERIAL type column, then you need to get the actual name of the sequence from the table. Have a look at the output of \d your_table and retry your GRANT command with the proper sequence name. If you did name your sequence ...


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

Connect to the psql command --> psql --u {userName} {DBName} then you can type the below command to check how many schemas are present in the DB DBName=# \dn Else you can check the syntax by the below steps easily- After connecting the the DB, press DBName=# help You will get the below options- You are using psql, the command-line interface to ...


3

I did a presentation about the various solutions to support custom fields: Extensible Data Modeling with MySQL. The most straightforward option for greatest compatibility with SQL, including constraints and data types, is just to add columns as needed. But I would caution to not let users do this, instead let them submit a request and the DBA can perform ...


3

You can use Vertabelo. Vertabelo is an online database designer working under Chrome developed by company I work for. Vertabelo focuses on online collaboration in the visual database schema creation area. The most important feature of this tool is that it allow to share database models across the team and collaborate on them via web browser. It also allow ...


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

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


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


2

While demanding the destruction of foreign key constraints without detailed reasoning is foolish, it makes sense to keep the outside references under control. What if the schemas you are referencing are named differently on your new server? In Oracle you resolve this problem by creating SYNONYMS for objects that are outside of the current schema.


2

It may end up costing more in storage, but I prefer to house all relevant details of the sale with the transaction itself, so that if for whatever reason our audit trail gets broken, or an administrator overrides the safeties in place, the details of the sale like: currency used, unit-price, qty, taxes applied and what value they came to, etc. are all ...


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

The basic concept is to use GRANT/DENY Schema Permissions. You can efficiently manage permissions by creating a role and then adding members to it. Below is an example that will explain you in detail use master go --Create Logins CREATE LOGIN UserA WITH Password='UserA123'; go CREATE LOGIN UserB WITH Password='UserB123'; use AdventureWorks2008R2 go ...



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