A very large table design - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

A very large table design

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-05-05 : 07:27:11

I have a very large table which I want to ask if it is well designed. The table has 160 columns (fields) and this is because I allow to classify a quantity of a product in several categories.

For example, if the user shows the product A (for example 2500 Kg of Onions), he/she can fill ‘only’ these fields: Caliber, Measure_Caliber, Kind_Caliber, Ripening_grade, Available_quantity, Measure_quant, Kind_container, Quant_container, Measure_quantCont, Image_name, Transport_cost, Price and Other_data.

Or instead, can fill: Caliber, Measure_Caliber, Kind_Caliber, Image_name, Transport_cost, Quant_avail_MA, Measure_quant_MA, Kind_container_MA, Quant_container_MA, Measure_qCont_MA, Price_MA, Quant_avail_MM, Measure_quant_MM, Kind_container_MM, Quant_container_MM, Measure_qCont_MM, Price_MM, Quant_avail_MB, Measure_quant_MB, Kind_container_MB, Quant_container_MB, Measure_qCont_MB, Price_MB and Other_data.

MA is high ripening grade
MM is medium ripening grade
MB is low ripening grade

In the first case the user type a quantity of the offered product (Available_quantity), and in the second case can divide that quantity into three categories (MA, MM and MB). For example, in the first case the user could offer 450 Kg of apples (Available_quantity), and in the second case these 450 Kg could be divided in 150 Kg in (Quant_avail_MA), 150 Kg in (Quant_avail_MM) and 150 Kg in (Quant_avail_MB). And continue filling the rest of the necessary fields in every case.

But the user has other options. If she/he want to divide the offer of the product between Quality_A, Quality_M and Quality_B, she/he has to fill for every kind of quality the same fields mentioned before but for each quality. For example, if the user wants to distinguish his/her product by Quality_A:

The user can fill these fields:
Caliber_QA, Measure_Caliber_QA, Kind_Caliber_QA, Ripening_grade_QA, Available_quantity_QA, Measure_quant_QA, Kind_container_QA, Quant_container_QA, Measure_quantCont_QA, Image_name_QA, Transport_cost_QA, Price_QA and Other_data.

Or could fill:
Caliber_QA, Measure_Caliber_QA, Kind_Caliber_QA, Image_name_QA, Transport_cost_QA, Quant_avail_QA_MA, Measure_quant_QA_MA, Kind_container_QA_MA, Quant_container_QA_MA, Measure_qCont_QA_MA, Price_QA_MA, Quant_avail_QA_MM, Measure_quant_QA_MM, Kind_container_QA_MM, Quant_container_QA_MM, Measure_qCont_QA_MM, Price_QA_MM, Quant_avail_QA_MB, Measure_quant_QA_MB, Kind_container_QA_MB, Quant_container_QA_MB, Measure_qCont_QA_MB, Price_QA_MB and Other_data.

And the same with Quality_M (QM) and Quality_B (QB)

So, the result is a table with 160 fields to allow all the possibilities to classify the offered product in whatever combination (only QA, only QB, QA and QM, QA and QM and QB, etc..) And my question is:

Is this table too large? If so, which is the alternative?

A sample of the table:


CREATE TABLE [Offers] (
	[Offer_id] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Reference] [varchar] (50) COLLATE Modern_… NULL ,
	[User_num] [bigint] NULL ,
	[theDate] [datetime] NULL ,
	[Product_num] [smallint] NULL ,
	[Other_families] [varchar] (50) COLLATE Modern_… NULL ,
	[Other_products] [varchar] (50) COLLATE Modern_… NULL ,
	[Variety] [varchar] (50) COLLATE Modern_… NULL ,
	[Trade_name] [varchar] (50) COLLATE Modern_… NULL ,
	[Offer_title] [varchar] (100) COLLATE Modern_… NULL ,
	[place_City_num] [int] NULL ,
	[day] [char] (2) COLLATE Modern_… NULL ,
	[month] [char] (2) COLLATE Modern_.. NULL ,
	[year] [char] (4) COLLATE Modern_… NULL ,
	[origin_State_num] [smallint] NULL ,
	[origin_City_num] [int] NULL ,
	[Kind_production_num] [tinyint] NULL ,
	[DOP_IGP] [smallint] NULL ,
	[Certificate_private] [varchar] (50) COLLATE Modern_… NULL ,
	[Certificate_auto] [smallint] NULL ,
	[Other_Certificates] [varchar] (150) COLLATE Modern_… NULL ,


	[Caliber] [numeric](6, 2) NULL ,
	[Measure_Caliber] [smallint] NULL , (FOREIGN KEY)
	[Kind_Caliber] [smallint] NULL , (FOREIGN KEY)
	[Ripening_grade] [smallint] NULL , (FOREIGN KEY)
	[Available_quantity] [numeric](8, 2) NULL ,
	[Measure_quant] [smallint] NULL , (FOREIGN KEY)
	[Kind_container] [smallint] NULL , (FOREIGN KEY)
	[Quant_container] [numeric](8, 2) NULL ,
	[Measure_quantCont] [smallint] NULL , (FOREIGN KEY)
	[Image_name] [varchar] (256) COLLATE Modern_… NULL ,
	[Directory_path_num] [int] NULL , (FOREIGN KEY)
	[Transport_cost] [smallint] NULL , (FOREIGN KEY)
	[Price] [numeric](8, 2) NULL ,
	[Currency] [smallint] NULL , (FOREIGN KEY)
	[Measure_Price] [smallint] NULL , (FOREIGN KEY)
	[Quant_avail_MA] [numeric](8, 2) NULL ,
	[Measure_quant_MA] [smallint] NULL , (FOREIGN KEY)
	[Kind_container_MA] [smallint] NULL , (FOREIGN KEY)
	[Quant_container_MA] [numeric](8, 2) NULL ,
	[Measure_qCont_MA] [smallint] NULL , (FOREIGN KEY)
	[Price_MA] [numeric](8, 2) NULL ,
	[Measure_Price_MA] [smallint] NULL , (FOREIGN KEY)
	[Quant_avail_MM] [numeric](8, 2) NULL ,
	[Measure_quant_MM] [smallint] NULL , (FOREIGN KEY)
	[Kind_container_MM] [smallint] NULL , (FOREIGN KEY)
	[Quant_container_MM] [numeric](8, 2) NULL ,
	[Measure_qCont_MM] [smallint] NULL , (FOREIGN KEY)
	[Price_MM] [numeric](8, 2) NULL ,
	[Measure_Price_MM] [smallint] NULL , (FOREIGN KEY)
	[Quant_avail_MB] [numeric](8, 2) NULL ,
	[Measure_quant_MB] [smallint] NULL , (FOREIGN KEY)
	[Kind_container_MB] [smallint] NULL , (FOREIGN KEY)
	[Quant_container_MB] [numeric](8, 2) NULL ,
	[Measure_qCont_MB] [smallint] NULL , (FOREIGN KEY)
	[Price_MB] [numeric](8, 2) NULL ,
	[Measure_Price_MB] [smallint] NULL , (FOREIGN KEY)


	[Caliber_QA] [numeric](6, 2) NULL ,
	[Measure_Caliber_QA] [smallint] NULL , (FOREIGN KEY)
	[Kind_Caliber_QA] [smallint] NULL , (FOREIGN KEY)
	[Ripening_grade_QA] [smallint] NULL , (FOREIGN KEY)
	[Quant_avail_QA] [numeric](8, 2) NULL ,
	[Measure_quant_QA] [smallint] NULL , (FOREIGN KEY)
	[Kind_container_QA] [smallint] NULL , (FOREIGN KEY)
	[Quant_container_QA] [numeric](8, 2) NULL ,
	[Measure_qCont_QA] [smallint] NULL , (FOREIGN KEY)
	[Image_name_QA] [varchar] (256) COLLATE Modern_… NULL ,
	[Transport_cost_QA] [smallint] NULL , (FOREIGN KEY)
	[Price_QA] [numeric](8, 2) NULL ,
	[Measure_Price_QA] [smallint] NULL , (FOREIGN KEY)
	[Quant_avail_QA_MA] [numeric](8, 2) NULL ,
	[Measure_quant_QA_MA] [smallint] NULL , (FOREIGN KEY)
	[Kind_container_QA_MA] [smallint] NULL , (FOREIGN KEY)
	[Quant_container_QA_MA] [numeric](8, 2) NULL ,
	[Measure_qCont_QA_MA] [smallint] NULL , (FOREIGN KEY)
	[Price_QA_MA] [numeric](8, 2) NULL ,
	[Measure_Price_QA_MA] [smallint] NULL , (FOREIGN KEY)
	[Quant_avail_QA_MM] [numeric](8, 2) NULL ,
	[Measure_quant_QA_MM] [smallint] NULL , (FOREIGN KEY)
	[Kind_container_QA_MM] [smallint] NULL , (FOREIGN KEY)
	[Quant_container_QA_MM] [numeric](8, 2) NULL , 
	[Measure_qCont_QA_MM] [smallint] NULL , (FOREIGN KEY)
	[Price_QA_MM] [numeric](8, 2) NULL ,
	[Measure_Price_QA_MM] [smallint] NULL , (FOREIGN KEY)
	[Quant_avail_QA_MB] [numeric](8, 2) NULL ,
	[Measure_quant_QA_MB] [smallint] NULL , (FOREIGN KEY)
	[Kind_container_QA_MB] [smallint] NULL , (FOREIGN KEY)
	[Quant_container_QA_MB] [numeric](8, 2) NULL ,
	[Measure_qCont_QA_MB] [smallint] NULL , (FOREIGN KEY)
	[Price_QA_MB] [numeric](8, 2) NULL ,
	[Measure_Price_QA_MB] [smallint] NULL , (FOREIGN KEY)
	[Caliber_QM] [numeric](6, 2) NULL ,
	[Measure_Caliber_QM] [smallint] NULL , (FOREIGN KEY)
            ...	

            And the same with QM and QB.

	[Other_data] [varchar] (1000) COLLATE Modern_… NULL ,

