Re: SQL Table Design - Database Discussion Boards

HI,

Im using Visual Studio Express 2005 to develop my application Im just trying to INSERT a MYSQL query into the database.Im using Managed and unmanaged C++ The following is my code:

MySqlConnection^ Mycon = gcnew MySqlConnection("Server=localhost;database=test;UserID=admin;Password=;Port=3306");
Mycon->Open();
MySqlCommand^ cmd = gcnew MySqlCommand("SELECT * FROM Patient", Mycon);

cmd = gcnew MySqlCommand("INSERT INTO patient(PID,Title,FirstName,MiddleName,Surname) VALUES ('+@txtPID+','"+cmbTitle+"','"+txtFname+"','"+txtMname+"','"+txtSurname+"')", Mycon);

I get the following error:

Error:
An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll

Additional information: Duplicate entry '0' for key 1

can someone pleassse help me!!!!

Many thanks
hbjs

makaveli

-- modified at 22:22 Wednesday 18th January, 2006

sorry i figured it out! Big Grin | :-D

makaveli

Is there a way to auto-connect to specified database engines when SQL Server Management Studio is started ?

...cmk

Save the whales - collect the whole set

Simpley register the server under the Database Engine folder in the Registered Servers window.

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

Hmmm, i had done that but the servers only show up in the Registered Servers window, not the Object Explorer window.

I still need to explicitly connect to the servers i want to view in the Object Explorer window.

What i want is for SQL Management Studio to start, not prompt me to connect to a server, and display a list of pre-defined servers in the Object Explorer window.

...cmk

Save the whales - collect the whole set

Hi guys (and girls)

I have the folowing situation, a Person table, a Company table and a Contract table.
I can make contracts with both persons and companies alike so in the Contract table must be a referece to either a person or a company. In this case there are only two tables that need to link to the Contract table, but lets suppose there can be any number.
My initial approach is to create another table let's say Entity that has a column named EntityID that is the PK. In the Person table there is a PersonID column that is a PK for the Person table and a FK from the EntityID in the Entity table. The same applies to the Company table having a CompanyID column that is the PK for the Company table and a FK from the EntityID in the Entity table. The ID columns are GUIDs.
I use the EntityID as a FK in the Contract table to link either Person or Company to a contract in a SupplierID and a ClientID column.
While this design works, there is a small problem: acidentally deleting either a person or a company can leave an orphaned EntityID with no corespondece to the Person or Company table wich will leave the contract without one of its parts.

Is there other database design appropriate for this situation that overcomes this issue using referential integrity?

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

Just came up with another ideea, but it involves creating in the Entity table a new column for each referenced table (in this case Person and Company). In this case PersonID and CompanyID columns will be added to the Entity table and they will be FK from the Person and the Company table, for each Entity row only one of them having a value. After that, the row's EntityID will be used in the Contract table.
This works but new columns have to be created for each referenced table.

Is there any other solution?

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.

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.

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

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

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;}

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

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

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.

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

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;}

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

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

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

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

thanx a lot Colin

sathy

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.

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

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.

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