Designing the core tables of my DB and application

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)

Designing the core tables of my DB and application

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-05-10 : 04:07:22

I am designing a very important set of tables in my database, which are for now the core of my DB and application. The purpose of these tables is to store offers of products that users will enter in the app. These offers, every one, will consist in a set of general fields for an offer (only one possible row), a set of fields about the quality of the product which can be up to three different qualities at the same time (three possible rows for an offer), and a set of fields about the ripening grade of the product which can be up to three different grades for every quality specified (so, up to nine possible rows for an offer).

And this is the design I decided for now. What do you think about it?:

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 (minimum one, and maximum up to three rows for an offer. Q, 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 (minimum one row, and maximum 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 maximum up to three rows for one offer differentiating these three qualities (QA, QM and QB), and minimum one row differentiating one of these qualities (Q, QA, QM or QB).

In ‘Offers_details2’ will be stored the kind of ripening grade (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, maximum up to 9 rows for an offer will be possible here, and minimum 1 row (MA, MM, MB or Null).

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’ t realize it.
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?

Thank you,
Cesar

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-05-10 : 05:29:13

a) Get you naming to more reflect reality. Offer_details1 -> OfferQuality and Offer_details2 --> OfferRipeningQuality. It may seem trivial, but I find it vital for modelling.

b) Stop thinking OO.. It will royally screw you model.

c) You have variable cardinality database constraint or in some other speak.. "A data dependant cardinality Foreign Key". UDF's make this easy to implement.

quote:

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’ t realize it.
Is this a bad practice?

Must...call...ambulance....

DavidM

A front-end is something that tries to violate a back-end.

cesark
Posting Yak Master

215 Posts

Posted - 2005-05-10 : 06:12:40

quote:
a) Get you naming to more reflect reality. Offer_details1 -> OfferQuality and Offer_details2 --> OfferRipeningQuality. It may seem trivial, but I find it vital for modelling.

These aren't the real names, are only an aapproximated translation from t0he Spanish.

quote:
b) Stop thinking OO.. It will royally screw you model.

What do you mean with 'OO..'?

quote:
c) You have variable cardinality database constraint or in some other speak.. "A data dependant cardinality Foreign Key". UDF's make this easy to implement.

I don' t understand anything what you mean. What is UDF's and "A data dependant cardinality Foreign Key"?

Aobut allow null values: If it is wrong, Is it enough to change those fields to not allow null values?

Thanks

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-10 : 13:42:01

OO = Object Oriented.
--A lot of programmers bring their object oriented mindset to the database design. As a result, they don't build a good relations model, have data integrity issues, and generally have bad performance or hack things when the database gets large.

UDF = User Defined Function.
--Go to Books Online and type in "user-defined functions" at the Index tab. You'll get all kinds of information.

Cardinality thoughts: http://weblogs.sqlteam.com/davidm/archive/2003/11/18/539.aspx

You shouldn't allow NULL in the database layer if the data rules indicate there should not be NULL values. Enforce database integrity at the database layer. You can't count on the application(s) doing it for you. Generally, an overuse of NULL in an OLTP system is a sign of bad design. Either you have not normalized sufficiently, or you have not enforced proper data integrity.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

cesark
Posting Yak Master

215 Posts

Posted - 2005-05-10 : 18:47:30

Ok I am going to find out some information about User Defined Functions and Cardinality.

But, apart of the columns in bold which I have to not allow null values and some other little considerations, Do you really think that in general, the design of these three tables is bad?? If so, which is the right way? Only the guide lines..

If not is bad, Is it so difficult to accept (recognize) an acceptable design?? Since there isn’ t an only right way to design a DB.. There are a lot of different points of view in DB design. I only want to know if it is an acceptable design, nothing else..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources