Database

19-Jan-06 1:54

You have a couple of options.

One is to use triggers on the Person and Company tables to do brute force referential integrity on the EntityID table.

Another is to take your second idea, and just roll it into the Contract table itself. That is, create an FK field for Person, and an FK field for Company, in the Contract table. Yes, this is poor normalization. However, you aren't gaining anything by splitting this out into an Entity table, and you're just increasing the complexity.

Another is to create a pair of intermediary tables. Create a ContractPerson table, that handles contracts to people, and a ContractCompany table, that handles contracts to companies. You would need to add some logic, probably through triggers, to make sure that you don't have the same ContractID in both tables.

But, probably your best bet is actually to drop back a step further. What exactly is the practical difference between Persons and Companies? You may want to significantly expand your Entity table to actually be the core of the system. It would include any information that is common to any customer, such as address. You would add a field that indicates if the Entity is a Person, Company, or whatever other categories you might have. You would then make child tables that handle data unique to the type of Entity.

Re: SQL Table Design

Alexandru Serban19-Jan-06 2:26

Alexandru Serban

19-Jan-06 2:26

Another is to take your second idea, and just roll it into the Contract table itself. That is, create an FK field for Person, and an FK field for Company, in the Contract table. Yes, this is poor normalization. However, you aren't gaining anything by splitting this out into an Entity table, and you're just increasing the complexity.

Well, it is not just the Contracts tabel involved, there are also invoices and all kinds of tabels that store documents and other data that can be applyed to either a persion or a company. And in this case there are only two root tabels but one can imagine a situation that has any number. Adding columns for every one in all the related tabels is not a viable option.
The Entity tabel offers a level of abstraction and restricts modifications to a single tabel.

Another is to create a pair of intermediary tables. Create a ContractPerson table, that handles contracts to people, and a ContractCompany table, that handles contracts to companies. You would need to add some logic, probably through triggers, to make sure that you don't have the same ContractID in both tables.

Again, creating intermediary tabels for every posible case is not a viable option.

But, probably your best bet is actually to drop back a step further. What exactly is the practical difference between Persons and Companies? You may want to significantly expand your Entity table to actually be the core of the system. It would include any information that is common to any customer, such as address. You would add a field that indicates if the Entity is a Person, Company, or whatever other categories you might have. You would then make child tables that handle data unique to the type of Entity.

Yes, this is the ideea. Entity is the core of the sistem. It links for example the Address table with the specific Person or Company, as a person or a Company can have multiple addresses.

So far the best solution is the one I came up the second time, addind a new column in the Entity table for each new table. And not only that, the EntityID equals the value of the column that has the value set(in this case PersonID or CompanyID). That is important because on the data layer I can create a Dataset that has a relation between let's say Person and Address.

One is to use triggers on the Person and Company tables to do brute force referential integrity on the EntityID table.

I want to restrict as much as possible the use of triggers to enforce referential integrity.
What I was wandering if is there a way to create what would logically be a column with multiple FK's that at any time it will corespond with only one PK in the reffered tabels. In this case EntityID would either link to PersonID or CompanyID so later adding other tabels considered as "entities" would be very easy.

Re: SQL Table Design

Russell Nash19-Jan-06 2:56

19-Jan-06 2:56

I think you're making the problem a little more complicated than it needs to be. Check out one-to-one relationships. That should enable you to enforce referential integrity between the tables. The only thing you'd need to add logic to enforce is having a record in the Person table if and only if the EntityType is "Person." And similarly for each of the other entity types. Even that isn't entirely necessary, if you'd rather put logic into your views and stored procedures to make different joins based on the EntityType (a little tricky, but hardly impossible).

Generate Unique ID

mcgann18-Jan-06 6:53

mcgann

18-Jan-06 6:53

I'm tryin to create a unique id for each row in the database(SQL SERVER), I don't want to use the standard convention of uniqueidentifer. but to create my own which takes an abbreviation say 'pm' adds the year '06' and finally adds the next row number in sequence.
eg.

id = 'pm' + '06' + '0001'

Has anyone any ideas of how to do this. I have something working but it's not entirely correct.

gfhg

Re: Generate Unique ID

Farhan Noor Qureshi18-Jan-06 7:24

Farhan Noor Qureshi

18-Jan-06 7:24

You could use a combination key, of, say:

1. PK_ID int IDENTITY (1, 1) NOT NULL
2. Abbreviation_of_whatever varchar(10) NOT NULL
3. Year int NOT NULL

Unsure | :~

Farhan Noor Qureshi

^{if (this == this) thow this;}

ER Diagrams

kids1718-Jan-06 5:07

kids17

18-Jan-06 5:07

