Tag Info

Hot answers tagged

8

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

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


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

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

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

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

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

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

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

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

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

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

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


2

yes you can, just be sure that the transactions are defined by the uppper layer - the client. A transaction has to be Atomic, so has to succeed (and commit) or has to fail (and rollback).


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


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

I will add some practical points that I have seen. Products are transient. What they may signify today, may not be the same as what they used to signify an year back. The same sku code (and hence the product_id), might refer to different variant/kind of the product at different stages. Not everyone understands all the concerns at hand; hence a user may ...


2

A logical schema won't exist in your database. A logical schema is a design-centric database structure built to meet your business requirements. It is a model that exists on a white board or in a diagraming tool. It is like the architect's drawings of your database. A physical model is what is actually implemented in your DBMS. The two can be ...


2

If you just need results from multiple schemas, you can re-use the same query string and set the search_path in between: SET search_path = u111, public; SELECT * FROM foo; SET search_path = u222, public; SELECT * FROM foo; ... If you need to combine results from multiple schemas (probably your use-case), you can either build the statement in your client ...


2

The schema creation has to be the only statement within it's own batch, with no statements preceeding or following it. As the IF statement is considered to be in the same batch, the statement is syntactically incorrect. You have to wrap the schema creation within an EXEC, so it is considered to be in a separate batch. Therefore the correct syntax for ...



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