sql server programming: [quoted text, click to view]

So why not store the data in that form in the first place? The design you
have proposed is contrary to sound relational design and is very
impractical. You have no easy way to enforce data integrity, you lose the
benefits of indexing the attributes individually and it requires a lot of
wasted effort to then extract the data in the format you want.

If you really think you have a compelling reason for your design then see
this article on how to produce crosstab reports: http://www.aspfaq.com/2462

--
David Portas
SQL Server MVP
--

Hi All,
Thank you for the support. Here I've learned many things. Thank you very
much!
I'd like to ask you something... I'd like to CREATE VIEW among these tables:

UsersTable
------------
UserID(PK) | UserName | Password | Name | Addr | E-mail | UsrType | Role |
LastLogOn

EstatesTbale
-----------
EstateID(PK) City Phone Heating Furniture TypeEstate(FK) TypeOffr(FK)
TypeConstr(FK)

OfferTable
------------
EstateID(FK) UserID(FK) Value ParamID(FK)

Param Table
--------------
ParamID(PK) Param
1 price
2 floors
3 square meters
4 rooms

As a result from this statement:

select a.UserName,a.Name,b.TypeEstate,b.City,c.ParamID
from UsersTable a INNER JOIN OfferTable c ON a.UserID=c.UserID
INNER JOIN EstatesTable b
ON b.EstateID=c.EstateID
I receive as many rows as parameters have the Offer for the exact estate
from the exact user.I'd like to make an Select that returns one row with
a.Username,a.Name,b.TypeEstate,b.City,Price,Floors,Square meters,Rooms and
if there is no parameter floors for some estate the value must be NULL in
the table. Do you know howcould be done this thing?Would you help me,
please!

Thank you in advance!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.

Thank you for the reply, David!
My DB looks like that, because I have one more table :

SearchTable
-----------------
EstateID(FK) UserID(FK) MinValue MaxValue ParamID(FK)

and the others:

UsersTable
------------
UserID(PK) | UserName | Password | Name | Addr | E-mail | UsrType | Role |
LastLogOn

EstatesTbale
-----------
EstateID(PK) City Phone Heating Furniture TypeEstate(FK) TypeOffr(FK)
TypeConstr(FK)

OfferTable
------------
EstateID(FK) UserID(FK) Value ParamID(FK)

Param Table
--------------
ParamID(PK) Param
1 price
2 floors
3 square meters
4 rooms

I use OfferTable to store the advertisement from the users. In the
SearchTable I store the criterion of users who want to receive
advertisements automatically.
They store for price-> MinValue and MaxValue and for the others parameters
also. Then a (trigger or SP) will send to them advertisements which answers
to their wishes. Could you tell me if you know a better way to do that?
Could you tell me a better solution to this problem if I use table like
this:
a.Username,a.Name,b.TypeEstate,b.City,Price,Floors,Square meters,Rooms

Thank you in advance!

Viktor Popov


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.

Here's a (simplified) example. Where the Min or Max values are NULL they
will be ignored for the purposes of the search.

CREATE TABLE Offers (userid INTEGER PRIMARY KEY /* REFERENCES Users (userid)
*/, price INTEGER NOT NULL CHECK (price BETWEEN 1 AND 99999999), rooms
INTEGER NOT NULL CHECK (rooms BETWEEN 1 AND 100))

CREATE TABLE SearchOffers (userid INTEGER PRIMARY KEY /* REFERENCES Users
(userid) */, minprice INTEGER NULL, maxprice INTEGER NULL, CHECK
(minprice<=maxprice), minrooms INTEGER NULL, maxrooms INTEGER NULL, CHECK
(minrooms<=maxrooms))

SELECT O.userid, O.price, O.rooms
FROM Offers AS O, SearchOffers AS S
WHERE
O.price BETWEEN COALESCE(S.minprice,0) AND COALESCE(S.maxprice,99999999)
AND O.rooms BETWEEN COALESCE(S.minrooms,1) AND COALESCE(S.maxrooms,100)
AND S.userid = 1

--
David Portas
SQL Server MVP
--

[quoted text, click to view]
No problem. You would just get the extra rows returned in the query.

