Select command with multiple tables

[Shwetabh]

Hi,
I have two tables: Code and Color.
The create command for them is :

create table Color(
Partnum varchar(10),
Eng_Color char(10),
Span_Color char(20),
Frch_Color char(20),
CONSTRAINT pkPartnum PRIMARY KEY(Partnum)
)

create table Code
(
Partnum varchar(10),
Barcode varchar(11),
I2of5s varchar(13),
I2of5m varchar(13),
UPC varchar(11),
BigboxBCode varchar(11),
DrumBCode varchar(11),
TrayBCode varchar(11),
QtyBCode varchar(11),
CONSTRAINT fkPartnum FOREIGN KEY(Partnum) references Color(Partnum)
)
Now my question is,
how can i give a select statement such that I can get all the fields as
output.
Also plz note that the above is a sample. I have another 9 tables and I
need a solution
such that on being refered by Partnum, I can get all the attributes.

Thanks

[Erland Sommarskog]

Shwetabh (sh**********@g mail.com) writes:

I understand what you are saying. But consider this scenario:
A user needs a few more fields in the database and adds them to a table
kept for the purpose. Now how can _those_ fields be accessed without
using select *?

Changing the schema in a database is usually not something you do out of a
whim, and normally by a DBA, and not just any user.

And if a new field is added - how can you know off-hand that it is of
interest to you?
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Shwetabh]

Erland Sommarskog wrote:

Shwetabh (sh**********@g mail.com) writes:

I understand what you are saying. But consider this scenario:
A user needs a few more fields in the database and adds them to a table
kept for the purpose. Now how can _those_ fields be accessed without
using select *?

Changing the schema in a database is usually not something you do out of a
whim, and normally by a DBA, and not just any user.

And if a new field is added - how can you know off-hand that it is of
interest to you?
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Well, apparently, since the dataabse will be used for Label printing,
the DBA has to
change the schema by adding new fields according to the requirements.
Do you suggest instead of normalising the database I should create a
single table
which holds all the fields which will be used for label? Or is thr some
better way out.
Now, since I am converting legacy database, it should be possible to
add data to the table through a single query which I cannot really
foresee happening in a normalised database.
What do u suggest?

[Hugo Kornelis]

On 15 Feb 2006 20:43:49 -0800, Shwetabh wrote:

Why would it be necessary to use SELECT * in such a scenario? SELECT *
is slow because it requires extra work by the server to retrieve the
column metadata. It's unreliable because more code may break if and
when the table structure changes. It's hard to maintain because you
can't easily search for column dependencies in your code during
development.

(snip)
I understand what you are saying. But consider this scenario:
A user needs a few more fields in the database and adds them to a table
kept for the purpose. Now how can _those_ fields be accessed without
using select *?

Hi Shwetabh,

Easy.

In the parts of the application that have to use the new column, you can
add it to the column list in the SELECT statement at the same time
you're making the change to use the column.

And in the parts of the application that don't have to use the new
column, you don't have to change anything.

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

Shwetabh (sh**********@g mail.com) writes:

Well, apparently, since the dataabse will be used for Label printing,
the DBA has to change the schema by adding new fields according to the
requirements. Do you suggest instead of normalising the database I
should create a single table which holds all the fields which will be
used for label? Or is thr some better way out. Now, since I am
converting legacy database, it should be possible to add data to the
table through a single query which I cannot really foresee happening in
a normalised database.
What do u suggest?

Since I don't have the full story, but just some selected bits and pieces,
maybe I should not suggest anything.

Generally, SELECT * is bad. You talk about converting a legacy database,
but I don't know if that database is even SQL Server. And how your
question about relational database fits into this, I don't really see.

If you could give a more complete picture of what you are up to, it
might be easier to give better advice.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Shwetabh]

Erland Sommarskog wrote:

Shwetabh (sh**********@g mail.com) writes:

Well, apparently, since the dataabse will be used for Label printing,
the DBA has to change the schema by adding new fields according to the
requirements. Do you suggest instead of normalising the database I
should create a single table which holds all the fields which will be
used for label? Or is thr some better way out. Now, since I am
converting legacy database, it should be possible to add data to the
table through a single query which I cannot really foresee happening in
a normalised database.
What do u suggest?

Since I don't have the full story, but just some selected bits and pieces,
maybe I should not suggest anything.

Generally, SELECT * is bad. You talk about converting a legacy database,
but I don't know if that database is even SQL Server. And how your
question about relational database fits into this, I don't really see.

If you could give a more complete picture of what you are up to, it
might be easier to give better advice.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Ok,here are the details:

I am supposed to convert DBASE IV data to MS SQL server 2000 database.
Now, there are about 40 DBASE tables each of which contains fields
ranging
from 3 to 30. Most of the fields hold either NULL values or redundant
data. In all these tables and fields, there is only one common field
"Partnum".
Now earlier, this DBASE database was used by a labelling software
"Easylabel" . It used to
retrieve the neccessary data using the field "Partnum". This software
will connect to
SQL server and retrieve the data when conversion process is complete.

Now my dillema is that should I just create a database containing all
the fields in a single
table or should I try to create relationships among different data
objects. If I put them in a single table, will the system take a
performance hit when accessing data?
If I create relationships, how can I retrieve the data from tables
where information about new fields added by users without using select
*..?

