Use market
GO
ALTER PROCEDURE offer_detail
@Offer_num bigint, @Reference varchar(50) output, 
@Company_State varchar(50) output, @Company_City varchar(100) output, 
@Company_name varchar(100) output, @Web_page varchar(100) output,
@Activity_description varchar(500) output, 
@TheDate datetime output,
@Family_prod varchar(50) output, @Product_name varchar(100) output, 
@Other_fam varchar(50) output, @Other_prod varchar(50) output,
@Variety varchar(50) output, @Trade_name varchar(50) output, 
@Offer_title varchar(100) output, 
@State_place varchar(50) output, @City_place varchar(100) output, 
@day char(2) output,
@month char(2) output, @year char(4) output,
@State_origin varchar(50) output, @City_origin varchar(100) output,
@Production_type varchar(50) output, @logo_cert_ecol varchar(256),
@logo_cert_pi varchar(256), 
@DOP_IGP varchar(50), @DOP_IGP_name varchar(100), @DOP_IGP_logo varchar(256),
@Private_cerificate varchar(50), @CertAut_name varchar(100), @logo_CertAut varchar(256),
@CertMana_name1 varchar(50), @CertMana_name2 varchar(50), @CertMana_name3 varchar(50), 
@CertMana_name4 varchar(50), @CertMana_name5 varchar(50), @CertMana_name6 varchar(50), 
@CertMana_name7 varchar(50), @Others_cert varchar(150)     
As

SET NOCOUNT ON 
Select @Reference = Reference, 
@Company_State = state_user.State_name,
@Company_City = city_user.City_name,
@Company_name = user.Company_name, @Web_page = user.Web_page, 
@Activity_description = user.Activity_description,
@TheDate = TheDate,
@Family_prod = fam.Family_name, @Product_name = prod.Product_name,
@Other_fam = Other_families, @Other_prod = Other_products, @Variety = Variety,
@Trade_name = Trade_name, @Offer_title = Offer_title, 
@State_place = state_prod.State_name, @City_place = city_prod.City_name,
@day = day,
@month = month, @year = year,
@State_origin = state_prodOr.State_name, @City_origin = city_prodOr.City_name,
@Production_type = p_type.Production_type_name, 
@logo_cert_ecol  = CASE
WHEN offe.Production_type_num = 1 THEN path_ec.Directory_path + prod_ecol.Certificate_logo ELSE NULL END,
@logo_cert_pi = CASE
WHEN offe.Production_type_num = 2 THEN path_int.Directory_path + prod_int.Certificate_logo ELSE NULL END,
@DOP_IGP = DI.DOP_IGP_name, @DOP_IGP_name = DI_prod.DOP_IGP_name,
@DOP_IGP_logo = path_DI.Directory_path + DI_prod.Logo, 
@Private_cerificate = Private_cerificate, @CertAut_name = certAut.Quality_certificate_name,
@logo_CertAut = path_cAut.Directory_path + certAut.Logo, 
@CertMana_name1 = certMana_name1.Management_cert_name,
@CertMana_name2 = certMana_name2.Management_cert_name,
@CertMana_name3 = certMana_name3.Management_cert_name,
@CertMana_name4 = certMana_name4.Management_cert_name,
@CertMana_name5 = certMana_name5.Management_cert_name,
@CertMana_name6 = certMana_name6.Management_cert_name,
@CertMana_name7 = certMana_name7.Management_cert_name, 
@Others_cert = certMana.Others_Certificates          

From Offers As offe 	

JOIN Users As user
On offe.User_num = user.User_id
JOIN Europe_cities As city_user
On user.City_num = city_user.City_id
JOIN States As state_user
On state_user.State_id = city_user.State_num  

JOIN Products_names As prod
On offe.Product_num = prod.Producto_Id
JOIN Families_products As fam
On prod.Family_num = fam.Family_Id

JOIN Europe_cities As city_prod
On offe.place_City_num = city_prod.City_id
JOIN States As state_prod
On state_prod.State_id = city_prod.State_num  

JOIN States As state_prodOr
On offe.origin_State_num = state_prodOr.State_id  
JOIN Europe_cities As city_prodOr
On offe.origin_City_num = city_prodOr.City_id

JOIN Production_type As p_type
On offe.Production_type_num = p_type.Production_type_id

JOIN Production_ec As prod_ecol
On prod_ecol.CCAA_num = state_prodOr.Comunity_num

JOIN Path_to_directories As path_ec
On prod_ecol.Directory_path_num = path_ec.Directory_path_id

JOIN Production_int As prod_int
On prod_int.CCAA_num = state_prodOr.Comunity_num

JOIN Path_to_directories As path_int
On prod_int.Directory_path_num = path_int.Directory_path_id

JOIN DOP_IGP_Product As DI_prod
On offe.DOP_IGP = DI_prod.DOP_IGP_Product_id 

JOIN DOP_IGP As DI
On DI.DOP_IGP_id = DI_prod.DOP_IGP_num  