[quoted text, click to view]
That's what the COALESCE functions are for. If one of the parameters is NULL
then it is ignored.

[quoted text, click to view]

SELECT O.userid, E.estate_id, O.price, O.rooms
FROM Offers AS O, Estates AS E, SearchOffers AS S
WHERE
O.estate_id = E.estate_id
AND O.price BETWEEN COALESCE(S.minprice,0) AND
COALESCE(S.maxprice,99999999)
AND O.rooms BETWEEN COALESCE(S.minrooms,1) AND COALESCE(S.maxrooms,100)
AND S.userid = 1

--
David Portas
SQL Server MVP
--

[quoted text, click to view]
queries?

That's not the way to look at it. The correct use of NULLs is to represent
missing or inapplicable data. In this case that's exactly what you have in
your search table. It is a good idea to keep nullable columns to a minimum
but not at the expense of good design and your original proposal was not a
good design (IMO) for the reasons I gave.

If you want to avoid NULLs completely then make all the columns non-nullable
and use high and low range values (0 and 99999999 for example) to represent
the missing data. Just remove COALESCE from my query:

SELECT O.userid, E.estate_id, O.price, O.rooms
FROM Offers AS O, Estates AS E, SearchOffers AS S
WHERE
O.estate_id = E.estate_id
AND O.price BETWEEN S.minprice AND S.maxprice
AND O.rooms BETWEEN S.minrooms AND S.maxrooms
AND S.userid = 1

--
David Portas
SQL Server MVP
--

Glad I helped. I'd be happy to help you out again but I'd rather do so in
the news groups. I can't always give prompt answers to email requests for
help. In these groups there are many experienced people who are willing to
provide support so please do post here again if you need more advice.

--
David Portas
SQL Server MVP
--

Thank again, David!
What about if the user have more than one Advertisement and if he/she search
only by max price and min rooms or minprice and max rooms?And how I have to
connect the advertisement(EstateTable) with the user and the SearchTable or
OfferTable?

Thank again, David!
What about if the user have more than one Advertisement and if he/she search
only by max price and min rooms or minprice and max rooms?And how I have to
connect the advertisement(EstateTable) with the user and the SearchTable or
OfferTable?

Thank you for the reply, David!
So you mean that it's better when there isn't table with parameters(price,
floor, square meters,rooms)? I have constructed the DB because I want to
avoid the NULL values when someone doesn't want to input in SearchTable for
example "min rooms" and "max rooms" value or "min floor" and "max floor".
What do you mean? Is it better to have NULL values than complicated queries?

Thank you in advance!

Viktor


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.

Thank you very much once again, David!
I understood what you mean:)
This is my final project at the Technical University, Plovdiv, Bulgaria. I
use MS SQL/ASP.NET/C# for implementing it.I'd like to ask you something. I
hope that you'll agree with that. Could be my consultant?
Thank you once again!

Viktor Popov


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.

Thank you once again, David!
Here it is my final DB. Could you take a look at it and tell me if I could
optimize it.

CREATE TABLE ESTATE.BLEK.Users
(
UserID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Username VARCHAR(20) NOT NULL UNIQUE(Username),
UserPass VARCHAR(20) NOT NULL,
RealName VARCHAR(64) NOT NULL,
UserAddr VARCHAR(74) NOT NULL,
UserEmail VARCHAR(64) NOT NULL,
UserType CHAR(1) NOT NULL, ----->agancy OR person
UserRole VARCHAR(6) NOT NULL, ----->Admin OR user
UserLLO SMALLDATETIME NOT NULL ------> LastLoginDate
)

CREATE TABLE ESTATE.BLEK.Est_Type
(
TypeID CHAR(3) PRIMARY KEY CLUSTERED, -----> is it OK if the Primary Key
is CHAR? I use char because the search is easier
TypeEst VARCHAR(16) NOT NULL ----> Type Estate: apartment, house,
office, field, etc
)

CREATE TABLE ESTATE.BLEK.TypeOffer
(
OfferID INT PRIMARY KEY CLUSTERED,
TypeOffer VARCHAR(14) NOT NULL -------> Type Offer : buy, sell, rent,
change, etc

)