Hi, I'm not sure if this is the right medium but I have a question to ask. What are some of the tables you would include in an ER diagram for creating a database for a encyclopedia using SQL. I really would appreciate your help!

-- modified at 11:09 Wednesday 18th January, 2006

Re: ER Diagrams

Colin Angus Mackay18-Jan-06 5:28

Moving data between servers

18-Jan-06 5:28

kids17 wrote:
What are some of the tables you would include in an ER diagram for creating a database for a encyclopedia using SQL

Gosh! That's a bit vague.

Okay an encyclopaedia is made up of entries. An entry can have cross references (normally, these days it would be hyperlinks within the text), but lets say there is a See Also section at the end of each article rather than through the text.

So an Articles table, and a References table.

I can't think of much else because an encyclopaedia is a free flowing thing.

What do you think needs to be included?

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

-- modified at 11:29 Wednesday 18th January, 2006

Russell Nash18-Jan-06 2:54

Re: Moving data between servers

18-Jan-06 2:54

I'm having an issue that I need "best practice" advice on.

I need to collect data from one SQL Server, and insert it into another SQL Server. The problem is that the two databases cannot talk to each other. And, because I access both with different full-tunnel VPN's, I cannot talk to both of them at the same time.

So, my process is:
Connect to source VPN
Connect to source database
Pull data from view down to local PC
Disconnect from source database
Disconnect from source VPN
Connect to destination VPN
Connect to destination database
Upload data from local PC to destination database
Disconnect from destination database
Disconnect from destination VPN

If I leave the data in a dataset:
Will the data persist after the connection is closed?
Can I connect that dataset to either a new data adapter, or change the connection string of the data adapter?
Is it efficient to copy the data into a different dataset?

If I use XML:
How do I save the data from the XMLReader to a file?
Is this efficient?

Is there another option I'm missing, that would be more efficient?

Thanks for your help! I could make this go, but as the datasets are going to be quite large, I want to make sure I'm doing it in the best way.

PolarbearDK18-Jan-06 4:57

PolarbearDK

18-Jan-06 4:57

I've been thinking about making a utility for this:

I a previous thread I asked for feedback on it, but got no replies. Frown | :(

Re: Moving data between servers

Farhan Noor Qureshi18-Jan-06 7:34

Farhan Noor Qureshi

18-Jan-06 7:34

Russell Nash wrote:
If I leave the data in a dataset:
Will the data persist after the connection is closed?

Yes, if you use disconnected datasets.

Russell Nash wrote:
Can I connect that dataset to either a new data adapter, or change the connection string of the data adapter?

I am pretty sure it can be done.

Russell Nash wrote:
Is it efficient to copy the data into a different dataset?

By using DataSet.Copy()?

Russell Nash wrote:
How do I save the data from the XMLReader to a file?

To dump contents of Dataset to an XML file use DataSet.WriteXml().

Russell Nash wrote:
Is this efficient?

I guess.

Russell Nash wrote:
Is there another option I'm missing, that would be more efficient?

One option would be the plain old fashion way of doing stuff like that. Generate INSERT statements and using Bulk Insert. Very fast and easy.

Russell Nash wrote:
I could make this go, but as the datasets are going to be quite large, I want to make sure I'm doing it in the best way.

For larger data, I find BULK insert to be the most efficient way.

Farhan Noor Qureshi

^{if (this == this) thow this;}

query for unique customers

imsathy18-Jan-06 2:22

imsathy

18-Jan-06 2:22

i need a query to display unique customers on each of thier last purchase date alone segregating other dates in the same columns for the same customer

thanx in advance

sathy

Colin Angus Mackay18-Jan-06 2:50

18-Jan-06 2:50

If you want help on building a query then you need to tell us what the tables are, what columns are available, what the primary key(s) are on each table and their foreign key counterparts on other tables.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

imsathy18-Jan-06 3:17

imsathy

18-Jan-06 3:17

the table contains customerId(foreign key), dateOfPurchase, totalValue, shopId(primary key) these are the four columns to be dealth

i want to display unique customerId, dateOfPurchase(in specific the last purchase date of a customer), totalValue for the given shopId

select distinct customerId, max(dateOfPurchase)as date, totalValue from business_orders where shopId = '9F5DF' group by customerId

this query works when totalValue is not added in the query

sathy

Colin Angus Mackay18-Jan-06 4:00

18-Jan-06 4:00

imsathy wrote:
this query works when totalValue is not added in the query

That is correct, it must be in the GROUP BY clause or part of an aggregate. If you want the totalValue from the corresponding date you need to create a subquery.

SELECT customerId, [date], totalValue
FROM business_orders AS bo
INNER JOIN (
    SELECT customerId, max(dateOfPurchase) AS [date]
    FROM business_orders 
    WHERE shopId = '9F5DF' 
    GROUP BY customerId) AS ld ON 
        bo.customerId = ld.customerID AND 
        bo.[dateOfPurchase] = ld.[date]

I also removed the DISTINCT keyword as the GROUP BY effectively does that already and so it was redundant.

CAVEAT: If you have two or more orders on the same dateOfPurchace then you will get duplicates for an individual customer. There is nothing you can do about that as there is not enough date to determine which was really the last purchase.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

How to assign dataset in Reporting Services 2005?

imsathy18-Jan-06 18:32

imsathy

18-Jan-06 18:32

thanx a lot Colin

sathy

Diego F.17-Jan-06 22:48

Diego F.

17-Jan-06 22:48

I'm trying to integrate Reporting Services with an existing ASP.NET 2.0 web application. The goal is to click a button to call a report, but instead of just calling an existing report, to pass the dataset and create dinamically the report. If that's possible, users can create their own reports, by selecting the fields that will appear in the report.

Is that possible? Where can I find a way to achieve that?

Regards,

Diego F.

multiple update problem in datagrid

sood_is_in17-Jan-06 21:24

sood_is_in

17-Jan-06 21:24

I have a datagrid in which there are 2 columns
--First one is a template column which is having a textbox in its itemtemplate.
--secound one is a bound column and is picking up some unique values from database

Now on the page load i am providing the datagrid its datasource and binding it then. after i bind the data, the text boxes will have some values which is the first column and the secound column will be having some unique value now what i want is when i click on a button(not in the grid anywhere on the page) i want to update all those records for which the value in the text box(in the datagrid) has been changed and i want to update all the changes in the database in one trip back to the server.

Help Me!!!

Re: multiple update problem in datagrid

Alexandru Serban18-Jan-06 10:59

Alexandru Serban

18-Jan-06 10:59

You are very ambiguous in describing your "problem" but this is a simple matter of getting your hands on decent ASP.NET book that covers databinding in Webforms.

Need Help With Creating an Easy Code.

CoMMyz17-Jan-06 21:19

CoMMyz

17-Jan-06 21:19

Hi,

Im using Win2000 SP4 with SQL Server 2000 SP4

Basically i need some help with the following.

I have 2 Tables: Called Member and MemberTable
In Member table a column in there stores Names (Called Names) of the members and also in MemberTable there is another column called Names (Called Names) Which stores Names also of members.

I want to erase all rows in MemberTable that do not match those in Member (Case insensitive).

Can anyone provide a code? I tryied doing this myself and erased all at the end Frown | :(

Good thing i had a backup!

Please help.
Chris

Re: Need Help With Creating an Easy Code.

Russell Nash18-Jan-06 2:58

18-Jan-06 2:58

If I'm understanding you correctly...

DELETE FROM [MemberTable]
WHERE [Called Names] NOT IN (SELECT [Called Names] FROM [Called Member])

HTH

NT User List

Bob Zagars17-Jan-06 20:08

Bob Zagars

17-Jan-06 20:08

Fellow Professionals,

Is there anyway to get the NT users associated with the server in SQL Server 2000? I know xp_enumgroups gets a list of NT Groups but I can not find a stored procedure that returns that information. Does SQL 2005 have it? My other alternative is to write a .NET object and obtain this information from the local network.

Well can anyone help me? Please let me know.

Best Regards,

Bob Zagars

Colin Angus Mackay17-Jan-06 20:54

17-Jan-06 20:54

Bob Zagars wrote:
Is there anyway to get the NT users associated with the server in SQL Server 2000?

SELECT name 
FROM master..syslogins
WHERE IsNTUser = 1

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Bob Zagars17-Jan-06 23:52

Bob Zagars

17-Jan-06 23:52

That gives me registered users what about user not yet regisered. Stored procedure:

xp_enumgroups
Provides a list of local Microsoft® Windows NT® groups or a list of global groups defined in a specified Windows NT domain.

Syntax
xp_enumgroups ['domain_name']

They do not have to be registered on the database just within a Windows NT domain.

Best Regards,

Bob Zagars

S Douglas18-Jan-06 0:40

S Douglas

18-Jan-06 0:40

Bob Zagars wrote:
They do not have to be registered on the database just within a Windows NT domain.

If they haven’t been registered in SQL Server then SQL server won’t know about them. You will need to interrogate the Domain it self, if it’s an Active Directory Domain (I think it’s called tree now) here is a vb script that lists all disabled users, a minor mod and it could dump out all users.

List All the Disabled User Accounts in Active Directory[^]

ZeePain! wrote:
This seems like one of those programs that started small, grew incrementally, building internal pressure, and finally barfed all over its source code sneakers. Or something.

thedailywtf.com[^]