JOIN Path_to_directories As path_DI
On DI_prod.Directory_path_num = path_DI.Directory_path_id

JOIN Certificates_quality As certAut
On offe.Certificate_aut = certAut.Certificate_quality_id

JOIN Path_to_directories As path_cAut
On certAut.Directory_path_num = path_cAut.Directory_path_id


JOIN CertMana_Offers As certMana
On offe.Offer_id = certMana.Offer_num

JOIN Certificates_management_Type As certMana_name1 
On certMana.Certificate_Mana1 = certMana_name1.Certificate_management_id
JOIN Certificates_management_Type As certMana_name2 
On certMana.Certificate_Mana2 = certMana_name2.Certificate_management_id
JOIN Certificates_management_Type As certMana_name3 
On certMana.Certificate_Mana3 = certMana_name3.Certificate_management_id
JOIN Certificates_management_Type As certMana_name4 
On certMana.Certificate_Mana4 = certMana_name4.Certificate_management_id
JOIN Certificates_management_Type As certMana_name5 
On certMana.Certificate_Mana5 = certMana_name5.Certificate_management_id
JOIN Certificates_management_Type As certMana_name6 
On certMana.Certificate_Mana6 = certMana_name6.Certificate_management_id
JOIN Certificates_management_Type As certMana_name7 
On certMana.Certificate_Mana7 = certMana_name7.Certificate_management_id

Where Offer_id = @Offer_num

SET NOCOUNT OFF 
GO

quote:

---

BUT...to join 28 tables together, no matter how effecient just doesn't seem right.

---

quote:

---

It also looks like you could cram these tables
@CertMana_name1 = certMana_name1.Management_cert_name,
@CertMana_name2 = certMana_name2.Management_cert_name,
@CertMana_name3 = certMana_name3.Management_cert_name,
...
into one table. I would suggest rethinking your database.

---

Cert_manaOffers_id smallint
Offer_num bigint
Certificate_Mana1 smallint (FOREIGN KEY = Certificate_management_id)
Certificate_Mana2 smallint (FOREIGN KEY = Certificate_management_id)
Certificate_Mana3 smallint (FOREIGN KEY = Certificate_management_id)
Certificate_Mana4 smallint (FOREIGN KEY = Certificate_management_id)
Certificate_Mana5 smallint (FOREIGN KEY = Certificate_management_id) 
Certificate_Mana6 smallint (FOREIGN KEY =  Certificate_management_id)
Certificate_Mana7 smallint (FOREIGN KEY = Certificate_management_id)
Others_Certificates varchar(100)

Certificate_management_id smallint
Certificate_mana_name varchar(50)

quote:

---

But if you have a table with 200 fields, and there are 95 FOREIGN KEY‘s (fields based on the primary keys of other tables), how you can do a complete query of that table without joining a lot of tables?

---

quote:

---

Originally posted by cesark

According BOL Sql Server can have 2.000 millions of tables, 1.024 columns (fields) per table, and 8.060 bytes per row. My table have 160 fields (only a 16% of the maximum allowed capacity).

Correct me if this information is wrong, but I think that a table with 160-200 fields is totally normal. Then perhaps the question is: How to deal with such tables to make queries?

---

Cert_manaOffers_id smallint
Offer_num bigint
Certificate_Mana1 smallint (FOREIGN KEY = Certificate_management_id)
Certificate_Mana2 smallint (FOREIGN KEY = Certificate_management_id)
Certificate_Mana3 smallint (FOREIGN KEY = Certificate_management_id)
Certificate_Mana4 smallint (FOREIGN KEY = Certificate_management_id)
Certificate_Mana5 smallint (FOREIGN KEY = Certificate_management_id) 
Certificate_Mana6 smallint (FOREIGN KEY =  Certificate_management_id)
Certificate_Mana7 smallint (FOREIGN KEY = Certificate_management_id)
Others_Certificates varchar(100)    

Cert_manaOffers_id = 267
Offer_num = 258
Certificate_Mana1 = 2
Certificate_Mana2  = 4
Certificate_Mana3 = 5 
Certificate_Mana4  = 7
Certificate_Mana5 = null 
Certificate_Mana6 = null 
Certificate_Mana7 = null
Others_Certificates = null

Certificate_Mana1 smallint (FOREIGN KEY = Certificate_management_id)
Certificate_Mana2 smallint (FOREIGN KEY = Certificate_management_id)
Certificate_Mana3 smallint (FOREIGN KEY = Certificate_management_id)
Certificate_Mana4 smallint (FOREIGN KEY = Certificate_management_id)
Certificate_Mana5 smallint (FOREIGN KEY = Certificate_management_id) 
Certificate_Mana6 smallint (FOREIGN KEY =  Certificate_management_id)
Certificate_Mana7 smallint (FOREIGN KEY = Certificate_management_id)
Others_Certificates

Select
	OfferID
from
	Offers