CREATE TABLE ESTATE.BLEK.TypeConstruction
(
ConstrID CHAR(1) PRIMARY KEY CLUSTERED, ----> Again CHAR. Is it OK?
TypeConstr VARCHAR(14) NOT NULL ----> Type Construction: bricks, panel,
etc..
)

CREATE TABLE ESTATE.BLEK.Estates
(
EstateID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
EstCity VARCHAR(20) NOT NULL,
EstDistr VARCHAR(30),
EstPhone CHAR(1) NOT NULL CHECK(EstPhone IN('Y','N')), -----> If the
estate has phone line
EstFurn CHAR(1) NOT NULL CHECK(EstFurn IN('Y','N')), ------->If the
estate has furniture
EstHeating CHAR(1) NOT NULL CHECK(EstHeating IN('Y','N')), ------>If the
estate has heating
AdDate SMALLDATETIME NOT NULL,
EstTypeID CHAR(3) NOT NULL
FOREIGN KEY(EstTypeID) REFERENCES ESTATE.BLEK.Est_Type(TypeID),
TypeOfferID INT NOT NULL
FOREIGN KEY(TypeOfferID) REFERENCES ESTATE.BLEK.TypeOffer(OfferID),
TypeConstrID CHAR(1) NOT NULL
FOREIGN KEY(TypeConstrID) REFERENCES
ESTATE.BLEK.TypeConstruction(ConstrID)
)

CREATE TABLE ESTATE.BLEK.Est_Descriptions
(
DescrID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
EstateID INT NOT NULL
FOREIGN KEY (EstateID) REFERENCES ESTATE.BLEK.Estates(EstateID),
Descr VARCHAR(255) NOT NULL --------> If some one would like to post
any additional description of his/her estate. It is optional when add an ad
)

CREATE TABLE ESTATE.BLEK.Pictures
(
PictureID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
EstateID INT NOT NULL
FOREIGN KEY (EstateID) REFERENCES ESTATE.BLEK.Estates(EstateID),
PICEST VARCHAR(55) NOT NULL -------> The same story as the
description. I save the FilePath here
)



CREATE TABLE ESTATE.BLEK.Search
(
EstateID INT NOT NULL
FOREIGN KEY (EstateID) REFERENCES ESTATE.BLEK.Estates(EstateID),
UserID INT NOT NULL
FOREIGN KEY (UserID) REFERENCES ESTATE.BLEK.Users(UserID),
MinPrice INT NULL, --/-----> Your offer here
MaxPrice INT NULL,--/
MinRoom INT NULL,--/
MaxRoom INT NULL,--/
MinFloor INT NULL,--/
MaxFloor INT NULL,--/
MinSQM INT NULL,--/
MaxSQM INT NULL--/

)

CREATE TABLE ESTATE.BLEK.Offers
(
EstateID INT NOT NULL
FOREIGN KEY (EstateID) REFERENCES ESTATE.BLEK.Estates(EstateID),
UserID INT NOT NULL
FOREIGN KEY (UserID) REFERENCES ESTATE.BLEK.Users(UserID),
Price INT NOT NULL,
Rooms INT NULL,
SqMeters INT NOT NULL,
Floors INT NULL


)

CREATE TABLE ESTATE.BLEK.Messages ------> I use this table if someone want
so answer to someone's advertisements
(
MssgID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
EstateID INT NOT NULL
FOREIGN KEY (EstateID) REFERENCES ESTATE.BLEK.Estates(EstateID),
UserID INT NOT NULL
FOREIGN KEY (UserID) REFERENCES ESTATE.BLEK.Users(UserID),
MssgTxt VARCHAR(255) NOT NULL,
MssgDate SMALLDATETIME NOT NULL,
ParentKey INT
FOREIGN KEY (ParentKey) REFERENCES ESTATE.BLEK.Messages(MssgID)
)


What do you think about thi construction? Could tell me?

Thank you in advance !

Viktor


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.

Hi,

What do you thing? Is it better every tbale to have Primary Key or it
depends on something?
If you check the previous question with the tables you could see that I have
2 tables without Primary Key:
Offers and Search. Do I have to use Primary Key?

Thank you!

Viktor


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 16.7.2004 a.