SQL Server Performance Forum – Threads Archive
A very large table design
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#%92 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 gradeMM 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
u can have a master table and detail table Releation ship is one to many in which detail table can have more than one record Detail table Key is OfferId and it other genereted number
You cna have addition filed called Type in which you can store QA, QM and etc which is an field
Let me know some information.
Will you identify your data row by column [Offer_id]?
Whether in each row, the data will be stored for one of the combination or for all combinations. combination (only QA, only QB, QA and QM, QA and QM and QB, etc..)
seriously consider splitting this into multiple tables,
consider taking the infrequently used large fields into a separate table why do so many columns allow nulls? this is usually a strong indication that normalization is needed. consider using smallmoney in place of numeric(8,2). while it many technically be a numeric value and not money, there is much less overhead with the money type compared with numeric
Ok (dineshasanka, surendrakalekar and joechang) 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#%92) called ‘Offer_quality#%92 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#%92
[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#%92) called ‘Offer_ripening#%92 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] [smallint] NULL , (FOREIGN KEY)
The columns in bold would be ‘repeated#%92 in both tables ‘Offer_quality#%92 and ‘Offer_ripening#%92. Then the repeating rows in ‘Offer_quality#%92 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#%92 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
Or perhaps it would be better only a linked table, this: A linked table (to the main ‘Offers#%92) called ‘Offer_details#%92 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#%92
[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 in this table for an offer (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#%92 fields. This can be a problem?
Then the total repeating rows for an offer in ‘Offer_details#%92 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)
if there could be an abitrary number of rows in either Offer_quality or Offer_ripening for each row in Offers,
then the use of the Identity in these tables is good. However, if you know and actually want to enforce a fixed upper bound on the number of rows, I would prefer the following:
instead of having an indentity in the 2 child tables, Offer_quality has Offer_num and a char(2) column with allowed values of QA,QM,QB or Q, or a tinyint column where 1,2,3 or 4 represents the previous values.
The primary key clustered for Offer_quality is Offer_id, followed by the column described above, Offer_Q for now Offer_ripening now has a column Offer_R that is either a char(2) or tiny int to represent MA, MM, MB either literally or with numbers
The primary key clustered on this table is
Offer_ID, Offer_Q, Offer_R
What do you think about this design?: The main ‘Offers#%92 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#%92 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#%92 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#%92 table with the different ripening grades we want to differentiate (up to three rows for each quality of an offer in ‘Offers_details1#%92 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#%92 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#%92 will be stored the kind of quality (Q, QA, QM, QB) for every offer in ‘Offers#%92 table. And it can be stored up to three rows for one offer, differentiating three qualities (QA, QM and QB).
In ‘Offers_details2#%92 will be stored the kind of ripening (MA, MM, MB) for every kind of quality of an offer in ‘Offers_details1#%92 table. And it can be stored up to three rows for each quality of an offer defined in ‘Offers_details1#%92 table, differentiating three ripening grades (MA, MM and MB). So, up to 9 rows for an offer will be possible here. Thank you
quote:The primary key clustered for Offer_quality is Offer_id, followed by the column described above, Offer_Q for now
quote:The primary key clustered on this table is
Offer_ID, Offer_Q, Offer_R
Offer_ID, Offer_Q, Offer_R
What is the advantage of using your primary keys clustered? Are a better design than mine? If so, why? Thanks
Please, somebody can tell me if now my tables are normalised?
Hi Cesar,
Without looking at whole database it will very difficult to say it is 100% optimized.
But sure, it is in much much better shape than your first design. I would like to suggest you to change few fields types [Day], [Month], [Year] to tinyint, tinyint, smallint respectively and also consider the change suggested by joechang.
This is the kind of opinion I wanted for now [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />].<br /><br />I changed the [day], [month] and [year] columns to tinyint, tinyint, smallint respectively. But about ‘numeric(8,2)’ I suppose that I only have to change it to smallmoney in the [price] column? Because the rest of the fields ([Available_quantity] and [Quant_container]) are numeric fields.. Anyway, smallmoney will allow 8 digits with two optional decimal places? (For example 999999’99 or 99999999). <br />And the rest of the numeric fields ‘Numeric(6,2)’ is correct?<br /><br /><br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /> <b>CREATE TABLE [Offers]</b> (<br />[Offer_id] [bigint] IDENTITY (1, 1) NOT NULL ,<br />[Reference] [varchar] (50) COLLATE Modern_… NULL ,<br /><b>[User_num] [bigint] NULL</b> ,<br />[theDate] [datetime] NULL ,<br /><b>[Product_num] [smallint] NULL</b> ,<br />[Other_families] [varchar] (50) COLLATE Modern_… NULL ,<br />[Other_products] [varchar] (50) COLLATE Modern_… NULL ,<br />[Variety] [varchar] (50) COLLATE Modern_… NULL ,<br />[Trade_name] [varchar] (50) COLLATE Modern_… NULL ,<br /><b>[Offer_title] [varchar] (100) COLLATE Modern_… NULL</b> ,<br /><b>[place_City_num] [int] NULL</b> ,<br />[day] [tinyint] NULL ,<br />[month] [tinyint] NULL ,<br />[year] [smallint] NULL ,<br /><b>[origin_State_num] [smallint] NULL</b> ,<br />[origin_City_num] [int] NULL ,<br />[Kind_production_num] [tinyint] NULL ,<br />[DOP_IGP] [smallint] NULL ,<br />[Certificate_private] [varchar] (50) COLLATE Modern_… NULL ,<br />[Certificate_auto] [smallint] NULL ,<br />[Other_Certificates] [varchar] (150) COLLATE Modern_… NULL ,<br /> [Other_data] [varchar] (1000) COLLATE Modern_… NULL ,<br /> <b>[Currency] [smallint] NULL ,</b> (FOREIGN KEY),<br /></font id="code"></pre id="code"><br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /> <b>CREATE TABLE [Offer_details1]</b> (<br /> [Offer_details1_id] [bigint] IDENTITY (1, 1) NOT NULL ,<br /> <b><b>[Offer_num] [bigint], (FOREIGN KEY) linked to ‘Offers#%92 table</b></b> <br /> <b>[Quality_num] [smallint], (FOREIGN KEY)</b> (Q, QA, QM or QB)<br /> [Caliber] [numeric](6, 2) NULL ,<br /> [Measure_Caliber] [smallint] NULL , (FOREIGN KEY)<br /> [Kind_Caliber] [smallint] NULL , (FOREIGN KEY)<br /> [Image_name] [varchar] (256) COLLATE Modern_… NULL ,<br /> [Directory_path_num] [int] NULL , (FOREIGN KEY)<br /> [Transport_cost] [smallint] NULL , (FOREIGN KEY),<br /></font id="code"></pre id="code"><br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /> <b>CREATE TABLE</b> [Offer_details2] (<br /> [Offer_details2_id] [bigint] IDENTITY (1, 1) NOT NULL ,<br /> <b><b>[Offer_details1_num], (FOREIGN KEY)</b> linked to ‘Offer_details1#%92 table</b><br /> [Ripening_grade] [smallint], (FOREIGN KEY) (MA, MM, MB or null)<br /> <b>[Available_quantity] [numeric](8, 2) NULL</b> ,<br /> <b>[Measure_quant] [smallint] NULL , (FOREIGN KEY)</b><br /> [Kind_container] [smallint] NULL , (FOREIGN KEY)<br /> [Quant_container] [numeric](8, 2) NULL ,<br /> [Measure_quantCont] [smallint] NULL , (FOREIGN KEY) <br /> <b>[Price] [numeric](8, 2) NULL</b> , <br /> <b>[Measure_Price] [smallint] NULL</b> , (FOREIGN KEY),<br /></font id="code"></pre id="code"><br /><br /><br />The fields (columns) in bold are specified to allow null values in the tables but the application forces to fill them. Indeed, in all my tables in the DB I allow null values for all the columns except the identity, and I let my application (in server side) decide which fields are obligatory to fill or not, and tell it to user if he/she didn#%92 t realize it.<br />Is this a bad practice? If so, is it enough to change those fields to not allow null values? Or it continue having too much possible null values in my tables posted?<br /><br /> <br />Thank you<br />
For me it’s now ok [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]
]]>