Entity Relationship Management Idea
Please let me know what you think of this scheme I have come up with
(not to imply that noone else has before me) for managing
relationships.
I have created an entities table with Individual and Organizational
subtypes. I need to be able to relate them to eachother (e.g., some
are customers of or suppliers to others, some have employer/employee
relationships,...). I know this is not an uncommon thing to do.
So, I have created relationship pairs with left and right values. The
pairs are things like employee/employer, customer/vendor,
contractor/client,... Then I can create relationships that will let
me look in two directions. For example, if I say entity1 has an
employee/employer relationship with entity 2, that means that entity1
is an employee of entity2 and entity2 is an employer of entity1.
Entities (This is just a view of the combined ind/org subtypes tables)
EntityID EntityName
1 Doe, John
2 MyCorp, Inc.
3 Smith, Jane
4 AnotherCorp, Inc.
RelationshipTypes
RelTypeID RelLValue RelRValue
1 Employee Employer
2 Customer Vendor
3 Client Contractor
Relationships
RelTypeID LEntityID REntityID
1 1 2
4 2 2
2 3 3
Then I can query for everyone that has a relationship with a specific
entity by using:
SELECT Entities.EntityName, RelationshipTypes.RelLValue AS
Relationship FROM Relationships
LEFT JOIN Entities ON Entities.EntityID = Relationships.LEntityID
LEFT JOIN RelationshipTypesON Relationships.RelTypeID =
RelationshipTypes.RelTypeID
WHERE REntityID = 2
UNION
SELECT Entities.EntityName, RelationshipTypes.RelRValue AS
Relationship FROM Relationships
LEFT JOIN Entities ON Entities.EntityID = Relationships.REntityID
LEFT JOIN RelationshipTypesON Relationships.RelTypeID =
RelationshipTypes.RelTypeID
WHERE LEntityID = 2
Returns:
EntityName Relationship (to EntityID = 2)
Doe, John Employee
Smith, Jane Contractor
AnotherCorp, Inc. Customer
View Complete Forum Thread with Replies
Related Forum Messages:
Entity Relationship Diagram
I have VS 2005 and SQL Server 2005 Express installed and I created 4 tables and setup the primary and foreign keys and can view the individual foreign key relationships by right clicking on the foreign key. Is there a way to view a table relationship diagram (fields with primary and foreign keys), such as Access provides. Does VS 2005 or SQL Server 2005 have that capability? Thanks
View Replies !
Need Help On Entity-Relationship Design
I need to design a schema that will provide me a hierarchical view in reporting. I mean by this is, lets say: Hospital |_________> Hospital Entity | | | | | V | | Clinic A | | - Jane Doe MD | | - Janette Brown MD | | ACME Clinic | | - Jennifer Smith MD | | Clinic of Jennifer Smith MD | | - Jennifer Smith MD | | - Billy Johnson MD | | | V | - Earl Brown MD (Hospital Entity Resident Practitioner) | - Janette Brown MD V - John Smith MD - Bob Jones MD The design should also allow me to query a practitioner and the query will show all the entities he or she is a member of. If this is not the correct place to post this posting, please let me know the correct place and I will make the necessary changes. Thanks, Stephen
View Replies !
Entity Relationship Diagram
Entity Relationship diagram can anyone help me with my diagram pleasee..i have been banging my head on it for weeks. i have 6 entities Student, Course, Module, Attendance, and Result Polato Student Record System "The Polato university administration has decided to computerise their student record system. The system must be able to automatically print a new ID card for students on their admission and after completion of each academic year. The card should have the student’s name, number of years and the message welcome to 1st, 2nd or final year. In a bid to curb the rate of incompletion, the system should also be able to record attendance of students to any class. The system should be able to automatically send out a warning to any student that misses any two lectures for the first time. (Of course the student should come up with a very good reason for missing the lectures or else he/she will be removed from the module). Missing three or more lectures leads to automatic withdrawal from the module involved. A student will receive notice from the system, right after missing the second lecture. The system also must be able to calculate each student’s results for that semester only showing details of all modules taken. You should use the simple formula; the total of all the modules taken, divided by the number of modules" charles
View Replies !
How Can I Create A One-to-one Relationship In A SQL Server Management Studio Express Relationship Diagram?
How can I create a one-to-one relationship in a SQL Server Management Studio Express Relationship diagram? For example: I have 2 tables, tbl1 and tbl2. tbl1 has the following columns: id {uniqueidentifier} as PK name {nvarchar(50)} tbl2 has the following columns: id {uniqueidentifier} as PK name {nvarchar(50)} tbl1_id {uniqueidentifier} as FK linked to tbl1.id If I drag and drop the tbl1.id column to tbl2 I end up with a one-to-many relationship. How do I create a one-to-one relationship instead? mradlmaier
View Replies !
Reporting Off Of MS CRM Tying Notes Entity To Opportunity Entity
I am trying to create an opportunity report that includes notes from the notes entity. Unfortunatly, when I pull the fields from the notes entity into my reports, it seems there is no way to tie the notes to specific opportunities or accounts that they are associated with and I get all notes under the first item listed on my report. Is there a way to link data sets like Access lets you link tables?
View Replies !
Primary Entity Always Switched With Secondary Entity
Hi... I just tested to create a Report Model using SQL Server 2005 Reporting Services. But I found some problems when I tried to create a report using the Report Builder, that the entity (that was supposed to be a primary entity) always switched to secondary entity when I drag a field from other entity. For example: The entity that was supposed to be the primary one is the 'Sales Target per site' table. List of fields of 'Sales Target' table: - Site ID - Sales Target value The entity that was supposed to be the secondary one is 'Sales Order' table. List of fields of 'Sales Order' table: - Site ID - Sales Order Number - Sales Value First, I drag Site ID and Sales Target value from 'Sales Target per site' entity. Then, I drag the Sales Order Number from 'Sales Order' table, and......... 'Sales Target' entity switched to be a secondary entity, and 'Sales Order' become the primary one =( anybody help me to solve this problem please.... thanks, Reza
View Replies !
Relationship Management In SQL 2005 Express Edition
Hello everybody, I'm creating a database for my new web site. This database has around 9 tables and I would like to create the relationships for its tables. I know that in MS Access I can visually create the relationships, but after a log research about relationship management in SQL 2005 express edition, I'm not sure if this is possible in this environment. So, if I'm not able to visualy manage my relationships, do I have to create them by using only SQL? Thanks, Eduardo
View Replies !
Cannot Add An Entity That Already Exists.
Hello, Why do I receive this error "Cannot add an entity that already exists." I'm trying to add three data at one call using LINQ, after getting the error above then refreshing the page, only one data is inserted.... Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim db As personalDataContext = New personalDataContext Dim p As New personal For i = 0 To 3 - 1 p.name = "Mick" p.number = "01213" p.picture = "image/image.jpg" db.personals.InsertOnSubmit(p) db.SubmitChanges() Next End Sub... cheers,imperialx
View Replies !
Record (entity) Versions...
Database newbie question: My sample database (T-SQL syntax): CREATE DATABASE sample GO USE sample CREATE TABLE customers (CustomerId int IDENTITY(1,1) PRIMARY KEY CLUSTERED,name varchar(50),address varchar(50)) CREATE TABLE invoices (InvoiceId int IDENTITY(1,1) PRIMARY KEY CLUSTERED,date datetime,CustomerId int REFERENCES customers(CustomerId)) INSERT customers (name,address) VALUES ('First Company Ltd.','New York') INSERT customers (name,address) VALUES ('Second Company Ltd.','Washington') INSERT invoices (date,CustomerId) VALUES ('Jan 1, 2004',1) INSERT invoices (date,CustomerId) VALUES ('Jan 2, 2004',1) INSERT invoices (date,CustomerId) VALUES ('Jan 2, 2004',2) SELECT * FROM customers GO CustomerId name address 1 First Company Ltd. New York 2 Second Company Ltd. Washington SELECT i.date,c.name,c.address FROM invoices AS i INNER JOIN customers AS c ON i.CustomerId=c.CustomerId GO date name address 2004-01-01 00:00:00.000 First Company Ltd. New York 2004-01-02 00:00:00.000 First Company Ltd. New York 2004-01-02 00:00:00.000 Second Company Ltd. Washington UPDATE customers SET address='Boston' WHERE name='First Company Ltd.' GO INSERT invoices (date,CustomerId) VALUES ('Jan 3, 2004',1) SELECT * FROM customers GO CustomerId name address 1 First Company Ltd. Boston 2 Second Company Ltd. Washington SELECT i.date,c.name,c.address FROM invoices AS i INNER JOIN customers AS c ON i.CustomerId=c.CustomerId GO date name address 2004-01-01 00:00:00.000 First Company Ltd. Boston 2004-01-02 00:00:00.000 First Company Ltd. Boston 2004-01-02 00:00:00.000 Second Company Ltd. Washington 2004-01-03 00:00:00.000 Second Company Ltd. Boston Is it possible in any of the RDBMS's to make this last query return the following result set? 2004-01-01 00:00:00.000 First Company Ltd. New York 2004-01-02 00:00:00.000 First Company Ltd. New York 2004-01-02 00:00:00.000 Second Company Ltd. Washington 2004-01-03 00:00:00.000 First Company Ltd. Boston
View Replies !
Lookup Entity Not Working
Hi, I have a table called Posts which contains an attribute Hospital ID. This is a foreign Key to the Hospitals table, which contains two fields : Name & Region. In my report model, I would like the Hospital ID to be replaced with just the Hospital name. I have set the Hospitals entity to IsLookup=True and the IdentifyingAttribute to Hospital ID. However, in the report builder Hospital ID is still being displayed with no sign of the hospital name. Can anyone point out where I'm going wrong? I have tried setting the Name field in Hospitals to Role or Merge as I read that using 'name' might cause some problems but that made no difference
View Replies !
Report Model Entity Descriptions
If I want to set a description for each column in a large model with many tables, it is a pretty intensive manual effort. Is there any way to autogenerate the enity descriptions from say the extended properties table designer description?
View Replies !
Entity Results Based On Parameter
In the report designer cant we createb report paramter to create an table using new named query. when I deploy the report model and when the power user try to access the entity it should prompt an prompt asking for which quarter information does he need. Thats is when an end user click on the entity it should prompt for paramter and based on the paramter the results of entity should be avaialble. In some cases based on user i want to limit the number of fields in an entity can an user see. Regards, Navin
View Replies !
REPORT Model - Missing Entity
Hi , I created 3 tables in SQL server magmt studio as student - sno (pk), sname subjects - subno(pk), subname marks - sno(fk), subno(fk), marks While creating the report model: 1.Created the data source - no problem 2. created the data source view - no problem added all 3 tables here. 3. created the report model - only subject and student show up here. Where is the marks table? Please help Regards, Reshma
View Replies !
Question Related To E-r Model And Entity
Hello to everyone! I would like to ask you sthg as far as the e-r model is concerned! I haven't started of course to use SQL yet,cause i have to make the e-r model first. I have a a question to make : For example i have to keep data for a Personal Computer (PC) So i have the entity : COMPUTER COMPUTER has many characteristics/fields like the primary key which is going to be PC_ID and it's unique for every computer,NAME_OF_PC the name and also TYPE_OF_MOTHERBOARD, TYPE_OF_RAM, TYPE_OF_HARD_DISK etc. I have to keep for the TYPE_OF_MOTHERBOARD the manufacturer, the motherboard's code etc. Shall i make an entity for the motherboard or for the HARD_DISK or for the RAM? Cause i want next to change these fields of every COMPUTER if it's n eeded! e.g. i have the PC_ID = 12 and i want to change the hard disk cause it's not working!(shall i keep an entity of the hard disk or not?) Can sm advise me what i shall do? Thanks, in advance! :)
View Replies !
Associative Entity - Combining Result
Hi, I have the following table (1) Item Table ItemID Name 1 Sample Item (2) Brand Table BrandID Name 1 Sample Brand - 1 2 Sample Brand - 2 (3) ItemBrand Table ItemID BrandID 1 1 1 2 Desired Output ItemID ItemName Brand1 Brand2 -------------------------------------------------------- 1 Sample Item Sample Brand - 1 Sample Brand - 2 How to get the desired output?
View Replies !
External Entity Handling Database Tables
Hi, I'm trying to find out whether it is possible to integrate an external entity into MS SQL Server in following way: the entity defines certain set of tables when user performs a query on one of these tables, the query is sent to the entity for processing and results are returned back to SQL server (and to client) I thought integration services could be used for this task, but I'm quite unfamiliar with MS SQL server, so I don't know whether it would be a feasible solution...
View Replies !
List CRM Entity Attributes And Data Types
Hi, I am using CRM 3.0 and have a requirement to list all the the tables, attributes names and display names and datatypes. Is there any easy way to export this information from the CRM tool or prepare a SQL query that will list the information?
View Replies !
Design To Accomodate Entity Based Schema Versioning
in simple words it's about versioning at record level.ExampleTableEmployee - EmployeeId, EmployeeName,EmployeeAddress, DepartmentId,TableDesignationMap - EmployeeId, DesignationId, EffectiveDate,validityTableDepartment - DepartmentId, DepartmentTableDesignation - DesignationId, designationVia Modify-Employee-Details screen following are editableEmoyeeNameEmployeeAddressDepartmentDesignationthis screen should allow user to navigate through changes history.Example :Version -1EmoyeeName John SmithEmployeeAddress 60 NewYorkDepartment AccountsDesignation AccountantVersion -2EmoyeeName John SmithEmployeeAddress 60 NewYorkDepartment AccountsDesignation Chief Accountant - changedVersion -3EmoyeeName John SmithEmployeeAddress 60 NewYorkDepartment Sales - changedDesignation Marketing Manager - changedQuestion :What is the best proposed database design for maintaining historyrecords bound with version and retrieval techniqueBest RegardsSasanka
View Replies !
Changing The Primary/base Entity On The Report Model
Let's say there is a relationship A 1--* B defined in a report model. When using the model either in Report Builder or while defining the dataset in Report Designer I find that if I mean for entity A to be at the root and I accidentally click on B's fields, B becomes the root entity. While I understand the reasoning behind this, I cannot find any way to get back to A as the base entity. Even if all B fields are removed from the report, B remains as the root. This is very frustrating as the entire report/dataset will need to be rebuilt from scratch. Any ideas?
View Replies !
VS 2005 REPORT MODEL MAXIMUM ENTITY SIZE
Hi, I'm opening a Report Model with 3mb size in a 256 mb memory and cannot load Report Model...Tried it on a 1.5 GIG memory and I was able to open Report Model. But we need to open a 14 mb Report Model. When we tried to open this in our 1.5 GIG memory, we were not able to do so...Does CPU memory have to do with the size of our Report Model in loading it?
View Replies !
Limit On Blob/image Data Type In SQL Compact 3.5 SP 1 BETA For ADO Entity Framework?
Hi! I tried to save some image data, but it get truncated at 8000 (the table column is defined as Image). I then wrote a converter to try ntext-datatype instead, but it gets truncated at 4000. Error message: System.Data.SqlServerCe: @3 : String truncation: max=4000, len=4168 The code uses only ADO entity framework for database access. Is there a way to store binary data larger than 8000 bytes? I am running SQL Compact 3.5 sp 1 BETA. Henning
View Replies !
Any Idea?
i have a table FORUM_REPLY it contain follwing fieldsquest_id,answer_id, reply_user_id.i want who is post most answer.so i need reply_user_id and max(no_of_answer).but following query given reply_user_id and no_of_answer onlySELECT reply_user_id,count(answer_id) as no_of_answer FROM FORUM_REPLY GROUP BY(reply_user_id)How to get max_no_of_answer?
View Replies !
Just An Idea
what if I took this trigger and based it on a view rather then a table CREATE TRIGGER TerminationUpdateTrigger ON EmployeeGamingLicense FOR UPDATE AS INSERT INTO TERMINATION(Status,[TM #],LastName, FirstName, [SocialSecurityNumber], DateHired, Title) SELECT STATUS, [TM#], LASTNAME, FIRSTNAME, [SSN#], HIREDATE, JOBTITLE FROM Inserted WHERE STATUS = 'TERMINATED' CREATE VIEW dbo.Update_Terminations AS SELECT STATUS, TM#, LASTNAME, FIRSTNAME, SSN#, HIREDATE, JOBTITLE FROM dbo.EmployeeGamingLicense WHERE (STATUS = N'TERMINATED') Base the Trigger on this View rather then on the Table itself????
View Replies !
Any Idea's On 605
When one of the users log into the database via a thrid party application they receive an error message: 2000/11/16 10:40:13.84spid51Error : 605, Severity: 21, State: 1 2000/11/16 10:40:13.84spid51Attempt to fetch logical page 7832 in database 'highview' belongs to object '1241055457', not to object 'application_data'. 2000/11/16 10:45:07.68spid46Getpage: bstat=0x1008/0, sstat=0x80002110, disk 2000/11/16 10:45:07.68spid46pageno is/should be:objid is/should be: 2000/11/16 10:45:07.68spid460x1e98(7832)0x49f900e1(1241055457) 2000/11/16 10:45:07.68spid460x1e98(7832)0x4810b86f(1209055343) 2000/11/16 10:45:07.68spid46... extent objid 0, mask 0/0, next/prev=0/0 2000/11/16 10:45:07.68spid46... retry bufget after purging bp 0x2da7e060 I tried running DBCC checkdb, newalloc,& checktable to fix the probelm and than droping the table and rebuilding it but not success, any suggestion are more than welcome.
View Replies !
Need An Idea How To....
If anybody can suggest the most efficient way to "page" the output from a big and wide table (about 7000000 records) in order to display on the web in the user requested sort order. Sort order could be on at least 7 different columns from 50 of returned. I need to find a solution to move to the next and previous page. Any Idea? Dim
View Replies !
Can A DTS Do This For Me , If So, Any Idea How?
Hi all, I am new to the DTS game - or at least to trying to do anything other than transfer rows between databases with identical structures. I now need to create a far more complex DTS package to transfer data between an SQL Server database (I am using SQL Server 7.0) and an Oracle database. I have no trouble in making the connections and performing simple DTS's beween the two. However, my current task is more complicated. Problem 1: In one database I would store fifty values as 5 five records each containing 10 values (i.e. has ten fields). However, in the other database these fifty values would all be stored as one record (i.e. one row with 50 fields). How could I go about creating a DTS to transfer this information? Problem 2: These values should only be transfered if certain conditions are met in an another unrelated table. For example, a flag in another table indicating that transfer of the said values should occur. Any help with either of these problems would be much appraciated. Thanks in advance, Ross
View Replies !
Do Anyone Have An Idea?
Hi there, I have number of tasks in my control flow most of them are execute sql task. I want to update one of the column in my table when anyone of the task in the control get fails? Please let me know if anyone have an idea how to do this. Thanks and Regards
View Replies !
Someone Have Any Idea About It?
I have a table call CLIENTE and another table call ENDERECO where the CLIENTE table has the FOREIGN KEY of the ENDERECO table. When I try INSERT COMMAND in C# this message is show. The instruction INSERT conflicted with a constraint of FOREIGN FUNDAMENTAL "FK_CLIENTE_ENDERECO." The conflict happened in the database "E:ARQUIVOS DE PROGRAMASMICROSOFT SQL SERVERMSSQL.1MSSQLDATALINETEC.MDF", table "dbo.I ADDRESS", column 'IDENDERECO.' The instruction was concluded. I am a "little" lost. Thanks.
View Replies !
Some Idea
Hi I'm new to sql and it would be great if someone could give some idea on how to do the following This is the relational model: Department(DeptNum, Descrip, Instname, DeptName, State, Postcode) Academic(AcNum, DeptNum, FamName, GiveName, Initials, Title) Paper(PaNum, Title) Author(PaNum, AcNum) Field(FieldNum, ID, Title) Interest(FieldNum, AcNum, Descrip) The question is :- Which academics have the largest number of fields of interests? Return their academic number, given name, family name, institution and total number of fields they are interested in. Your must use a subquery in the sense that you must use 2 sql statements and use any of the following to connect both:- 1)Exists 2)Not exists 3)IN
View Replies !
When Are Transactions A Bad Idea?
Lets say I have to insert 60,000 or so records into SQL Server from another data source using the sqlTransaction class. However if at any point an error should occur I would like to roll back any changes. Would a transaction be a bad idea when dealing with this many records? Thanks for any advice. SA
View Replies !
Need Good Idea
Hi guysWe have a following problem. For security reasons in each table in ourDB we have addition field which is calculated as hash value of allcolumns in particular row.Every time when some field in particular row is changed we create andcall select query from our application to obtain all fields for thisrow and then re-calculate and update the hash value again.Obviously such approach is very ineffective, the alternative is tocreate trigger on update event and then execute stored procedure whichwill re-calculate and update the hash value. The problem with thisapproach is that end user could then change the date in the tables andthen run this store procedure to adjust hash value.We are looking for some solution that could speed up the hash valueupdating without allowing authorized user to do itThanks in advance,Leon
View Replies !
An Interesting Idea...
Having no more experience than reading books online, here is an interesting idea I would like to run by you guys and you can let me know if it is feasible or tell me I need to put the crack pipe down... We are going to increase the number of disks in our SAN, and I was speaking with the SAN administrator and he mentioned the shuffling of logical drives to match the new space. He said he is going to have to go through quite a few combinations/permutations on figuring out the best configuration for what data goes on the old vs. new to get the optimal space. Is this something that can be modeled out? I can write something that recursively figures it out, but why not explore fun ideas with tools that may be able to do it? Thank you in advance, John Hennesey
View Replies !
Any Idea What This Means .....?
Hi, Does anyone have any idea waht this means: aspnet_wp!resourceutilities!5!07/11/2007-10:32:00:: i INFO: Reporting Services starting SKU: Developer aspnet_wp!resourceutilities!5!07/11/2007-10:32:00:: i INFO: Evaluation copy: 0 days left It is take from the ReportServer log file. Thanks in advance for any response. Jon
View Replies !
Designing Idea Please
Dear Friends,I'm a junior DBA, I've to prepare an online examination. for this, I've three categories. a)beginer level b)intermediate level c)expert level again here subjects are 6. like sqlserver,oracle,c#,vb.net,html,javascript. in these subjects, i've to select these three types of questions. now how can i design for this requirement? shall i create three tables for beginer, intermediate,expert or shall i create 6 tables and write according that? am i given correct inputs? please give me an idea to design thank you verymuch experts. Vinod
View Replies !
Can Anyone Explain To Me Why This Is Not A Good Idea
I have a complex select statement that is used in several stored procedures. I decided that instead of having x number of T-SQL scripts with the same exact select statement that I would to put this query into a view and then do a select * from View. Recently an instructor told me that this was a bad idea and that anyone who uses a select * from anything should be fired. When I asked for his reasoning his response was to say the least abnoxious. I can understand why a Select * from Table might be a bad idea as the table definition can change, but the chances of a view changing seems much less likely. Is a view a good idea in this case? Is the Select * from View really a bad idea? Thanks
View Replies !
New Idea To Use The EXCEPT Operator To Paging
Last night, I have this idea, but can not know if it can have a good performance and efficiency when the @CurrentPage is big.-------------------------------------------------------------------DATABASE:AdventureWorks DECLARE @CurrentPage intDECLARE @PageSize intDECLARE @OrderExpression nvarchar(100)DECLARE @Sql nvarchar(500)SET @CurrentPage = 1SET @PageSize = 10SET @OrderExpression = N' employeeid 'DECLARE @BigTop intDECLARE @SmallTop intSET @BigTop = @CurrentPage * @PageSizeSET @SmallTop = (@CurrentPage -1) * @PageSizeDECLARE @StartTime datetimeSET @StartTime = GETDATE()SET @Sql = N' SELECT TOP (' + CAST(@BigTop AS nvarchar(10)) + ') * FROM humanresources.Employee '+ ' EXCEPT '+ ' SELECT TOP (' + CAST(@SmallTop AS nvarchar(10)) + ') * FROM humanresources.Employee ORDER BY ' + @OrderExpressionEXEC sp_executesql @SqlDECLARE @EndTime datetimeSET @EndTime = GETDATE()SELECT DATEPART(s,@EndTime-@StartTime)SELECT DATEPART(ms,@EndTime-@StartTime)GO-----------------------------------------------------------------
View Replies !
Have An Idea But Not The Answer.... :( Needed Help
Hi, I have a table that has the ff: LastName varchar(50) FirstName varchar(50) PhotoPath varchar(50) Now I want to create a form that can accept the LastName,Firstname and also can upload a picture which in turn the filename of the image will be the value for the PhotoPath field, and eventually displays it using the repeater control. Your Help/Info. is highly appreciated... Jeff
View Replies !
Idea On Hardware Purchase
I am looking for a good reference on hardware specs for a dedicated SQL server. I don't want to talk to vendors, because I'm not looking to get snowed. Does anyone know of any resources? The server is to be a dedicated dataserver, for about 300 clients.
View Replies !
Concurrency (recap And Idea).....
With respect to my (now not so recent) thread on Concurrency, I would liketo run my idea past you gurus to see if its a runner. First, a brief recap:I have a single user system (one user, one copy of the software, one copy ofMSDE, one machine) that I wish to convert into a multi-user/single databasenetworked system. The problem I had was that a lot of information isfetched from the database and cached in the client program (the programimplements a tree structure, similar to a file system, and each of the nodesin the system has properties). The concurrency issue revolved around havingmultiple users updating these properties and possibly able to modify thetree structure and there being no way to notify the other clients that theyneed to refresh their data structures. Consider the system to be similar toVSS to look at (and in VSS, people can make modifications to the treestructure also!).Ok, one of the suggestions was time stamping each record. So, when one usermodifies the record, a second user can detect whether their timestamp isdifferent and thus whether or not their update is invalid (and also whetheror not the client program needs to refresh the properties of the givennode). How about instead of a timestamp I simply use a reference counter.ie. an integer that increments every time the record is modified (sameprinciple). I don't need to know when it was changed, just that the tworeference counters are different between when I fetched and when I amupdating the record.Secondly, I think I have to distinguish between a change in properties and achange in structure. For example, User A doesn't need to know about achange in properties for a node he is not currently looking at. However,that same user will want to be told about any change to the overall treestructure. So, I was thinking that any operations involving modificationsto the tree structure should set a "structure changed" flag in the database(increment a counter). After any operation is performed, the clientcompares its "changed" flag to the database value to see if it needs toreload the tree structure.Do you think this is workable?Thanks.Robin
View Replies !
Design Idea Help For Database
we are creating a database of sales agents. Basically I have a tableof about 35,000 people, a second one with 8000 offices, and a thirdtable of around 400,000 transactions done by those 35,000 people inthose 8000 offices. We get new data everyday that just updates theexisting tables with the updated rosters and transactions.I want to build a quick website where our recruiters can look up thosepeople and keep contact info and all that fun stuff but also pullnumbers on those people. Like* How Many Transactions that Sales Agent did last year* Rosters by office showing productionThe goal is to click the users name and see all the percentages,commissions and data like that which we will get by searching thattable of transactions by the agents ID.the problems I see right away are stuff like* If I were to pull a report showing all agents in a single office withtheir number of transactions next to their name, that is a HUGE query.It would have to search the 400k worth of records for each of theagents on just that one report.A suggestions I was givenI was told by a fellow programmer a better way to do this is to have anadditional table that houses stats info and have the SQL server runautomated reports everyday at say midnight where it updates that table.This table could show stuff like** number of transactions for each user** avg sales price on all transactions for each user** avg commission on transaction for each userlet me know your thoughtsthanks in advanceMonkey Girl
View Replies !
Any Idea Of This Replication Error?
Hi, all.. One database called POS in a server A is replicated with another server B with merge replication. Server A is distributor and publisher. I tried to replicate POS db it keeps giving me error following. "SQL Server Enterprise Manager could not retrieve information about database 'POS' Error 21776: [SQL-DMO] The name 'POS was not found in the ReplicationDatabase collection. If the name is a qualified name. user [] to separate various parts of the name, and try again." When other try it gives following error.. SQL Server Enterprise Manager could not enable database 'POS' for merge replication. Error 20736: [SQL-DMO] This object has been dropped from the server. I did all I can do.. I reinstalled sql server and tried again.. but it gives same error.. Does anyone has any idea of this problem???? Please post any idea. Thank you...
View Replies !
Is There Any Idea For The View In ERWin?
Hello, everyone: I know this post is on the wrong place, but I didn't know any ERWin forum. I set a data model project by ERWin 4, and import the database by Reverse Engineer. But there are not column names for all views, only view names are here. Any help will be great appreciated. ZYT
View Replies !
|
TRACKER
