InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 17:00:14 GMT20http://www.sqlservercentral.com/Forums/Topic1446557-373-1.aspxHi all,Here is a relatively complex design that I would like to have vetted by experts! :)Say that you need to track PhoneNumbers for different objects, such as Clients, Staff, Managers, etc. Each object could have many phone numbers associated with them.The basic approach to many to many would be to create join tables between objects to maintain referrential integrity (such as ClientsPhoneNumbers, StaffPhoneNumbers, etc). We have about 10 different object types for which to track phone numbers, and then the same for addresses, etc. Pretty soon we could have 20+ join tables.The other approach is to track parent object references on the phone, and differentiate the parents by parent type (i.e. each phone number would track the parent's ID and its type such as Client, Manager, etc.). The problem with this approach is that it does not maintain referrential integrity and you run the risk of having orphaned records in the database.What are other approaches to this problem?Thanks everyone!:)Thu, 25 Apr 2013 09:04:42 GMTGanga-236835http://www.sqlservercentral.com/Forums/Topic1449027-373-1.aspxHi everyoneWe are using Sql Server 2008 and we have a log table which has one clustered index(on creationdate, that can be duplicate) and two non clustered index.This table is being used for logging and being inserted and updated frequently.Is it neccessary to create a primary key(new identity column) on this table?and if we do not create any,will that cause any problem?ThanksThu, 02 May 2013 19:41:13 GMTAspet Golestanian Namagerdihttp://www.sqlservercentral.com/Forums/Topic1447287-373-1.aspxI was just wondering if anyone out there uses SQL Server schemas for logical organization, instead of security purposes. I'm self-trained and have yet to work in the field due to a few health issues (I used my time out of the workforce to train myself in SQL Server) and am just wondering what I'll encounter in a professional environment. Since I come from a VB background I think like a programmer, and have found it useful to reuse certain schemas in various test databases just to keep the logical functional of tables separate. You can tell the function from some of the names: Import, Testing, CodeGeneration, ConsistencyLogical, ConsistencyPhysical, Testing, Traces, Backup, Documentation, Performance etc. On a professional production server I'm sure some of these functions would be off in other databases, but some of them, like the Legacy schema for dead code or Documentation might remain in the original databases. Could I still use schemas for purposes like this, or are they strictly for security? And if this is a permissible, wouldn't it be a helpful feature to have hierarchical relationships between schemas? I find myself wishing I could create parent-child relationships between them quite often.-- SteveEdit: "Schema" in the title should be plural obviously, LOL - My keyboard can't keep up with my fingers ;):-)Sat, 27 Apr 2013 12:32:52 GMTSQLServerStevehttp://www.sqlservercentral.com/Forums/Topic1445807-373-1.aspxHi Folks,I would like to discuss the following design issue:I'm working in a Project in which several systems are involved, like SAS Marketing Automation and Microsoft Dynamics 2011. There is a database to hold the marketing campaigns information, other to stores the CRM data and a Data Hub to store and exchange general data across the systems.At the beginning I created a database (The Data Hub) to store the data coming from an external provider. The other systems have their own databases and also read data stored in the Data Hub. Then I created new schemas in the Data Hub to stored relevant data from the other systems, like marketing lists, campaigns and so own.Now I have to load data into the CRM, so I created a separated Staging DB to prepare the data before load them in the CRM database.My new requirements are twofold:- Store changes made in the CRM by the operators back in the Data Hub.- Send the current data to the external provider.My first try was to build new schemas to manage the permissions and so on, but I don´t know exactly which strategy should I follow. Why is your opinion about this issue?I know I should consider another factors like maintenance, backups, security, server size, etc.Any comment would be appreciated.Wed, 24 Apr 2013 02:49:45 GMTPaul Hernándezhttp://www.sqlservercentral.com/Forums/Topic1446909-373-1.aspxHi AllI'm not really sure this is the right sub forum but since the question is sort of design related it seemed my best shot. Come to think of it, SQLServerCentral might be the wrong forum entirely but I reckon there's probably more than enough cross-over knowledge floating around here to make you guys a useful source of ideas.I've always been intrigued by the idea of the attribute value pairs pattern and the flexibility it would offer in making a highly configurable product. I'm well aware of the implementation problems that come with trying to implement it in a relational database (so no need to hit me with a bunch of responses as to why it's a bad idea) but have read that NoSQL platforms are far better suited to it. Basically, I think I'd like to try it out and see how I get on but I have no experience whatsoever of NoSQL platforms. Has anyone here ever tried implementing AVP using a NoSQL DB and how was the experience? What were the wins? What were the pifalls?Can anyone recommend a decent platform to pick? I'm primarily a .Net developer so am steering towards MongoDB at the moment, particularly as it's free and open source. (This is for "home"projects so I really can't afford to be paying enterprise level fees). I've asked on developer forums and MongoDB seems to be the general consensus but it's always worth hearing more voices.Any other warning/tales of woe/pre-learned lessons that anyone can provide?Fri, 26 Apr 2013 05:29:55 GMTFunkyDexterhttp://www.sqlservercentral.com/Forums/Topic1441129-373-1.aspxI've done a fair bit of Googling and asking dba friends, but I wanted to see what some of you guys thought about this. I have a database of films. The issue I want to address is one of managing the people associated with each film. However, I want to be able to have multiple alternate names (AKAs) for each person, and to keep track of which of those names were used for each film. I think IMDB does this, so you've probably seen it before. I also want to be able to store information about each person, such as birth date, country of origin, biography, etc. So, as so often seems to be the case, I quickly find myself bumping into the issue of how to store hierarchical data in SQL Server. I can't seem to come up with a solution that I feel good about. I'd be happy to share my 2-3 working ideas if you want. But you can probably guess what they are, more or less. I'm kind of just wondering what you guys would do design-wise in this situation since you always seem to surprise me. Assuming I have at least a FILMS table, a PEOPLE table, a ROLES table, and a FILMS_PEOPLE_ROLES (many to many) table, what would you do to handle the AKAs? Store them as records in the PEOPLE table? In their own AKAs table? Concern #1: If I store the AKAs in a separate table, as "children" with each record referencing its "parent" PEOPLE record, what's to stop someone entering the "parent" name as an AKA for itself? Conversely, what's to stop someone entering a new PEOPLE record with a name that already exists in the AKAs table? Perhaps the solution is a trigger to automatically add a new duplicate AKA record whenever a new PEOPLE record is created? Concern #2: If I store the AKAs in the PEOPLE table, there will be many columns that are unused/irrelevant to those records (such as birth date, bio, etc.). Anyway, just curious what your thoughts are. Maybe you have some great ideas that I haven't even considered yet. :)Thu, 11 Apr 2013 01:17:13 GMTautoexcrementhttp://www.sqlservercentral.com/Forums/Topic1259607-373-1.aspxHave you experienced the need for analyzing data temporally in your business? Do your existing information management systems allow you to view historical data? Typically, when you update or delete information in any application the previous state of that data is lost forevever. Have you ever wanted to be able to see what your information "used to be" as opposed to what it is now? In addition, have you ever wanted to know the state of your data in the past "as I knew it to be then" in comparison to "as I currently know it to have been then"?What is your experience in solving these problems?Wed, 29 Feb 2012 08:30:41 GMTJQAllenhttp://www.sqlservercentral.com/Forums/Topic1432699-373-1.aspxHi AllI will apologise this will be a little vague but is all the info I can give at the moment.I have a few ideas however I'm not going to present those here as I want fresh ideas..Consider this:Two tables across two sites and serversThey are a duplicate of one-another in structureThere is an ID in each table (auto inc)Each table HAS to have data from it inserted in the other duplicate table WITHIN SECONDS ideallyIf one server goes down we need to be able to switch over INSTANTLY between one server to another to hit this table with updates / inserts / selects AND containingthe data from the other table up-to the point of failure.. If the above happens then ALL data not included in one table while it was down HAS to be inserted into the other table once it is back onlineReport off these tables (be it a separate DW or server sky is the limit here as long as the latency is minimum (seconds) ) 24/7Require a method of distinguishing where the data has come from table A or BThere are many columns in these tables all VARCHAR(whatever) and DATETIME Need to be able to allow one client to hit a primary table for INSERTS / UPDATES and SELECTS (be it A or B as long as data is replicated to the other table) and be able to switch these AT ANY TIME Need a PK in these tables that is uniform if possible i.e table A has PK ID of 1 and 2 and 5 then table B will not have either and start at 3 - 4 and restart at 6As I said vague yes but at the moment it is the best I can do..The end game a 24/7 solution for inserts ect plus reporting and each table requires to hold data from the other 24/7Ideas anyone!!!!CheersAndyTue, 19 Mar 2013 08:50:11 GMTAndy Hyslophttp://www.sqlservercentral.com/Forums/Topic1406667-373-1.aspxHi all,I've been brought on board to help a company start to build their first business intelligence setup. I'm a developer by trade, and have good knowledge of SQL and application design using relative tables but this is my first step into designing a data warehouse.I have two questions initially from the customer, and if I can do a good job of providing a report setup for this we'll expand from there. The questions I'm tasked to answer are:How many support tickets are being opened each day?How many support tickets have been closed each day?How many does each employee have?How many does each group of employees have?I've designed my first start schema to handle the answering of these questions which is as follows.[img]http://alanhollis.com/wp-content/uploads/2013/01/datawarehouse-2.png[/img]I would love any feedback/critique of this design.Thanks Alanp.s As a side note I'm planning on using SISS to perform the ELT tasks rather than programming my own software to do the job? Does this seem like a sensible opinion? For the first part of the data entry I'll be pulling information out of Dynamics CRM and Request Tracker.Mon, 14 Jan 2013 05:17:25 GMTalan.hollis 1097http://www.sqlservercentral.com/Forums/Topic1426267-373-1.aspxHello, I asked a question at dba.stackexchange.com [url=http://dba.stackexchange.com/questions/35855/database-describing-databases-and-references-to-tables]here[/url], that's not getting a lot of interest. Now that it has fallen out of the first page I fear there's no hope of getting an answer, if there's someone that can help here I would appreciate a lot.I don't know how these things work, if you think it's better to answer on the other site do so, if you want to answer here you're welcome.If you don't understand something please tell me.P.S. I asked it there because there seemed to be more activity and I needed an answer quickly, I hope no one feel offended :-)Mon, 04 Mar 2013 08:15:07 GMTgbfvhttp://www.sqlservercentral.com/Forums/Topic1426230-373-1.aspxHi,I am creating a new Database. In this i will have a column for to store current Date.I can specify it as a default Constraint with GetDate() or ask the developers to insert it themselves.My doubt is which will give the best performance since the database will hold millions of records.Thanks in advance,KarthickMon, 04 Mar 2013 07:14:26 GMTard5karthickhttp://www.sqlservercentral.com/Forums/Topic1412187-373-1.aspxi have enclosed here sheet which contains column's list . can anybody help how can i seggregate them into different tables ?Sun, 27 Jan 2013 23:13:10 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1393191-373-1.aspxHello.I've started using a SEQUENCE in a table instead of an identity.I seem to be experiencing problems of the sequence getting reset to a lower value periodically. Inserting will work on the table, producing the next bigint in the sequence as the primary key, for days and then all of the sudden duplicate primary key errors show up. When I check, the last primary key value in the table is higher than the current value of the sequence.For example: right now I have primary key values 6000 through 7032 contiguously in the table, all of which were generated with the sequence. Suddenly I'm getting duplicate primary key errors. A quick check of the sequence shows it's at 7007, but the last inserted row has a primary key of 7032! I'm populating this table in one place (in the application layer), leaving the primary key null, which allows the default constraint to get the next sequence.When the problem shows up, I've reset the sequence to the higher number in the past and all is well for many days, then the problem occurs again.The definition for the sequence is:CREATE SEQUENCE [dbo].[IntegrationQueueSEQ] AS [bigint] START WITH 1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 9223372036854775807 CYCLE CACHE 50 The default constraint for the primary key on the table is defined as:ALTER TABLE [dbo].[IntegrationQueue] ADD CONSTRAINT [DF_IntegrationQueue_IntegrationQueueID] DEFAULT (NEXT VALUE FOR [dbo].[IntegrationQueueSEQ]) FOR [IntegrationQueueID]Does anyone have any ideas how this could be happening or experienced it themselves??I'm very excited about using sequences moving forward but this is making me nervous about their reliability.Thanks!Wed, 05 Dec 2012 13:34:23 GMTdave-Lhttp://www.sqlservercentral.com/Forums/Topic1413440-373-1.aspxcan anybody help me with steps to consider for "designing the database for an re-engineer project" ?or any link or article reference ? Tue, 29 Jan 2013 22:14:03 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1417280-373-1.aspxHi, Can someone tell me if can I activate Service Broker on a database and doesn't use this feature for some ([i]or long[/i]) time?Can I get bad performance on my database?I mean, I'm learning about SB ([i]Service Broker[/i]) and I want to implement, but I can´t for now, because I don't know how to do it. I want to leave database ready for use this feature when I understand how to implement properly.I ask this, because some created databases show an error msg when I try to activate SB after structure is created, but if I create again the databases and activate SB before to create all structure ([i]tables and relationships[/i]) I dont have problems to activate.I will try to implement this feature for take an history of movements in my database and audit.Thanks in advance for help :)Thu, 07 Feb 2013 11:59:23 GMTCarl0s_http://www.sqlservercentral.com/Forums/Topic1414261-373-1.aspxI'm trying to understand the nuances in tables and indexes beyond what Management Studio does by default.My question is what are the advantages of using a primary key constraint as opposed to just having a unique index on a key (that is a primary key)? I've done searching online and read some parts of books. Everyone seems to agree they should be used, but there is no clear reasoning of what advantage the Primary Key constraint provides over a simple Unique constraint.Example:[code="sql"]create table dbo.Car ( CarID int not null identity(1,1), VIN varchar(25) not null [b]primary key nonclustered[/b]);gocreate unique clustered index CX_Car_CarID on dbo.Car(CarID)with (fillfactor=90);go[/code]What are the implications of not declaring VIN as a "Primary key", but just a unique key, such as:[code="sql"]create table dbo.Car ( CarID int not null identity(1,1), VIN varchar(25) not null [b]unique[/b]);gocreate unique clustered index CX_Car_CarID on dbo.Car(CarID)with (fillfactor=90);go[/code]One reason I'm wondering is that I would like to include some columns in my primary key, but this isn't allowed if the index is for an actually declared "Primary Key". It seems pointless to build a "Primary Key" index and then another, on the same key, with included columns, unless the declaration of "Primary Key" actually provides some performance advantages.Incidentally, I'm wondering about this for Foreign Keys as well. Beyond enforcing referential integrity and documentation, are there any advantages to actually declaring these constraints in the schema? It would seem that the enforcement of referential integrity would actually be a bit of a performance hit, no? Any thoughts?Thanks!Thu, 31 Jan 2013 11:06:29 GMTdave-Lhttp://www.sqlservercentral.com/Forums/Topic1412512-373-1.aspxI was thinking of using a table with one row to keep configuration data and information about the server the database is hosted on.Is there any reason not to do it this way? Is there a better way to do it?It seems a major advantage to using a table and not (for example) items in the .config file is that stored procedures and queries could include information from the configuration directly without it having to be passed as parameters.Here is my initial table def:CREATE TABLE [dbo].[DataBaseInstanceInformation]( [DbiId] [int] NOT NULL, [DbiDeptId] [int] NULL, [DbiIsRemoteDatabase] [tinyint] NULL, [DbiServerName] [varchar](255) NULL, [DbiComments] [varchar](max) NULL)Mon, 28 Jan 2013 09:42:08 GMTtimwellhttp://www.sqlservercentral.com/Forums/Topic1411775-373-1.aspxWe have a reporting system that I present multiple databases to (i.e. for multiple clients). Our UAT & DEV source systems don't provide enough business-like data for me to provide real-life situations for testing. Resource constraints also mean I'm not going to get situations keyed in. This has resulted in users developing & testing their reports on the production reporting system.So I'm looking to present a copy of the production database (or even the production database itself) to our development servers, but in doing so I need to satisfy the compliance requirement that sensitive data is not available. Preferably I would like something that is self-maintaining i.e. that is self-aware of the user, or the environment it is on and either makes the data available or doesn't.So what approach would you take? Would it be a script to mask the data post-restore? Or a table that is security-controlled with an overlayed view / proc that either masks the sensitive data on execute / query or decrypts the encrypted data on execute / query? Or would you use a 3rd party tool to generate duff data? Some other route?My issue with something like Data Generator (I have the SQL Toolbelt) is that it is restricted, it's either a whole table or nothing which isn't what I want since that will result in inconsistent data. Additionally, it isn't set up to be all that automated - you need to build a project then run it from the command line, it would be amazing to be able to do something along the lines of "data generator" [table] [column] [data type]Thanks for looking ;-)Fri, 25 Jan 2013 08:55:56 GMTT4FFhttp://www.sqlservercentral.com/Forums/Topic1410874-373-1.aspxHi I am having some trouble on how to normalize some tricky tables.Basically it is a recurrence event table that can either be daily, weekly, monthly or yearly.I have made it so each recurrence row in the header table can have one id for either daily, weekly, monthly or yearly. This id corresponds to the appropriate table. Each table has different structures.So here is an example of what I have.Table: RecurrenceColumns: RecurrenceId (PK), RecurrenceDailyId(FK), RecurrenceWeeklyId (FK), RecurrenceMonthlyId (FK), RecurrenceYearlyId (FK)Table: RecurrenceDailyIdColumns: RecurrenceDailyId(PK), EveryDays, DayOfWeek etcTable: RecurrenceWeeklyIdColumns: RecurrenceWeeklyId(PK), EveryWeeks, TimesAWeek etcetcWhat is the best way to create this structure so it is normalized? am I on the right path?Wed, 23 Jan 2013 19:44:29 GMTgf-350872http://www.sqlservercentral.com/Forums/Topic1403216-373-1.aspx:-)Sat, 05 Jan 2013 05:04:42 GMTmsanchez7http://www.sqlservercentral.com/Forums/Topic1406284-373-1.aspxIt's been a long time since I've dealt with complex database design, and in this case, I'd like to ask the opinions of others who are more current than I am.I need to design a good structure to store and enforce the rules, if practical on two types of information. Constraint: CLR is disallowed.Constraint: SQL Server 2012 SP1 is the DB being targeted.Physical object No physical object relates to any other physical row. Physical objects have at most one descendent virtual object row. Physical objects have "Physical" type data, which is unrelated to the data virtual objects have (mostly non-overlapping)Virtual object All virtual objects have a parent of EITHER a physical object, OR a virtual object, never both. No virtual object is its own parent. All virtual objects relate either directly or indirectly (through ancestor virtual objects) to a physical object. Virtual objects have zero or more descendent virtual object row(s). Virtual objects have "Virtual" type data, which is unrelated to the data physical objects have (mostly non-overlapping)I.e. example objects:Phys1Phys2Phys3Virt1 - parent Phys1Virt2 - parent Phys2Virt3 - parent Virt2Virt4 - parent Virt3Virt5 - parent Virt3My initial idea is one I really don't like, as I don't like conditional field choice (use field A except when you use field B), but I hate fields that join to more than one table even more, which resulted in:DB init[code]CREATE DATABASE [PhysVirtTest] CONTAINMENT = NONE ON PRIMARY ( NAME = N'PhysVirtTest', FILENAME = N'YourPath\PhysVirtTest.mdf' , SIZE = 8MB , FILEGROWTH = 4MB ) LOG ON ( NAME = N'PhysVirtTest_log', FILENAME = N'YourPath\PhysVirtTest_log.ldf' , SIZE = 4MB , FILEGROWTH = 4MB )GOALTER DATABASE [PhysVirtTest] SET COMPATIBILITY_LEVEL = 110GOALTER DATABASE [PhysVirtTest] SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE [PhysVirtTest] SET ANSI_NULLS OFF GOALTER DATABASE [PhysVirtTest] SET ANSI_PADDING OFF GOALTER DATABASE [PhysVirtTest] SET ANSI_WARNINGS OFF GOALTER DATABASE [PhysVirtTest] SET ARITHABORT OFF GOALTER DATABASE [PhysVirtTest] SET AUTO_CLOSE OFF GOALTER DATABASE [PhysVirtTest] SET AUTO_CREATE_STATISTICS ON GOALTER DATABASE [PhysVirtTest] SET AUTO_SHRINK OFF GOALTER DATABASE [PhysVirtTest] SET AUTO_UPDATE_STATISTICS ON GOALTER DATABASE [PhysVirtTest] SET CURSOR_CLOSE_ON_COMMIT OFF GOALTER DATABASE [PhysVirtTest] SET CURSOR_DEFAULT GLOBAL GOALTER DATABASE [PhysVirtTest] SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE [PhysVirtTest] SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE [PhysVirtTest] SET QUOTED_IDENTIFIER OFF GOALTER DATABASE [PhysVirtTest] SET RECURSIVE_TRIGGERS OFF GOALTER DATABASE [PhysVirtTest] SET DISABLE_BROKER GOALTER DATABASE [PhysVirtTest] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GOALTER DATABASE [PhysVirtTest] SET DATE_CORRELATION_OPTIMIZATION OFF GOALTER DATABASE [PhysVirtTest] SET PARAMETERIZATION SIMPLE GOALTER DATABASE [PhysVirtTest] SET READ_COMMITTED_SNAPSHOT OFF GOALTER DATABASE [PhysVirtTest] SET READ_WRITE GOALTER DATABASE [PhysVirtTest] SET RECOVERY SIMPLE GOALTER DATABASE [PhysVirtTest] SET MULTI_USER GOALTER DATABASE [PhysVirtTest] SET PAGE_VERIFY CHECKSUM GOALTER DATABASE [PhysVirtTest] SET TARGET_RECOVERY_TIME = 0 SECONDS GOUSE [PhysVirtTest]GOIF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [PhysVirtTest] MODIFY FILEGROUP [PRIMARY] DEFAULTGO[/code]Old-fashioned way[code]-- The first try, without using HierarchyID - output still needs to be consolidated with FOR XML or other tricksUSE [PhysVirtTest];DROP TABLE dbo.VirtEx1;DROP TABLE dbo.PhysEx1;CREATE TABLE dbo.PhysEx1( ID INT IDENTITY(1,1) NOT NULL ,Data VARCHAR(32) ,CONSTRAINT PKP1 PRIMARY KEY CLUSTERED (ID));CREATE TABLE dbo.VirtEx1( ID INT IDENTITY(1,1) NOT NULL ,VirtParentID INT NULL CONSTRAINT FKVV1 FOREIGN KEY REFERENCES PhysVirtTest.dbo.VirtEx1(ID) ,PhysParentID INT NULL CONSTRAINT FKVP1 FOREIGN KEY REFERENCES PhysVirtTest.dbo.PhysEx1(ID) ,Data VARCHAR(32) ,CONSTRAINT CKOnePar1 CHECK ((VirtParentID IS NOT NULL AND PhysParentID IS NULL) OR (VirtParentID IS NULL AND PhysParentID IS NOT NULL)) ,CONSTRAINT CKVDup1 CHECK (VirtParentID <> ID) ,CONSTRAINT PKV1 PRIMARY KEY CLUSTERED (ID));CREATE UNIQUE NONCLUSTERED INDEX UNIVP1 ON VirtEx1(PhysParentID)WHERE PhysParentID IS NOT NULL;-- Insert Good DataINSERT INTO PhysEx1 (Data) VALUES ('Phys1'), ('Phys2'), ('Phys3');INSERT INTO VirtEx1 (VirtParentID, PhysParentID, Data) VALUES(NULL,1,'Virt1'), (NULL,2,'Virt2'), (2,NULL,'Virt3'),(3,NULL,'Virt4'),(3,NULL,'Virt5')-- Basic data displaySELECT * FROM PhysEx1SELECT * FROM VirtEx1-- Virtual object parental display-- THIS TECHNIQUE ALLOWS STRICTLY LIMITED NESTING (but doesn't loop)-- If you don't have ALL NULLs in the right-most ancestor column,-- you may be missing some ancestors!SELECT Vdesc.*,COALESCE(Vpar4.Data, Ppar4.DATA, Vpar3.Data, Ppar3.DATA, Vpar2.Data, Ppar2.DATA, Vpar1.Data, Ppar1.DATA) AS PhysHost,COALESCE(Vpar1.Data, Ppar1.DATA) AS Parent,COALESCE(Vpar2.Data, Ppar2.DATA) AS GrandParent,COALESCE(Vpar3.Data, Ppar3.DATA) AS GreatGrandParent,COALESCE(Vpar4.Data, Ppar4.DATA) AS GreatGreatGrandParentFROM VirtEx1 VdescLEFT OUTER JOIN VirtEx1 Vpar1ON Vpar1.ID = Vdesc.VirtParentIDLEFT OUTER JOIN PhysEx1 Ppar1ON Ppar1.ID = Vdesc.PhysParentIDLEFT OUTER JOIN VirtEx1 Vpar2ON Vpar2.ID = Vpar1.VirtParentIDLEFT OUTER JOIN PhysEx1 Ppar2ON Ppar2.ID = Vpar1.PhysParentIDLEFT OUTER JOIN VirtEx1 Vpar3ON Vpar3.ID = Vpar2.VirtParentIDLEFT OUTER JOIN PhysEx1 Ppar3ON Ppar3.ID = Vpar2.PhysParentIDLEFT OUTER JOIN VirtEx1 Vpar4ON Vpar4.ID = Vpar3.VirtParentIDLEFT OUTER JOIN PhysEx1 Ppar4ON Ppar4.ID = Vpar3.PhysParentID-- Test Bad Data preventionINSERT INTO VirtEx1 (VirtParentID, PhysParentID, Data) VALUES(NULL,1,'DuplicatePhysParent')INSERT INTO VirtEx1 (VirtParentID, PhysParentID, Data) VALUES(NULL,NULL,'NoParent')INSERT INTO VirtEx1 (VirtParentID, PhysParentID, Data) VALUES(1,1,'TooManyParent')INSERT INTO VirtEx1 (VirtParentID, PhysParentID, Data) VALUES(111,NULL,'VirtParentNotExist')INSERT INTO VirtEx1 (VirtParentID, PhysParentID, Data) VALUES(NULL,111,'PhysParentNotExist')[/code]Possible hierarchyid based, but this is my very first foray into hierarchyid, and trying to get the Data values for ancestors isn't as simple as I'd hoped.[code]-- The second try, using HierarchyID - output is still not coded at allUSE [PhysVirtTest];DROP TABLE dbo.VirtEx2;DROP TABLE dbo.PhysEx2;CREATE TABLE dbo.PhysEx2( ID INT IDENTITY(1,1) NOT NULL ,Data VARCHAR(32) ,CONSTRAINT PKP2 PRIMARY KEY CLUSTERED (ID));CREATE TABLE dbo.VirtEx2( ID HIERARCHYID NOT NULL ,PhysParentID INT NULL CONSTRAINT FKVP2 FOREIGN KEY REFERENCES PhysVirtTest.dbo.PhysEx2(ID) ,Data VARCHAR(32) --,CONSTRAINT CKOnePar2 CHECK ((VirtParentID IS NOT NULL AND PhysParentID IS NULL) OR (VirtParentID IS NULL AND PhysParentID IS NOT NULL)) ,CONSTRAINT PKV2 PRIMARY KEY CLUSTERED (ID));CREATE UNIQUE NONCLUSTERED INDEX UNIVP2 ON VirtEx2(PhysParentID)WHERE PhysParentID IS NOT NULL;-- Insert Good DataINSERT INTO PhysEx2 (Data) VALUES ('Phys1'), ('Phys2'), ('Phys3');-- Hierarchyid guideance from http://stackoverflow.com/questions/1352778/sql-2008-hierarchyid-with-multiple-root-nodesINSERT INTO VirtEx2 (ID, PhysParentID, Data) VALUES(hierarchyid::GetRoot().GetDescendant(NULL,NULL),1,'Virt1') INSERT INTO VirtEx2 (ID, PhysParentID, Data) VALUES(hierarchyid::GetRoot().GetDescendant((select MAX(ID) from VirtEx2 where ID.GetAncestor(1) = hierarchyid::GetRoot()),NULL),2,'Virt2') DECLARE @VirtParent hierarchyid DECLARE @VirtNew hierarchyidSELECT @VirtParent = ID FROM VirtEx2 WHERE Data = 'Virt2';INSERT INTO VirtEx2 (ID, PhysParentID, Data) VALUES(@VirtParent.GetDescendant(NULL,NULL),NULL,'Virt3') SELECT @VirtParent = ID FROM VirtEx2 WHERE Data = 'Virt3';SELECT @VirtNew = MAX(ID) FROM VirtEx2 WHERE ID.GetAncestor(1) = @VirtParent;-- Method that works only for the first node: INSERT INTO VirtEx2 (ID, PhysParentID, Data) VALUES(@VirtParent.GetDescendant(NULL,NULL),NULL,'Virt4') INSERT INTO VirtEx2 (ID, PhysParentID, Data) VALUES(@VirtParent.GetDescendant(@VirtNew,NULL),NULL,'Virt4') SELECT @VirtNew = MAX(ID) FROM VirtEx2 WHERE ID.GetAncestor(1) = @VirtParent;INSERT INTO VirtEx2 (ID, PhysParentID, Data) VALUES(@VirtParent.GetDescendant(@VirtNew,NULL),NULL,'Virt5') -- Basic data displaySELECT * FROM PhysEx2SELECT ID.ToString() AS IDString, ID.GetLevel() AS IDLevel, * FROM VirtEx2-- ??? Advanced data display - parents and grandparents.-- Test Bad Data prevention--'DuplicatePhysParent'--'NoParent'--'TooManyParent'--'VirtParentNotExist'--'PhysParentNotExist'[/code]Thank you all for your time in reading and/or replying.Fri, 11 Jan 2013 15:42:51 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic1401761-373-1.aspxHi there,I was wondering if there is experience one where about the optimal filesize for databases (2012) lies.I am creating a new table that will have quite an amount of rows.Approximately about 1,5 billion rows per month.The size of a rows will be 20 byte.As I have a date-column I will be partitioning by date. As this table will be filled for many years I also decided to create a table for each year and UNION ALL them with a view (constrain on date, so the optimizer can eliminate tables within the view)I have 24 LUNs in the system, so I will create 24 files for each filegroup to equally arrange the data.For each (year-)table I will have about 335 Gbyte so I would create 24*14GB-Files for each year.However I could go further on and create 24 files for each month, so that would be about 1GB per file having 12 filegroups for each year (arranging it with partition function/scheme)Version a: The partitioning will be over days but all in one filegroup. (Files 24x14GB)Version b: The partitioning will be over days, each month in one filegroup. (Files 12x24x1,2GB)I am not worried about the "work" to create all these files and groups but I was wondering, what is better for the sql server.More smaller files or fewer bigger files.ThxMitchWed, 02 Jan 2013 04:13:38 GMTchristian.terharthttp://www.sqlservercentral.com/Forums/Topic1393079-373-1.aspxI am looking to find or create a robust list of decision points for database naming conventions. I'll start with a list and ask people to suggest useful additions. For this exercise I do not care to indicate an preference for any particular conventions, just to create a list that any organization could use to set their standards.In no particular order, but numbered for possible reference:1.Use of Hungarian (prefix or suffix) for object names ("tibbling")2.Pluralizing of tables 3.Abbreviating and standard abbreviations ("abrvtng")4.Use or avoidance of [Escaping]5.Indication word boundaries (pascal, camel, _, etc.)6.Capitalization scheme7.Key naming8.Association/join/many-to-many table namesWed, 05 Dec 2012 09:28:40 GMTKarl Kieningerhttp://www.sqlservercentral.com/Forums/Topic1384915-373-1.aspxHello.I'm working on a system that was originally designed with UUID (uniqueIdentifier) clustered indexes.Later the clustered indexes were rebuilt on an INT identity column to boost the performance of the index. Because the schema (and code base) reference the UUIDs as primary keys everywhere this was never changed.I'm now concerned that we are doing an extraordinary number of key lookups. For example, when ever a table is joined on the UUID primary key, no additional data is in the index so it must be looked up using the new INT.My question is: is it better to have all of these key lookups happening or would it be better to just build the clustered indexes on the UUIDs? Alternatively I could include an inordinate amount of columns making the primary key index wide, but at that point wouldn't it be similar to reorganizing my clustered index on the UUID?Any thoughts would be appreciated. Thanks, DaveWed, 14 Nov 2012 16:08:31 GMTdave-Lhttp://www.sqlservercentral.com/Forums/Topic1385868-373-1.aspxHi AllI ask you guidance on this design question because this seems to be one of those things where if you don't get it right and it gets implemented much suffering will follow...I need to design some advanced group functionality. I am calling the three functions I need 'group of groups' and 'group minus group' and 'groups plus manual'Given the group 'Blue and Yellow'. Clearly I can add all the individual blue and yellow stars to this group but I would like to define the 'Blue and Yellow' group as being comprised of the two groups: Bluestars and Yellowstars.Given the group '10 Brightest No Supergiants'. I would like to define this as being composed of the members of the group '10 Brightest' minus the group 'Supergiants'.The group 'Blue stars and my current favorite star' should be composed of the group Bluestars and one other star manually selected.Any design ideas would be much appreciated.CREATE TABLE #Stars (Star varchar(30)) INSERT INTO #Stars (Star) VALUES ('Sirius') INSERT INTO #Stars (Star) VALUES ('Canopus') INSERT INTO #Stars (Star) VALUES ('Rigil Kentaurus') INSERT INTO #Stars (Star) VALUES ('Arcturus') INSERT INTO #Stars (Star) VALUES ('Vega') INSERT INTO #Stars (Star) VALUES ('Capella') INSERT INTO #Stars (Star) VALUES ('Rigel') INSERT INTO #Stars (Star) VALUES ('Procyon') INSERT INTO #Stars (Star) VALUES ('Achernar') INSERT INTO #Stars (Star) VALUES ('Betelgeuse')CREATE TABLE #StarGroups (StarGroup varchar(45)) INSERT INTO #StarGroups (StarGroup) VALUES ('10 Brightest') INSERT INTO #StarGroups (StarGroup) VALUES ('Supergiants') INSERT INTO #StarGroups (StarGroup) VALUES ('Bluestars') INSERT INTO #StarGroups (StarGroup) VALUES ('Yellowstars') INSERT INTO #StarGroups (StarGroup) VALUES ('10 Brightest No Supergiants') INSERT INTO #StarGroups (StarGroup) VALUES ('Blue and Yellow') INSERT INTO #StarGroups (StarGroup) VALUES ('Blue stars and my current favorite star')CREATE TABLE #StarGroupMembers (StarGroup varchar(15),Star varchar(30)) INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Sirius') INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Canopus') INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Rigil Kentaurus') INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Arcturus') INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Vega') INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Capella') INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Rigel') INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Procyon') INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Achernar') INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Betelgeuse') INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('Supergiants','Rigel') INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('Supergiants','Achernar') INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('Supergiants','Betelgeuse') INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('Bluestars','Rigel') INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('Yellowstars','Procyon') INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('Yellowstars','Capella') SELECT * FROM #StarsSELECT * FROM #StarGroupsSELECT * FROM #StarGroupMembersDROP TABLE #StarsDROP TABLE #StarGroupsDROP TABLE #StarGroupMembersFri, 16 Nov 2012 14:58:51 GMTChrissy321http://www.sqlservercentral.com/Forums/Topic1283053-373-1.aspxNEWBIE to DW : the IT Manager wants to use varchar2(2000) for all fields in all tables.I thought we should be more realistic. Sure, use varchar2, but if it is a state, use 2 or even 4.Any norms is this area? Anything "bad" about using varchar2(2000) for everything?Manager's idea is to make sure we capture the data in staging exactly like it is in the flat file. Thanks!JoeFri, 13 Apr 2012 05:52:56 GMTdevereauxjhttp://www.sqlservercentral.com/Forums/Topic1338156-373-1.aspxAnd by third parties, I mean of all parties, Microsoft themselves. If a column needs to be added to a table, you'd think it would be as simple as executing ALTER TABLE ADD <column info>, right? Except that in at least two cases this is not what Microsoft does. In CRM and in, of all things, scripts generated by SSManagement Studio, MS has the system create a copy of the old table, copy data from old to new, drop old, rename new. Uh, WHY? This is about as inefficient as it gets and is a disaster on very large tables. And worst of all, replication will not work with this approach requiring replication to be deleted before a CRM deploy and re-added after the deploy.So the rant is, these developers should know better. What on earth possesses them to take lame shortcuts to make it easier on them while shifting the pain to the production end? Yes, I have hit this situation at work and until we go to AlwaysOn in 2012 there's nothing I can do about it.Rant over. Anyone who knows the reasons why such things happen feel free to say so and remove the feeling of helplessness generated by such practices. ARGH!Tue, 31 Jul 2012 13:59:10 GMTjeff.masonhttp://www.sqlservercentral.com/Forums/Topic991752-373-1.aspxHi AllWe need to create a clustered index by implementing schema binding in the view. As you know it throws error. Could anyone possibly tell me to achieve in alternate way?Thanks in advanceVinThu, 23 Sep 2010 00:18:54 GMTvinothrajhttp://www.sqlservercentral.com/Forums/Topic1377803-373-1.aspx1.- After how many records is appropiate to begin to use indexes?2.- Is it correct to create a nonclustered index on a foreign key before to populate table?, or after? I mean in this way: a) Create database script with tables and only clustered indexes b) Create another script to update database with nonclustered indexes "only" for foreign keys ([i]I read is appropiate to do in two steps 'Clustered and Nonclustered'[/i]) c) Populate d) Run another one script with nonclustered index columns to be use to perform selections, like columns used on [order by] clause, or columns deterministics on results ([i]not foreign keys[/i]). Or a) Create database script with tables and only clustered indexes b) Populate c) Create another script to update database with nonclustered indexes ([i]Fk and columns to perform selections[/i]) Or a) Create database script with tables and only clustered indexes b) Create another script to update database with all kind of nonclustered indexes c) Populate3.- Is it correct to group "foreign key indexes" with normal column indexes?I mean, if I have two tables ([i][b]menu_cat[/b] and [b]submenu_cat[/b][/i]) with a column called [mnu_order] on [b]submenu_cat[/b], I read is it good idea to create index on [mnu_order] field, because is a colum to be used on [order by] clause ([i]all time[/i]),, also I have [menu_id]([i]Foreign key[/i]), is it correct in this way to do a group index?:[code="sql"]CREATE NONCLUSTERED INDEX [IX_SubMenu] ON [dbo].[subMenu_cat] ( [Menu_iD] ASC, [mnu_Order] ASC)[/code]Or I need to separate[code="sql"]CREATE NONCLUSTERED INDEX [IX_SubMenu_Menu_iD] ON [dbo].[subMenu_cat] ( [Menu_iD] ASC)CREATE NONCLUSTERED INDEX [IX_SubMenu_mnu_order] ON [dbo].[subMenu_cat] ( [mnu_Order] ASC)[/code]There is a difference doing this([i]internally[/i])?, this fields ever will be used on [where] clause.Thanks in advance, I'm learning about indexes and I have many doubts yet. Sorry for my bad english.Fri, 26 Oct 2012 13:12:24 GMTCarl0s_http://www.sqlservercentral.com/Forums/Topic1101412-373-1.aspxI've just read a dozen articles/posts where people absolutely condemn the use of EAV's (Entity, Attribute, Value) and NVP's (Name, Value Pair) tables and lambasted anyone who stood up and even eluded to a possible good use.So... let me ask with the promise that I'm not going to lambaste anyone for their opinion... when you have a front end app that allows users to build their own custom "record" format, how do YOU store that information in a database without using EAV's or NVP's? Or is THAT the exception?Thanks ahead of time for your thoughts, folks.Sun, 01 May 2011 19:40:00 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1377154-373-1.aspxSo I'm trying to help out our developers, and trying to make the DB for the application a little more "sane" and normalized. The devs are doing most all access through a .NET application and I'm not worrying at the moment about what changes would need to be made to the application.I know I'm not going to get everything right, and that's OK too, this is a start point for cleaning up the existing DB before this application goes out to dozens / hundreds of clients and fixing / cleaning up the DB becomes a logistical nightmare...What I'm looking for, is a suggestion for a starting point. I've got an empty copy of the DB I can use to make sure I keep the same column names, and I expect to be creating new tables.So, my question is pretty much, where to start? If this were you doing this, would you start by building up a list of the tables and columns, maybe printing out an ERD (possibly one generated by Database Diagrams in SQL,) or would you pick a table and start there?I know everyone likely has their own way of building up a DB and tables, but I'm betting there's at least some consistency when re-building a DB to make it more efficient / normalized (at least when you also have access to / control of the application itself)Thanks,Jason A.Thu, 25 Oct 2012 11:21:48 GMTjasona.workhttp://www.sqlservercentral.com/Forums/Topic1376997-373-1.aspxi am desiging database and i need answer.for example i am getting flat file ,which contains pharmacy into and patient infopharmacy :pharmacyid primary keynamepatient:patientid primary keyfirstnamelastnamenow they dont have any relation between them,i can have multiple record of patients from same pharmacy, that i need to store, so if there is pharmacyid is primary key, how can i store tha record.similarly i can have multiple records for same patient from different pharmacy.what should i doplease help me with design, what kind of relation i need to make,do i need to create third tableThu, 25 Oct 2012 07:55:04 GMTharri.reddyhttp://www.sqlservercentral.com/Forums/Topic1372547-373-1.aspxHey guys, I have some problems with normalizing the data.Could you take a look at this filehttp://speedy.sh/YtK9G/example.xlsxThese is the list of attributes: [u]week[/u] [u]site[/u] day timeSlot employee role sick totalHrsWeek and site will be a compound key.A lot of things confuses me here so I'd really appreciate your help. Thanks in advance :)Sun, 14 Oct 2012 17:17:58 GMTzesralem_siehttp://www.sqlservercentral.com/Forums/Topic1368571-373-1.aspx[b]Can someone help me to clear this situation please, I have the tables shown below:[/b][code="sql"]CREATE TABLE [dbo].[[b]Country.cat][/b]( [[b]Country_iD[/b]] [smallint] NOT NULL, [[b]FIPS[/b]] [char](2) NULL, [[b]Country[/b]] [varchar](64) NOT NULL, [[b]Continent[/b]] [char](3) NOT NULL, CONSTRAINT [[b]PK_Country_iD[/b]] PRIMARY KEY CLUSTERED ( [Country_iD] ASC )[/code] [b]Data sample[/b][b]Country_iD[/b]: 32 (Pk) (Clustered index)[b]FIPS[/b]: AR[b]Country[/b]: Argentina[b]Continent[/b]: SA[b]As we know MSSQL generate clustered index for primary key if I not specify another thing, until here all fine and clear for me, now I have Entity table:[/b] -- Entity refer to states or provinces[code="sql"] CREATE TABLE [dbo].[[b]Entity.cat[/b]]( [[b]Country_iD[/b]] [smallint] NOT NULL, [[b]Entity_iD[/b]] [smallint] NOT NULL, [[b]Entity[/b]] [varchar](32) NOT NULL, CONSTRAINT [[b]PK_Entity_iD[/b]] PRIMARY KEY CLUSTERED ( [Pais_iD] ASC, [Entity_iD] ASC )[b] ... -- I deleted default properties for simplify sample[/b]GO[b]... -- more default code and[/b] ALTER TABLE [dbo].[Entity.cat] WITH CHECK ADD CONSTRAINT [[b]FK_Entity_Country_iD[/b]] FOREIGN KEY([Country_iD]) REFERENCES [dbo].[Country.cat] ([Country_iD])GO ALTER TABLE [dbo].[Entity.cat] CHECK CONSTRAINT [FK_Entity_Country_iD]GO[/code][b]Data sample[/b][b]Country_iD[/b]:32 (Pk)(Fk)[b]Entity_iD[/b]: 1 (Pk)[b]Entity[/b]:Buenos Aires [b]The doub is here:[/b] [b]Must I create a nonclustered index for my Fk ([/b][FK_Entity_Country_iD][b])?? or not because is part of a clustered index. Thanks in advance for help[/b]Thu, 04 Oct 2012 10:35:50 GMTCarl0s_http://www.sqlservercentral.com/Forums/Topic1368716-373-1.aspxGreetings,I have a design problem that I am having trouble with.I'll preface with the fact that I am not real experienced with SQL design.My problem is that I have 2 tables with data (Operations and Objects) that are joined in a many-to-many relationship with a bridge or junction table (Permissions). Now I want to assign roles to permissions by creating a many-to-many relationship between the Roles table and the Permissions table, which is itself a junction table. To accomplish the many-to-many relationship between these, I have a RolesToPermissions junction table. The problem arises when I try to make the 1-to-many relation between the Permissions junction table and the RolesToPermissions junction table. SQL server will not let me make this relation.Is there any other way around this, or am I doing something wrong here.OperationsTable ObjectsTable--------------- ------------- opID(PK) objID(PK) name name \1 /1 \ / \ / \many /many Permissions (Junction table) ------------- opID(PK) objID(PK) permID(PK) ?1 ? ? <-- error when making relation ? ?many RolesToPermissions (Junction table) ------------------- roleID(PK) permID(PK) |many | | | |1 RolesTable ---------- roleID(PK) nameThanks,AbbyThu, 04 Oct 2012 14:28:55 GMTabby1240http://www.sqlservercentral.com/Forums/Topic1367268-373-1.aspxCan anybody advise if the below design is good for a database of to-do lists where different users can submit lists of 5 items? Thanks :-)user id name1 John2 Mike list id userid1 12 13 2 listname id name1 Housekeeping2 Banking task id name1 Laundry2 Washing up3 Bake cookies4 Clean bathroom5 Water garden6 Pay bills7 Transfer funds8 Apply for loan9 Close account10 Cancel credit card11 Vacuum12 Wash car13 Tidy garagelistnametask listid listnameid taskid1 1 11 1 21 1 31 1 41 1 52 2 62 2 72 2 82 2 92 2 103 1 33 1 113 1 123 1 13 1 13Tue, 02 Oct 2012 13:45:47 GMTjoseph.moffatthttp://www.sqlservercentral.com/Forums/Topic1363587-373-1.aspxHello,I seem to recall from my database design classes a while back, being told that a link table with just a datetime field in wasn't really a link table.I've just designed exactly that, but can't see another way?Is this true, and what would the alternative be?[code="sql"]create table sourcefile (sourcefileid int primary key, name varchar(500));create table updateserver (updateserverid int primary key, name varchar(500));create table sourcefiles_updateserver (sourcefileid int, updateserverid int, updatetime datetime, primary key (sourcefileid, updateserverid),foreign key (sourcefileid) references sourcefile (sourcefileid),foreign key (updateserverid) references updateserver (updateserverid));[/code]CheersMon, 24 Sep 2012 09:56:01 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1349820-373-1.aspxI'm considering a change to our current design and I'm curious if anyone has advice or similar experience they can share. I'm sure there is a name for the pattern, but I don't know what it is. We have a Phone table and it is referenced by several other tables - locations have phones, people have phones, and there are a couple other objects that have phones as well. Our initial design had a single phone table and many-to-many join tables between phone and the owning object (e.g. LocationPhone, PersonPhone, etc.). That seems to be the correct "normalized" design, but I'm seeing now that we don't plan to have phones shared among locations and persons, so having them together doesn't give us much benefit. In fact, having them separated would remove ambiguity about what "type" the phone was for (is this number for a location or a person?), make smaller tables (faster queries), and allow us to add different fields that were unique to phones at a location if we needed to in the future. Is this a good or bad pattern? It "feels" wrong - everything should be normalized right? But logically it seems like in this case I'm not getting any benefit from having it all together and the ambiguity and performance makes me lean toward separating them out.This pattern plays out in a couple other places, maybe 3-4 other tables hooked up similar to how phones are done.Fri, 24 Aug 2012 11:01:40 GMT Chad Crawfordhttp://www.sqlservercentral.com/Forums/Topic1360886-373-1.aspxI've encountered a data design that is new to me. Frankly, I want to gouge my eyes out because it is a beat-down to work with. However, I am posting here to see if somebody can enlighten me about how this might be aN appropriate or useful data design. (I am receiving this from an external source and must incorporate it into our internal data.)The following example is a fictional parallel used to illustrate this data design.Table 1: ProductsProductID ProductName123 Widget456 Gridget789 ZidgetTable 2: ProductAttributesProductID AttributeTable AttributeTableID123 Color 12123 Size 333456 Shape 9789 Weight 60Table 3: ColorColorID Color12 Blue13 Red14 Yellow15 GreenTable 4: SizeSizeID Size123 Small124 Medium125 LargeTable 5: ShapeShapeID Shape456 Round457 SquareTable 6: WeightWeightID Weight789 Light780 HeavyThe booger is table 2. One would associate table 1 to table 2 based on ProductID. Then table 2 is used to look up attributes. The AttributeTable column tells you what table to look in and the AttributeTableID tells you what ID to use.Also a product may have anywhere from zero attributes (rows in table 2) or as many rows as there are attribute tables.Does this make any sense? Does this have any value? Is there a name for this approach? Is this common? Any thoughts are welcome.Thanks.Tue, 18 Sep 2012 10:20:28 GMTdglanehttp://www.sqlservercentral.com/Forums/Topic1352647-373-1.aspxHiI am after a bit of advice.... I am considering the design for some tables in my database and wondered if anyone has any experience/ideas about the following structures.[b]Background Info[/b]I need to created a CommunicationHistory table, this can hold communication history against a Customer, a Contact, a Job, a Task, a Product etc (could be associated to other things going forwards)[b]Option 1[/b]I create nullable fields in the CommunicationHistory table for CustomerId, ContactId, JobId, TaskId, ProductId etc[b]Option 2[/b]I create a table called 'Entity' that contains the common information about all 'Entities', e.g. EntityId, EntityTypeId, CreatedDate, CreatedByUserId...Each of the Customer/Contact/Job/Task/Product tables have an EntityId FK field.The CommunicationHistory table then has a FK to EntityId[b]Option 3[/b]I create a CommunicationHistory table that stores all records and then linking tables for each entity e.g. CustomerCommunicationHistory(CommunicationHistoryId, CustomerId) , ContactCommunicationHistory(CommunicationHistoryId, ContactId), JobCommunicationHistory(CommunicationHistoryId, JobId) etc...-----------------------------------------What do you think? Are there any other options?Thanks in advance for your help and suggestions.Fri, 31 Aug 2012 02:39:36 GMTcraigbroadman