where
	Certificate_Mana1 = 20	or
	Certificate_Mana2 = 20	or
	Certificate_Mana3 = 20	or
	Certificate_Mana4 = 20	or
	Certificate_Mana5 = 20	or
	Certificate_Mana6 = 20	or
	Certificate_Mana7 = 20	or
	-- Assuming Others_Certificates doesn't contain
	-- spaces or anthing but numbers and commas
	-- If they do, it just gets worse.
	Others_Certificates like '20'		or
	Others_Certificates like '20,%'		or
	Others_Certificates like '%,20,%'	or
	Others_Certificates like '%,20'

Select
	OfferID
from
	Offers
where
	(
	Certificate_Mana1 = 20	or
	Certificate_Mana2 = 20	or
	Certificate_Mana3 = 20	or
	Certificate_Mana4 = 20	or
	Certificate_Mana5 = 20	or
	Certificate_Mana6 = 20	or
	Certificate_Mana7 = 20	or
	-- Assuming Others_Certificates doesn't contain
	-- spaces or anthing but numbers and commas
	Others_Certificates like '20'		or
	Others_Certificates like '20,%'		or
	Others_Certificates like '%,20,%'	or
	Others_Certificates like '%,20'
	)
	and
	( not (
	Certificate_Mana1 = 21	or
	Certificate_Mana2 = 21	or
	Certificate_Mana3 = 21	or
	Certificate_Mana4 = 21	or
	Certificate_Mana5 = 21	or
	Certificate_Mana6 = 21	or
	Certificate_Mana7 = 21	or
	-- Assuming Others_Certificates doesn't contain
	-- spaces or anthing but numbers and commas
	Others_Certificates like '21'	or
	Others_Certificates like '21,%'	or
	Others_Certificates like '%,21,%'	or
	Others_Certificates like '%,21'
	))

Select
	OfferID
from
	Offer_Certificates
where
	Certificate_management_id = 20
group by
	OfferID

Select
	OfferID
from
	Offer_Certificates
where
	Certificate_management_id = 20 and
	OfferID not in 
	(
	select
		OfferID
	from
		Offer_Certificates
	where
		Certificate_management_id = 21
	)
group by
	OfferID

Select
	OfferID
from
	Offer_Certificates
where
	Certificate_management_id between 1 and 20 and
	OfferID not in 
	(
	select
		OfferID
	from
		Offer_Certificates
	where
		Certificate_management_id >= 21
	)
group by
	OfferID

quote:

---

I think a simple illustration of why the denormalized design of the Offer table is bad will make it easier to see my point.

You have a Offers table with these columns, among others:

Certificate_Mana1 smallint (FOREIGN KEY = Certificate_management_id)
Certificate_Mana2 smallint (FOREIGN KEY = Certificate_management_id)
…

---

quote:

---

A product offered in an offer of the ‘Offers’ table can have as far as 7 certificates plus a text field to write other certificates. For example, the product 56 (a watch) can have the Certificates Quality_A, Quality_B, Quality_C, Quality_D, Quality_E, Quality_F and Quality_G (I mean that can have 0, 2, 4 or 7 of them). This is why I have the ‘CertMana_Offers’ table:

---

quote:

---

Originally posted by cesark

quote:

---

I think a simple illustration of why the denormalized design of the Offer table is bad will make it easier to see my point.

You have a Offers table with these columns, among others:

Certificate_Mana1 smallint (FOREIGN KEY = Certificate_management_id)
Certificate_Mana2 smallint (FOREIGN KEY = Certificate_management_id)
…

---

Where did you see that I have the Certificate_Mana1..7 columns in Offers table? I have it in a separate table with a FK reference.

As I said:

quote:

---

A product offered in an offer of the ‘Offers’ table can have as far as 7 certificates plus a text field to write other certificates. For example, the product 56 (a watch) can have the Certificates Quality_A, Quality_B, Quality_C, Quality_D, Quality_E, Quality_F and Quality_G (I mean that can have 0, 2, 4 or 7 of them). This is why I have the ‘CertMana_Offers’ table:

---

CertMana_Offers is a separate table joined with Offers table through a FK

The thing you don’ t understand is that in that table (CertMana_Offers) there are 8 columns, 7 of them with a Foreign Key to a table with the names of the certificates (Quality_A, Quality_B, Quality_C, Quality_D, Quality_E, Quality_F and Quality_G). This means that one row of CertMana_Offers can be:

Cert_manaOffers_id = 267
Offer_num = 258
Certificate_Mana1 = 2
Certificate_Mana2 = 4
Certificate_Mana3 = 5
Certificate_Mana4 = 7
Certificate_Mana5 = null
Certificate_Mana6 = null
Certificate_Mana7 = null
Others_Certificates = null


This means that Offer number 258 has the certificates (Quality_B, Quality_D, Quality_E and Quality_G).

And I repeat: This design is wrong? Or bad?

If not is bad, Are there something wrong in my initial SP (Query) posted?

---