Representation For Deleted Entities: Difficult Question
Jul 20, 2005
Our customer (of our ecommerce system) wants to be able to preserve
deleted entities in the database so that they can do reporting,
auditing etc.
The system is quite complex where each end user can belong to multiple
institutional affiliations (which can purchase on behalf of the user).
The end user also has a rich trail of past transactions affiliations
etc. Thus in the schema each user entity is related to many others
which in turn relate to yet others and so on.
In the past when a user was deleted all of his complex relationships
were also deleted in a cascading fashion. But now the customer wants
us to add a "deleted" flag to each user so that a user is never
_really_ deleted but instead his "deleted" flag is set to true. The
system subsequently behaves as if the user did not exist but the
customer can still do reports on deleted users.
I pointed out that it is not as simple as that because the user entity
is related to many, many others so we would have to add this "deleted"
flag to every relationship and every other entity and thus have
"deleted" past purchases, "deleted" affiliations - a whole shadow
schema full of such ghost entities. This would overtime degrade
performance since now each query in the system has to add a clause:
"where deleted = 0".
I assume this is a standard problem since many organizations must have
this need of preserving deleted records (for legal or other reasons).
I tried to talk them into creating a simple audit file where all the
deletions will be recorded in XML but they were not too happy with
that.
Is there a more satisfying solution to this than have this "deleted"
flag?
Apologies if this has already been asked and answered, though I haven't found it via search.
In my report model I have a lookup entity that consists of a Code field (PK) and a Description field this is linked to a parent entity (table) and works fine, e.g. the description is shown in the parent entity instead of the FK code. However this lookup entity would be useful linked to many other entities (tables) which use the same FK code linked to the lookup entity's Code field (PK) for use of the Description field. In my first attempt I linked the lookup to a second table entity but unlike the originally linked table entity, this is not shown as the description in report builder, only the role appears and I have to select the role then the description from the 2 fields displayed (Code, Description), additionally I get shown the related first table entity.
Firstly can a lookup entity be linked to multiple tables? Secondly, if so am I forgetting something so only the description is shown like in the first linked table entity? Thirdly, if I correct the problem is there a property I need to enable/disable to stop the first linked table entity appearing in the second table entity entity list in report builder or should I just leave it?
Any help/advice/suggestions would be appreciated, and apologies for long list of questions but all the books and help files I have used don't seem to use imperfect/real life data sources and examples.
My company is working on a bond derivative portfolio analysis tool andwe're facing a problem that I did not see adequately addressed anywhere in literature. I really did RTFM. I'm very experienced inrelational modelling (10+ years) so this is not a case of notunderstanding the principles. Here is the problem stripped ofirrelevant context. The problem below is simplified for the sake of theexample so don't sweat the details.THE PROBLEM1. There are many types of bonds, each type has a different set ofattributes, different attribute names, different attribute datatypes.For example, bond A has two variables: a yearly interest rate anddate of issue, B has five variables: an interest rate and 4 specificdates on which various portions of principal need to be paid, bond Chas a set of 4 variables: interest rate in period 1, interest rate inperiod 2, the date on which the bond can be put back to the issuer,and two dates on which the bond can be called by the issue. And so on.So, on the first attempt I could represent each bond type as its owntable. For example,create table bond_type_a (rate INTEGER, issue_date DATE)create table bond_type_b (rate INTEGER, principle_date1 DATE,principle_date2 DATE, principle_date3 DATE, principle_date4 DATE)create table bond_type_c (rate1 INTEGER, rate2 INTEGER, put_date DATE,call_date DATE)This is the nice relational approach but it does not work because:2. There are many thousands of bond types thus we would have to havemany thousands of tables which is bad.3. The client needs to be able construct the bond types on the flythrough the UI and add it to the system. Obviously, it would be bad ifeach new type of bond created in the UI resulted in a new table.4. When a user loads the bond portfolio it needs to be very fast. Inthe table per type approach if a user has a 100 different types if bondin the portfolio you would have to do 100 joins. This is a heavilymulti user environment so it's a non-starter. It's impossibly slow.THE SOLUTIONSSo now that we ditched the table per bond type approach we can considerthe followiing solutions (unpleasant from the relational point ofview):1. Name-Value pairs.create table bonds (bond_id INTEGER, bond_type INTEGER, attribute_idINTEGER, value VARCHAR(255))Comment: The client does not like this approach because they want torun various kinds of reports and thus they doe not want the values tobe stored as VARCHAR. They want the DB to enforce the datatype.2. Typed Name-Value pairs.create table bonds (bond_id INTEGER, bond_type INTEGER, attribute_idINTEGER, int_val INTEGER, string_val VARCHAR(255), date_val DATE_Comment: The client does not like this because the table is sparse.Every row has two empty fields.3. Link table with table per data type.create table bonds (bond_id INTEGER)create table bond_int_data (bond_id INTEGER REFERENCES bonds(bond_id),value INTEGER)create table bond_string_data (bond_id INTEGER REFERENCESbonds(bond_id), value VARCHAR(255))create table bond_date_data (bond_id INTEGER REFERENCES bonds(bond_id),value DATE)Comment: This meets most of the requirements but it just looks ugly.4. Dynamic Mappingcreate table (bond_id INTEGER, int_val1 INTEGER, int_val2 INTEGER,date_val1 DATE, date_val2 DATE, string_val1 VARCHAR(255), string_val2VARCHAR(255))Then you have to add some dynamic mapping in your code which willprovide bond specific mapping (say, stored in an XML file). Forexample,For bond_A: yearly_rate maps to int_val1, issue_date maps to date_val1For bond_C: rate1 maps to int_val1, rate2 maps to int_val2, put_datemaps to date_val1, call_date maps to date_val2)Comment: This is very good for performance because when I load aportfolio of different bond types I can pull them all in in one SELECTstatement. However this approach has a problem that the table issparse. The number of fields of each type has to be as high as toaccmodate the most complex bond while simple bonds will only be usingtwo or three.THE QUESTIONS:Are the four approaches I described above exhaustive? Are there anyother that I overlooked?
Hi,I want to get the string representation of a hex number from avarBinary column of a table.For example I want to get the output : 'The Hex value is 0xFF'butselect 'The Hex value is ' + convert(varchar(10), 0xFF)retruns the ascii charecter for 0xFFAny idea how do I get the hex form as it is?thanks.Supratim
In MS Access, for numeric fields, the decimal places shown can be defined as "Auto" meaning that the database will determine the number of decimal places to show based on the content of the field (i.e. 1.0, 0.75, 1.125).
In SQL Server for the same field, it appears that decimal precision is hard coded resulting in a fixed representation (i.e. 1.000, 0.750, 1.125)
Is there a way to make the decimal representation in SQL Server more like Access where trailing zeros are truncated?
I am trying to take a hexadecimal representation of a binary number and convert to the true binary representation so that I can compare it against a binary field in one of my tables.
After reading the documentation it seems I should be able to do this with the CAST or CONVERT function. However it does not appear to be working correctly.
Can you tell me why this T-SQL code produces the wrong binary value:
Here is my sample table creation and insertion script. I want represent as a summary-result using just one record. How can I ? Please note the below red color text.
create table policy ( id int not null, name nvarchar(50) not null, constraint pk_policy primary key(id) );
create table localhost ( policy_id int not null, id int not null, ip_begin binary(4) not null, ip_end binary(4) not null, prefix tinyint not null default 0, constraint pk_localhost primary key(policy_id,id) );
create table remotehost ( policy_id int not null, id int not null, ip_begin binary(4) not null, ip_end binary(4) not null, prefix tinyint not null default 0, constraint pk_remotehost primary key(policy_id,id) );
create table rate ( policy_id int not null, inbound int not null, outbound int not null, constraint pk_rate primary key(policy_id) );
------------- insert into policy values(0,N'policy0');
insert into localhost values(0,0,0xC0A80101,0xC0A80101,24); insert into localhost values(0,1,0xC0A80A01,0xC0A80B01,0);
insert into remotehost values(0,0,0xACA80101,0xACA80101,0); insert into remotehost values(0,1,0xACA80A01,0xACA80B01,0); insert into remotehost values(0,2,0xACA80C01,0xACA80C01,24);
insert into rate values(0,1000,2000);
------------- select * from policy; select * from localhost; select * from remotehost; select * from rate;
-- result of policy table 0 policy0
-- result of localhost table 0 0 0xC0A80101 0xC0A80101 24 0 1 0xC0A80A01 0xC0A80B01 0
-- result of remotehost table 0 0 0xACA80101 0xACA80101 0 0 2 0xACA80C01 0xACA80C01 24
-- result of rate table 0 1000 2000
------------------------------------------------------ desired result set id name localhost remotehost rate -- ---------- ---------------------------------------------------------------- ------------------------------------------- ----------------- 0 policy0 (192.168.1.1/24),(192.168.10.1~192.168.11.1) (172.168.1.1),(172.168.12.1/24) 0,1000,2000
descriptin of desired result set : 0. key value is id column and is referenced by each policy_id column. 1. id and name column is the same as policy table's. 2. localhost and remotehost columns are intigration of ip_begin,ip_end,prefix. 3. ip_begin and ip_end should be converted dotted presention from numeric format. 4. if prefix greater than 0, it should be displayed using '/'. 5. if ip_begin and ip_end are equal, show just one ip. 6. if the two ips are different from each other, they separated by '~'. 7. rate field is packed divided by ','.
Hi, I’ve three questions, would someone answer them please,
I have 3 entities: The book entity(BookNo,BookNmae,AuthorName,Price...etc), the MembershipCard entity(CardNo,MembershipNo,ClientNo,PublishDate,EmpNo..etc), and the Client entity(ClientNo,ClientName,ClientAddr,ClientPhoneBookNo,RequiredBookNo,..etc) 1-What is the relationship degree between them, is it ternary or binary?and Why? I'm so confused ,my answer is ternary, I made a (1:m) relationship between the clients entity and the books entity (a client may buy more than one book), (1:m) relationship between the employee entity and the books entity (an employee may sell more than one book)and a (1:m) relationship between the employee and the Client(an employee may serve more than one client(is it right or it's (M:N)). So is my answer is right or wrong, please help me to understand, I’m so confused.
2- Is the cardinality degree between the Client entity and the book entity , (0,N) for the client entity and (1:N) for the book entity??
3-What is the best model to use in designing data in general,Chen's model or Crow's Foot and why?I think it's Crow's one ,for it's more arranged and easy to read.
I created a report model with a single "data source view." The datasource view has three tables "Business, XRef, Employee" and a named query "PeopleCount" based on these tables. The smdl [design] view shows "Business, XRef,Employee" as "entities" and does not show the named query from the "data source view" (I don't know if that is normal). I published this report model to the reporting server (its showed "success").
I then used Report Builder to open the report model as a "chart", and it shows there is only one entity for me to play with, its called "XRef." Why do the other tables ("Business" and "Employee") not show up in the Report Builder's list of entities?
My own guess is that "XRef" is the only "actionable" table for making a chart. Indeed, I notice that among its "fields" is one called "Business Title" (which apparently comes from the Business table). But I also notice it does not have an "Employee Name." Why have the former and not the latter? Again, my own guess is that the type of the Employee.Name field, a nvarchar(MAX), might be considered unusable for reporting, since it is a "max" size. Thoughts?
I am using SQL Server 2005 RTM, which is running on Win 2K3 Server SP1.
Why is it that only SOME of the tables in my database show up in the Entities pane?
There are a total of six tables in my database, yet only three of them are available to me in the Entities pane. I have looked at permissions, etc, and I am using the SA account. It seems like I should see either ALL or NONE of the tables, but that is not the case.
A Composite entity can be made out of two Primary Keys (PK) of two different entities. Therefore it can be called as a link table. Therefore the composite entity depends upon the other two entites. Therefore the composite entity is a exsitence dependent on the other two entities.
Dear GroupCan anyone provide a sample query for the following scenario?Let's assume I want to search for an order someone placed which mightbe an individual or company. An individuals first name is stored incolumn FirstName And the individuls last name in column LastName of thecontact table and the company name is stored in column CompanyName ofthe company table.If a user issues a query with CName parameter how can I search allthese columns and tables at the same time to see if there's a match? Imight add that there's a column in the orders table that holds0=Contact 1=Company but ideally I don't want to use this column.Thanks very much for your help and efforts.Martin
I'm attempting to design a schema for a project I working on. The idea is that I can store a list of documents and associate meta data with them.
The problem is that I don't know what that meta data will be at design time. The user will create the meta data fields in the software. For the first design I just had a table called meta field that contained the meta field defintion, and another table called itemdata that contained the document primary key, the metafield primary key and a value encoded as a varchar. This works fine while the list of documents is small.
Another idea I've thought of is to have seperate tables for all of the metafields. This would allow me to be very specific about the values that can be stored in that column etc. It also turns out that for the kind of queries I'm doing its about 4 times as fast. The only problem is that the software will need to create these tables at run time.
What I would like to know is, is this a really bad idea? If so why?. Also are there any other ways I can store this kind of data?
The kind of queries I am doing are find all the documents where metafield 1 is A and metafield 2 is B and metafield 3 is C and metafield 4 is D etc.
When I've deployed a report model,in Report Builder I have 4 different entities at the same level,when I drag and drop the first field from one entity after I can't see the other three entities....What did I do wrong?? I don't understand ,if you could help me it would be great.
I have a database that tracks players for children's sports clubs. I haveincluded representative DDL for this database at the end of this post.A single instance of this database supports multiple clubs. I would like toadd support for letting each club define and store custom information aboutarbitrary entities. Basically, allows the clubs to define custom entities(i.e tables) and associated custom attributes (i.e. fields) that may berelated to existing tables (such as Player and FootballClub) or existingentities. For instance, a club may define a PlayerAssessment entity thatrecords all player assessments.To do this, I plan to support the following use case:1. FootballClub admin creates a new entity and gives it a name anddescription (Entity is only accessible to this FootballClub).2. FootballClub admin indicates that the new entity has a M:1 relationshipwith the Player table (this will add Player_ID as a FK attribute).- {An entity may have no relationships.}- {Relationships are also supported to other entities.}3. FootballClub admin specifies the names and domain/types of any dataattributes (i.e. fields) of the entity.- {An attribute's type may be constrained to a few allowable types likeRelationship, Integer, Float, Currency, Date, Time, DateTime, Name,Description and Memo.}4. System creates entity as specified.A few constraints:1. Any entity defined is "private" to the defining club. Other clubs aren'taware of it although they may define custom entities of their ownwith the same name and attributes. [Perhaps there is a way to sharedefinitions of identical entities?]2. A club doesn't have to define any custom entities.Ideas I've considered:1. Generate DLL and create actual tables- Restrict such customizations such that while admin is setting up entities,no other user is allowed to use the system.- Once entity definition is complete, generate an actual table using DLL.Table and column names might be changed to enforce uniqueness/validityconstraints - this suggests a need for table/column name mapping.- PROS: Easy to implement.- CONS: Doesn't scale since only a limited number of tables can be created.DDL on a live, shared system?. Scary!!All users for all clubs will be locked out while entity iscreated.2. Generate DDL and create actual tables in secondary database(s)- Same as above except that the user tables are created in secondary [,shared] databases.- PROS: Reassurance that DDL is never run on the "core" dataAll users don't have to be locked out.- CONS: Doesn't scale since only a limited number of tables can be created.{ Unless I start creating additional databases too!. }Still needs to DDL on a live, shared system.Has anyone done anything similar?. Any ideas on how it might be done?. Inparticular, is this possible without having to execute DDL on the livedatabase?Kunle=================== BEGIN DDL ===================CREATE TABLE FootballClub (Club_ID int IDENTITY,Name char(80) NOT NULL,Area char(4) NOT NULL,League char(4) NOT NULL,City char(30) NOT NULL,PRIMARY KEY (Club_ID))goexec sp_primarykey FootballClub,Club_IDgoCREATE TABLE Player (Player_ID int IDENTITY,First_Name char(30) NOT NULL,Initials char(30) NULL,Last_Name char(30) NOT NULL,Date_Of_Birth datetime NOT NULL,Position char(4) NULL,Club_ID int NULL,PRIMARY KEY (Player_ID),FOREIGN KEY (Club_ID)REFERENCES FootballClub)goexec sp_primarykey Player,Player_IDgoCREATE TABLE UserAccount (User_ID int IDENTITY,Club_ID int NOT NULL,FullName char(80) NOT NULL,Logon char(20) NOT NULL,PWD_Hash char(60) NOT NULL,PRIMARY KEY (User_ID, Club_ID),FOREIGN KEY (Club_ID)REFERENCES FootballClub)goexec sp_primarykey UserAccount,User_ID,Club_IDgoexec sp_foreignkey Player, FootballClub,Club_IDgoexec sp_foreignkey UserAccount, FootballClub,Club_IDgo=================== END DDL ===================
I have configured master data services in my DB server and when i am going to explorer the entities and respected data by using navigating as below
[URL] -->Click to Explorer I am able to select data for only one default entity, in entities drop down is getting hidden back side , please find below snap to clearance.
Note: I am using Internet explorer (11.0.9600.17239) to browse the solution.
I have scoured the internet looking for someone who has run into this issue with no such luck! Product: Reporting Services 2005 Analysis Services 2005
Our users use Report Builder as an ad-hoc report tool. Data sources for Report Builder come from Report Servers and Analysis Servers.
I have run into issues where a user will select a source for their report that is based on an AS cube. When they select an attribute (field) from a selected entity in the Report Builder Explorer and drop it on the canvas, all entities except the one the field was selected from disappear.
For example:
A cube has a fact table called "Annual Income" with dimension of employee and demographics. In Report Builder, the entities would be listed in the left hand Report Builder Explorer window as
Annual Income Employee Demographics
If I select attributes from Employee or Annual Income, as usual, you will see the related hierarchies of the remaining entities. However, if I select an attribute from Demographics, then the Annual Income and Employee entities just disappear and all I can see is the Demographics entity and its attributes.
Has anyone run into this and if so, any ideas? This is becoming extremely frustrating. I have deleted and rebuild cubes, individual dimensions, and everything else I can think of.
I have the following table:tblFriendsOwnerCode FriendCode7 107 1410 710 1210 1312 1013 1013 1814 718 13 I need a SP which return the following (im unsure about the best return datatype and the sql statement): I want return all friendcodes of user nr 7 (10 and 14)and I want to return all friendcodes of user 10 and 14 (7,12,13,7) WITHOUT user 7 (if possible WITHOUT the use of a temptable!)
As the page only has 21cm and the report grows horizontally, I need to repeat the WHOLE report with all of its sections after every 4 customers. So if I have 5 customers, there would be three pages(let's say every subreport takes 1 page) for the first four customers, and then another three pages(with every section once again) just for one customer.
Is it possible? It doesn't seem somehing out of the ordinary, but I can't figure how to make it work. Everytime there is more than 4 customers, the report breaks its layout trying to accommodate the records in a new table below the tables of each subreport.
The expected result would be to bring all of exceeding data to new pages.
I am trying the assign the models to the user in the user management page. But when I try to expand the + symbol next to the model and open the entities, I get the error that the object reference is not set for a object server instance.
I have roles set up for different companies. The role names are structured companyname_department, ex., CallawayContracting_SalesDepartment CallawayContracting_Administration FredsAutobody_PaintSales How would I search the roles and return only departments that belong to a certian company and also users that belong to a certian company. I appear to have gotten myself into quite a bind. Any help would be much appreciated! I will be certian to click best answer.
I'm pretty new to this so I'll explain as best I can.
I am building a small DB which will track attendance for employees based on a point system. I believe I'm almost there (with this piece) but am stuck.
The basic concept: 1. Collect all records from the attendance table for the previous 14 day period 2. sum the points column in the attendance table and group by UID, storing in a new table called TOTAL_POINTS ONLY for those UID's which have a value > 0 3. Perform a basic insert into statement on the attendance table for each UID matching those found in the previous TOTAL_POINTS table
Number 3 is where I'm failing and could really use some help.
My code thus far... -------------------------------
/*Declare local variables for current date and start date.*/ -- DECLARE @DateNow DATETIME DECLARE @StartDate DATETIME SET @DateNow=getdate() SET @StartDate = DATEADD(Day, -14, @DateNow) -- /*Create table to hold totals for future calculations*/ CREATE TABLE POINT_TOTALS (UID int, TOTAL float)
/*select ALL records from the table within the above defined date range and group them by UID tallying the score column*/ -- INSERT INTO POINT_TOTALS SELECT UID, SUM (POINTS) AS TOTAL_POINTS FROM attendance WHERE date >= @StartDate GROUP BY UID -- /*If the TOTAL_POINTS > 0 for the 14 day period, insert a record in to the attendance table which deducts .5 points for the UID in question*/
*** This is where I'm failing ***
--This was just to make sure I was returning the correct results to the POINTS_TOTAL table. SELECT UID FROM POINT_TOTALS WHERE TOTAL > 0
/*All I want to do now is for each of the UID's in the POINT_TOTALS table, I want to perform a basic insert on the ATTENDANCE table where the UID's in both match. I would think this to be fairly simple but I can't seem to figure it out. */
I have a table where i have 4 columns in it which i need to group together and then sum up a cost column also. I want to sum up the columns where i have a parent and and child and then i want to sum up the other column where i have only a child. Example of the data is below. I think i need to do this in a sub query
ID Ind Parent Child Cost P110041012705921.8000 W11004101270595.4500 A110041012705921.8000 B110041012705916.3500 R110041012705916.3500 B0100420043.3000 P0100420043.3000 W0100420021.6500
Hello !This is my table:Ordernr Date ArticleO1 1.1.05 22O2 2.2.05 33O3 5.5.05 22O4 2.2.05 33O7 8.8.05 55I need one result-row for each article with the newest Order(max(date)):article lastDate lastOrdernumber22 5.5.05 O333 2.2.05 O455 8.8.05 O7How can I get this ?I tried this:SELECT distinct article, max(date), max(ordernr)FROM tableGROUP BY articlearticle and max(date) is ok, but I am not sure that max(ordernr) andmax(date) comes from the same row.I think, I will need complex subqueries.Many thanksaaapaul
Hello !I habe 2 TablesTable1: OrdersFields: Ordernr, OpiecesTable2: CalloffsOrdernr, CpiecesIn Table1 ordernr is primary key.In Table2 the same ordernr can exist oftenMy problemIf the sum(Cpieces) < Opieces:I have to create a new virtual calloffwith Cpieces = opieces - sum(cpieces)Its too high for me.Please helpBest regardsaaapaul
HiI am trying to produce an update trigger. I understand the concept ofdelete and insert triggers without a problem. Unfortuantely, theupdate triggers do not have particularly simple documentation in BoL.So, can someone please explain to me, quite simply how I would producea trigger on the following:I have table 1 which we'll call simon. In here are various columns androws. I also have table 2, called simon_a, my audit table.Whenever anything is updated or deleted in simon, I want it sent tothe simon_a table. Delete, as above, is fine since it's conceptual buthelp me out on the update one. I cannot seem to figure out how to getthe information from the table before it's updated.As ever, champagne and beer for the successful answer.With thanksSimon
I have a table that stores billing rates for our employees by client.Each employee can have a different billing rate for each client for aspecified period. Here are the columns in the table.eid - Employee ID#cid - Client ID#startdt - start date of billing rateenddt - end date of billing ratebrate - billing rateI need to create a script that will verify that for a given eid, and cidthat either the startdt or enddt for one billing rate, the periods donot overlap.For example, I need to be able to detect overlaps such as this:eid cid startdt enddt brate001 001 1/1/2003 12/31/2003 $50001 001 11/01/2003 04/01/2004 $75*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