I hope I made myself clear. If any doubts, plz let me know.

[Erland Sommarskog]

Shwetabh (sh**********@g mail.com) writes:

I am supposed to convert DBASE IV data to MS SQL server 2000 database.
Now, there are about 40 DBASE tables each of which contains fields
ranging from 3 to 30. Most of the fields hold either NULL values or
redundant data. In all these tables and fields, there is only one common
field "Partnum".
Now earlier, this DBASE database was used by a labelling software
"Easylabel" . It used to retrieve the neccessary data using the field
"Partnum". This software will connect to SQL server and retrieve the
data when conversion process is complete.

Now my dillema is that should I just create a database containing all
the fields in a single table or should I try to create relationships
among different data objects. If I put them in a single table, will the
system take a performance hit when accessing data? If I create
relationships, how can I retrieve the data from tables where information
about new fields added by users without using select *..?

So you are saying that the application will largely be unchanged, only
the database will change? That puts quite some restriction on the work.
It would of course be nicer to work with a clean sheet and do it right.

Since I don't know the application, it is difficult to say "do this"
or "do that". But I think that you design a solution with maintainability
and extensibilty foremost in mind. I don't think one single table is
the answer to that aim. But, again, I don't know the business domain.

As for performance, what data volumes are we talking about?
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Shwetabh]

Erland Sommarskog wrote:

Shwetabh (sh**********@g mail.com) writes:

I am supposed to convert DBASE IV data to MS SQL server 2000 database.
Now, there are about 40 DBASE tables each of which contains fields
ranging from 3 to 30. Most of the fields hold either NULL values or
redundant data. In all these tables and fields, there is only one common
field "Partnum".
Now earlier, this DBASE database was used by a labelling software
"Easylabel" . It used to retrieve the neccessary data using the field
"Partnum". This software will connect to SQL server and retrieve the
data when conversion process is complete.

Now my dillema is that should I just create a database containing all
the fields in a single table or should I try to create relationships
among different data objects. If I put them in a single table, will the
system take a performance hit when accessing data? If I create
relationships, how can I retrieve the data from tables where information
about new fields added by users without using select *..?

So you are saying that the application will largely be unchanged, only
the database will change? That puts quite some restriction on the work.
It would of course be nicer to work with a clean sheet and do it right.

Since I don't know the application, it is difficult to say "do this"
or "do that". But I think that you design a solution with maintainability
and extensibilty foremost in mind. I don't think one single table is
the answer to that aim. But, again, I don't know the business domain.

As for performance, what data volumes are we talking about?
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

I am working on a software to manage the labelling database. The DBASE
IV database tables are to be converted to MS SQL server database. Each
table consists of around 45 to 60 records.There is only one field which
is common in all tables and is unique. I will be using that field as
the primary key. Would I be wrong if I create a database in SQL and add
all DBASE IV tables according to the names of the files they are stored
in?
I can send you the sample database if needed.

As for extensibility, since the database will be stored in seperate
tables in SQL server database, I think there should be no problem. Am I
correct in my assumption?
Awaiting your replies,
Thanks

[David Portas]

Shwetabh wrote:

I am working on a software to manage the labelling database. The DBASE
IV database tables are to be converted to MS SQL server database. Each
table consists of around 45 to 60 records.There is only one field which
is common in all tables and is unique. I will be using that field as
the primary key. Would I be wrong if I create a database in SQL and add
all DBASE IV tables according to the names of the files they are stored
in?
I can send you the sample database if needed.

As for extensibility, since the database will be stored in seperate
tables in SQL server database, I think there should be no problem. Am I
correct in my assumption?
Awaiting your replies,
Thanks

You don't need to worry about performance. The database schema alone
isn't going to make a measurable difference to performance if your
tables average just 45-60 rows. Not unless they are likely to grow 100s
or 1000s of times bigger in the lifetime of the system.

Design a schema that will preserve data integrity and be easy to
maintain in future. Usually that means the schema should be fully
normalized but if this is just a one-off, low-value project you may
prefer to mimic the existing structure in SQL in order to minimise
changes to the application.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[Erland Sommarskog]

Shwetabh (sh**********@g mail.com) writes:

I am working on a software to manage the labelling database. The DBASE
IV database tables are to be converted to MS SQL server database. Each
table consists of around 45 to 60 records.There is only one field which
is common in all tables and is unique. I will be using that field as
the primary key. Would I be wrong if I create a database in SQL and add
all DBASE IV tables according to the names of the files they are stored
in?
I can send you the sample database if needed.

As for extensibility, since the database will be stored in seperate
tables in SQL server database, I think there should be no problem. Am I
correct in my assumption?

As David said, with those volumes there is no reason to consider a "smart"
design for performance. As I said, focus on maintainability and
extensibility. And ease of development. That may be a single-table
design, but most likely it's a more normalised design.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Doug]

as a place to start, create a sql table for every dbf file. copy the
column names verbatim.

then use the import function to import the data.

no matter WHAT you eventually end up with, you will have to do this to
get all the data in.

from there, you will have learned a lot, and will think of ways to
change the app to make it better.

but, to get all the data in, you have to do the first step.