Thanks

mr_mist
Grunnio

1870 Posts

Posted - 2005-05-05 : 07:32:13

Your table is not normalised, so it's not a particularly good design. You should take any of the repeating rows out of the table and put them into their own tables. Possibilities might be things like

Conatiner table
Quant
Measure
Price
style
foreign keys..

Style would then be MM/MB/QA or whatever. Your current design would be fairly inflexible.

If you go down the route of keeping everything in your one table like that, be prepared to write horrific queries later on when you want to fetch any kind of information out of it.

-------
Moo. :)

cesark
Posting Yak Master

215 Posts

Posted - 2005-05-05 : 11:03:23

Ok.

Perhaps the best choice would be create a main table with the common fields, this:

CREATE TABLE [Offers] (
[Offer_id] [bigint] IDENTITY (1, 1) NOT NULL ,
[Reference] [varchar] (50) COLLATE Modern_… NULL ,
[User_num] [bigint] NULL ,
[theDate] [datetime] NULL ,
[Product_num] [smallint] NULL ,
[Other_families] [varchar] (50) COLLATE Modern_… NULL ,
[Other_products] [varchar] (50) COLLATE Modern_… NULL ,
[Variety] [varchar] (50) COLLATE Modern_… NULL ,
[Trade_name] [varchar] (50) COLLATE Modern_… NULL ,
[Offer_title] [varchar] (100) COLLATE Modern_… NULL ,
[place_City_num] [int] NULL ,
[day] [char] (2) COLLATE Modern_… NULL ,
[month] [char] (2) COLLATE Modern_.. NULL ,
[year] [char] (4) COLLATE Modern_… NULL ,
[origin_State_num] [smallint] NULL ,
[origin_City_num] [int] NULL ,
[Kind_production_num] [tinyint] NULL ,
[DOP_IGP] [smallint] NULL ,
[Certificate_private] [varchar] (50) COLLATE Modern_… NULL ,
[Certificate_auto] [smallint] NULL ,
[Other_Certificates] [varchar] (150) COLLATE Modern_… NULL ,
[Other_data] [varchar] (1000) COLLATE Modern_… NULL ,

And then a linked table (to the main ‘Offers’) called ‘Offer_quality’ with the necessary fields to differentiate every kind of quality (Q, QA, QM and QB)

CREATE TABLE [Offer_quality] (
[Offer_quality_id] [bigint] IDENTITY (1, 1) NOT NULL ,
[Offer_num] [bigint], (FOREIGN KEY) linked to the main table ‘Offers’
[Quality_num] [smallint], (FOREIGN KEY)
[Caliber] [numeric](6, 2) NULL ,
[Measure_Caliber] [smallint] NULL , (FOREIGN KEY)
[Kind_Caliber] [smallint] NULL , (FOREIGN KEY)
[Ripening_grade] [smallint] NULL , (FOREIGN KEY)
[Available_quantity] [numeric](8, 2) NULL ,
[Measure_quant] [smallint] NULL , (FOREIGN KEY)
[Kind_container] [smallint] NULL , (FOREIGN KEY)
[Quant_container] [numeric](8, 2) NULL ,
[Measure_quantCont] [smallint] NULL , (FOREIGN KEY)
[Image_name] [varchar] (256) COLLATE Modern_… NULL ,
[Directory_path_num] [int] NULL , (FOREIGN KEY)
[Transport_cost] [smallint] NULL , (FOREIGN KEY)
[Price] [numeric](8, 2) NULL ,
[Currency] [smallint] NULL , (FOREIGN KEY)
[Measure_Price] [smallint] NULL , (FOREIGN KEY)

And another linked table (to ‘Offer_quality’) called ‘Offer_ripening’ to differentiate the ripening grade for every kind of quality in the previous table:

CREATE TABLE [Offer_ripening] (
[Offer_ripening_id] [bigint] IDENTITY (1, 1) NOT NULL ,
[Offer_quality_num] [bigint], (FOREIGN KEY) linked to previous table
[Ripening_grade] [smalling], (FOREIGN KEY)
[Available_quantity] [numeric](8, 2) NULL ,
[Measure_quant] [smallint] NULL , (FOREIGN KEY)
[Kind_container] [smallint] NULL , (FOREIGN KEY)
[Quant_container] [numeric](8, 2) NULL ,
[Measure_quantCont] [smallint] NULL , (FOREIGN KEY)
[Price] [numeric](8, 2) NULL ,
[Measure_Price_MA] [smallint] NULL , (FOREIGN KEY)

The columns in bold would be ‘repeated’ in both tables ‘Offer_quality’ and ‘Offer_ripening’.

Then the repeating rows in ‘Offer_quality’ table would be up to three possible rows (QA, QM, QB), or only one (Q). For an offer number.

And the repeating rows in ‘Offer_ripening’ would be up to three possible rows (MA, MM, MB) ore none. For an offer number.

What do you think about this design? Please correct me if there is a better alternative in your opinion.

Thank you,
Cesar

cesark
Posting Yak Master

215 Posts

Posted - 2005-05-05 : 13:19:30

Or perhaps it would be better only a linked table, this:

A linked table (to the main ‘Offers’) called ‘Offer_details’ with a field to specify the kind of quality (Q, QA, QM and QB) and a field to specify the kind of ripening (MA, MM, MB or null)


   CREATE TABLE [Offer_details] (
            [Offer_details_id] [bigint] IDENTITY (1, 1) NOT NULL ,
            [Offer_num] [bigint], (FOREIGN KEY) linked to the main table ‘Offers’
            [Quality_num] [smallint], (FOREIGN KEY) (Q, QA, QM or QB)
            [Ripening_grade] [smallint], (FOREIGN KEY) (MA, MM, MB or null)
            [Caliber] [numeric](6, 2) NULL ,
	[Measure_Caliber] [smallint] NULL , (FOREIGN KEY)
	[Kind_Caliber] [smallint] NULL , (FOREIGN KEY)
	[Ripening_grade] [smallint] NULL , (FOREIGN KEY)
            [Available_quantity] [numeric](8, 2) NULL ,
	[Measure_quant] [smallint] NULL , (FOREIGN KEY)
	[Kind_container] [smallint] NULL , (FOREIGN KEY)
	[Quant_container] [numeric](8, 2) NULL ,
	[Measure_quantCont] [smallint] NULL , (FOREIGN KEY) 
	[Image_name] [varchar] (256) COLLATE Modern_… NULL ,
	[Directory_path_num] [int] NULL , (FOREIGN KEY)
	[Transport_cost] [smallint] NULL , (FOREIGN KEY) 
            [Price] [numeric](8, 2) NULL , 
	[Currency] [smallint] NULL , (FOREIGN KEY)
            [Measure_Price] [smallint] NULL , (FOREIGN KEY),

The problem is these fields in bold are only used once in every quality (Q, QA, QM and QB), but not for every ripening grade in each quality.
So, all the inserted rows (except the first one) with this field (Ripening_grade) different to null will have null values or the same values than the first one in those ‘no sense’ fields. This can be a problem?

Then the total repeating rows for an offer in ‘Offer_details’ table can be up to 9:

QA and MA (120 Kg of QA and high ripening grade)
QA and MM (48 Kg of QA and medium ripening grade)
QA and MB (230 Kg of QA and low ripening grade)

QM and MA (20 Kg of QM and high ripening grade)
QM and MM (310 Kg of QM and medium ripening grade)
QM and MB (400 Kg of QM and low ripening grade)

QB and MA (25 Kg of QB and high ripening grade)
QB and MM (55 Kg of QB and medium ripening grade)
QB and MB (130 Kg of QB and low ripening grade)

cesark
Posting Yak Master

215 Posts

Posted - 2005-05-06 : 05:54:47

What do you think about this design?:

The main ‘Offers’ table with all the common fields for an offer:


    CREATE TABLE [Offers] (
	[Offer_id] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Reference] [varchar] (50) COLLATE Modern_… NULL ,
	[User_num] [bigint] NULL ,
	[theDate] [datetime] NULL ,
	[Product_num] [smallint] NULL ,
	[Other_families] [varchar] (50) COLLATE Modern_… NULL ,
	[Other_products] [varchar] (50) COLLATE Modern_… NULL ,
	[Variety] [varchar] (50) COLLATE Modern_… NULL ,
	[Trade_name] [varchar] (50) COLLATE Modern_… NULL ,
	[Offer_title] [varchar] (100) COLLATE Modern_… NULL ,
	[place_City_num] [int] NULL ,
	[day] [char] (2) COLLATE Modern_… NULL ,
	[month] [char] (2) COLLATE Modern_.. NULL ,
	[year] [char] (4) COLLATE Modern_… NULL ,
	[origin_State_num] [smallint] NULL ,
	[origin_City_num] [int] NULL ,
	[Kind_production_num] [tinyint] NULL ,
	[DOP_IGP] [smallint] NULL ,
	[Certificate_private] [varchar] (50) COLLATE Modern_… NULL ,
	[Certificate_auto] [smallint] NULL ,
	[Other_Certificates] [varchar] (150) COLLATE Modern_… NULL ,
        [Other_data] [varchar] (1000) COLLATE Modern_… NULL ,
        [Currency] [smallint] NULL , (FOREIGN KEY),

Then, a ‘Offers_details1’ table with the different qualities we want to differentiate (up to three rows for an offer. QA, QM and QB). And the fields which are unique for every specified quality:


   CREATE TABLE [Offer_details1] (
        [Offer_details1_id] [bigint] IDENTITY (1, 1) NOT NULL ,
        [Offer_num] [bigint], (FOREIGN KEY) linked to ‘Offers’ table 
        [Quality_num] [smallint], (FOREIGN KEY) (Q, QA, QM or QB)
        [Caliber] [numeric](6, 2) NULL ,
	[Measure_Caliber] [smallint] NULL , (FOREIGN KEY)
	[Kind_Caliber] [smallint] NULL , (FOREIGN KEY)
        [Image_name] [varchar] (256) COLLATE Modern_… NULL ,
	[Directory_path_num] [int] NULL , (FOREIGN KEY)
	[Transport_cost] [smallint] NULL , (FOREIGN KEY),

Then, a ‘Offers_details2’ table with the different ripening grades we want to differentiate (up to three rows for each quality of an offer in ‘Offers_details1’ table. MA, MM and MB). And the fields which are unique for every specified ripening grade:


  CREATE TABLE [Offer_details2] (
        [Offer_details2_id] [bigint] IDENTITY (1, 1) NOT NULL ,
        [Offer_details1_num], (FOREIGN KEY) linked to ‘Offer_details1’ table
        [Ripening_grade] [smallint], (FOREIGN KEY) (MA, MM, MB or null)
        [Available_quantity] [numeric](8, 2) NULL ,
	[Measure_quant] [smallint] NULL , (FOREIGN KEY)
	[Kind_container] [smallint] NULL , (FOREIGN KEY)
	[Quant_container] [numeric](8, 2) NULL ,
	[Measure_quantCont] [smallint] NULL , (FOREIGN KEY) 	 
        [Price] [numeric](8, 2) NULL , 
	[Measure_Price] [smallint] NULL , (FOREIGN KEY),

In ‘Offers_details1’ will be stored the kind of quality (Q, QA, QM, QB) for every offer in ‘Offers’ table. And it can be stored up to three rows for one offer, differentiating three qualities (QA, QM and QB).

In ‘Offers_details2’ will be stored the kind of ripening (MA, MM, MB) for every kind of quality for an offer in ‘Offers_details1’ table. And it can be stored up to three rows for each quality of an offer defined in ‘Offers_details1’ table, differentiating three ripening grades (MA, MM and MB). So, up to 9 rows for an offer will be possible here.

Thank you

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-06 : 08:32:45

This might help: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_2oby.asp

along with this article: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/eqbol/eqintbol_008c.asp

cesark
Posting Yak Master

215 Posts

Posted - 2005-05-06 : 10:11:45

Good article the first one. But do you think now my tables posted are noramalised?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources