Home » Other » General » Database Design Review
Database Design Review [message #482726] Mon, 15 November 2010 11:37 Go to next message
IcedDante
Messages: 17
Registered: April 2010
Location: San Diego
Junior Member
I am fairly new to Database creation and wanted to post my design on this forum to get some feedback. Reading through the different sub forums here I am unsure of which one is the right one for such a post. Designer? General?

Or if it is here I'll just ask in this thread.
Re: Database Design Review [message #482734 is a reply to message #482726] Mon, 15 November 2010 11:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The one hard and fast rule for this forum, is that there are NO hard and fast rules.

IMO, this is as good a place as any for your thread.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Database Design Review [message #482752 is a reply to message #482726] Mon, 15 November 2010 12:55 Go to previous messageGo to next message
IcedDante
Messages: 17
Registered: April 2010
Location: San Diego
Junior Member
Cool so then, here it is:
This will be used to hold information for the size of clothes for specific labels at different stores. I wanted to get critiques and suggestions for alternatives on my work.

Let me know what you all think.

Table: Size
This will actually be three tables, each modeling a different type of sizing. There's your standard Small, Medium, Large. Then there's Women's sizing: 00, 0, 2, 4, 6, 8, etc. Finally petite sizing, 0P, 2P, 4P, 6P, etc.

This table will hold an integer that represent the centimeter length for each size. So there will be a SizeStd, SizeSML, and SizePetite table. Each size will be a column and have a primary key generated ID. So, the columns of SizeSML would be:

  • Id
  • XS
  • S
  • M
  • L
  • XL
  • XXL


Table: Measurement
Columns are:

  • Id: Primary Key
  • Type: Secondary Key. This is an enumerated type and can be BUST, WAIST, HIP, INSEAM, or TORSO
  • SizeStdKey
  • SizePetiteKey
  • SizeSMLKey

For the three size keys above one and only one must be specified. This looks like the weakest part of my design, but I'm not sure what better alternative is available.

Table: Clothes
Columns are:
Id (primary key)
MeasurementId (contains a record for each Type enumeration)
BrandId
StoreId
Category (an enumeration with values tops, jeans, bras, etc)
Description (optional)


Other trivial tables:
Brand (A clothing brand)
Id
Name
StoreId

Store
Id
Name

Your help is appreciated. My thanks to the community.
Re: Database Design Review [message #482758 is a reply to message #482752] Mon, 15 November 2010 13:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Your help is appreciated.
Please realize that we speak SQL here so posting actual DDL is PREFERRED! to written verbiage; as explained in Posting Guideline!

Did you ever hear of Third Normal Form for application design?

[Updated on: Mon, 15 November 2010 13:11]

Report message to a moderator

Re: Database Design Review [message #482759 is a reply to message #482758] Mon, 15 November 2010 13:13 Go to previous messageGo to next message
IcedDante
Messages: 17
Registered: April 2010
Location: San Diego
Junior Member
I haven't heard of third normal form, but I will google it. I guess I was just trying to get some information on this database conceptually as it is still in the design phase.
Re: Database Design Review [message #482760 is a reply to message #482759] Mon, 15 November 2010 13:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
(Re)search "data normalization" also, too.

[Updated on: Mon, 15 November 2010 13:17]

Report message to a moderator

Re: Database Design Review [message #482768 is a reply to message #482759] Mon, 15 November 2010 13:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68264
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read Normalization wiki page, it will help you.

Regards
Michel
Re: Database Design Review [message #482773 is a reply to message #482760] Mon, 15 November 2010 13:52 Go to previous messageGo to next message
IcedDante
Messages: 17
Registered: April 2010
Location: San Diego
Junior Member
I don't think this design violated 3NF. There will be several different measurements for one piece of clothing, each for a different part of the cloth (one for the waist, one for the inseam, etc). The size can be represented in one of three different ways as well (Size number, small-medium-large, etc).

Where do you see 3NF being violated?
Re: Database Design Review [message #482776 is a reply to message #482773] Mon, 15 November 2010 14:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
please post DDL ( CREATE TABLE) for all tables in the application
Re: Database Design Review [message #483950 is a reply to message #482776] Thu, 25 November 2010 16:28 Go to previous messageGo to next message
IcedDante
Messages: 17
Registered: April 2010
Location: San Diego
Junior Member
Alright: here's the DDL I got. It's problematic, so I'll try to intersperse some questions in it.

create table SizeUSStd (
	id NUMBER(10) PRIMARY KEY,
	notecode VARCHAR(2),
	
	size00    DECIMAL(2,3),
	size00max DECIMAL(2,3),
	size0    DECIMAL(2,3),
	size0max DECIMAL(2,3),
	size2    DECIMAL(2,3),
	size2max DECIMAL(2,3),
	size4    DECIMAL(2,3),
	size4max DECIMAL(2,3),
        ...
	size26    DECIMAL(2,3),
	size26max DECIMAL(2,3)
);	

create table SizePetite (
	id NUMBER(10) PRIMARY KEY,
	notecode VARCHAR(2),
	
	size0P    DECIMAL(2,3),
	size0Pmax DECIMAL(2,3),
	size2P    DECIMAL(2,3),
	size2Pmax DECIMAL(2,3),
        ...
	size12P    DECIMAL(2,3),
	size12Pmax DECIMAL(2,3)
);	

create table SizeSML (
	id NUMBER(10) PRIMARY KEY
	notecode VARCHAR(2),
	
	sizeXXS    DECIMAL(2,3),
	sizeXXSmax DECIMAL(2,3),
	sizeXS    DECIMAL(2,3),
	sizeXSmax DECIMAL(2,3),
	sizeS    DECIMAL(2,3),
	sizeSmax DECIMAL(2,3),
	sizeM    DECIMAL(2,3),
	sizeMmax DECIMAL(2,3),
	sizeL    DECIMAL(2,3),
	sizeLmax DECIMAL(2,3),
	sizeXL    DECIMAL(2,3),
	sizeXLmax DECIMAL(2,3),
	sizeXXL    DECIMAL(2,3),
	sizeXXLmax DECIMAL(2,3)	
);	



Right off the bat I think my design is a little problematic here. I have three size tables that are referenced by the Measurement table. A Measurement table can have one and only one size type, but it can be any of the three. This makes sense from an OO programming perspective: each size class would be a subclass of the same parent, but I'm not sure what the representative database schema should look like.

CREATE TABLE Measurement (
	id number(15) PRIMARY KEY,
	sizetype VARCHAR(3) NOT NULL,
	sizeid NUMBER(10) NOT NULL,
	type ENUM('bust', 'waist', 'hip', 'inseam', 'torso', 'height') NOT NULL
);	


Basically every Measurement record is a table. There will be a row for every measurement defined in the TYPE enumeration, so a complete measurement for Jeans would be made up of a size record for the waist, hip and inseam. My idea (and I think I didn't express this right in the DDL) is that the complete key would be made up of the id and type: so there could be one id value for different types.

ID   SIZETYPE   SIZEID TYPE
5    USS        2      waist
5    USS        3      hip
5    USS        4      inseam

The above is an example of what a size record might look like for a pair of jeans where the size is represented by SizeUSStd table.


CREATE TABLE Brand (
	id NUMBER(5) PRIMARY KEY,
	name VARCHAR(30) NOT NULL,
	measurementid NUMBER(15),
	storeid NUMBER(5)
);	
	
create table Store (
	id NUMBER(5) PRIMARY KEY,
	name VARCHAR(30) NOT NULL,
	measurementid NUMBER(15)
);	

create table Clothes (
	id NUMBER(10) PRIMARY KEY
	measurementid NUMBER(15),
	storeid NUMBER(5) NOT NULL, 
	brandid NUMBER(5) NOT NULL,
	category ENUM ('tops', 'jeans', 'bras', 'dresses') NOT NULL,
	description VARCHAR(300),
	createdate date
);	
	
Re: Database Design Review [message #483952 is a reply to message #483950] Thu, 25 November 2010 17:14 Go to previous messageGo to next message
John Watson
Messages: 8738
Registered: January 2010
Location: Global Village
Senior Member
Hi - I wouldn't push into this conversation, except that I'm stuck in a hotel with nothing to read and anything is better than watching telly. So: I don't think your model is normalized. Your three tables SizeUSStd, SizePetite, and Size SML could be replaced with this:
create table sizes_master(
sm_id number primary key,
size_method varchar2(20));

create table sizes_detail(
sd_id number primary key, 
sm_id references sizes_master, 
size_name varchar2(20),
size_value number);

insert into sizes_master values(1,'SizeUSStd');
insert into sizes_master values(2,'SizePetite');
insert into sizes_master values(3,'SizeSML');

insert into sizes_detail values(1,1,'size00','0.0');
insert into sizes_detail values(2,1,'size00max',1.0);

and so on. Do you see how a group of detail rows refer to a single master row? This means that you don't have to create new tables every time a different sizing method comes along. This technique could be expanded to handle, say, shoes (or bricks, or furniture...) simply by inserting more rows.

I'm sure there are many other mormalized models that would work equally well, but perhaps the above will help; your model is certainly not adequately normalized.

(other DBAs, please note that I know the DDL is not perfect - I would never define constraints and indexes in-line like that for a production system.)
Re: Database Design Review [message #483972 is a reply to message #483952] Fri, 26 November 2010 01:20 Go to previous messageGo to next message
Littlefoot
Messages: 21737
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
John

I would never define constraints and indexes in-line like that for a production system.

Why is that so? I mean, what advantages do out-of-line defined constraints have over in-line defined ones?

I thought that it doesn't matter. True, I didn't spend too much time investigating the issue. A document I read says that - syntactically - constraints can be defined either in-line or out-of-line. It explains how to use one or another, but I failed to find a reason why you shouldn't create in-line constraints (in a production system? Does that mean that in a non-production system you can do that?).

Further reading revealed the fact that people (DBAs) prefer out-of-line defined constraints and, also, keeping them in a separate file, so - when manipulating with large amount of data - it is easier/better to create tables, load data (no indexes - faster loading), and then create indexes and constraints.

On the other hand, if there was a huge difference between those options, Oracle would probably make it impossible to create in-line constraints (if they were no good).

Is there any other reason? I guess that cosmetics and personal preferences shouldn't be discussed.
Re: Database Design Review [message #483974 is a reply to message #483972] Fri, 26 November 2010 01:38 Go to previous messageGo to next message
John Watson
Messages: 8738
Registered: January 2010
Location: Global Village
Senior Member
Hi, man. this is my reason:
Defining a unique or primary key constraint in-line means that Oracle will create a unique index. That means that if you ever disable the constraint, the index will be dropped; this can be disastrous. If you create the table, then a non-unique index, and then define the constraint, if you disable the constraint the index will survive. My own preference is to create all constraints as DEFERRABLE INITIALLY IMMEDIATE (which requires a non-unique index) and I believe gives the most flexibility.
As for aesthetics, well, most people do not name the index when they create a constraint in-line. So you end up with indexes called SYS_C123456 which is not very self-explanatory.
Re: Database Design Review [message #483975 is a reply to message #483974] Fri, 26 November 2010 01:58 Go to previous messageGo to next message
Littlefoot
Messages: 21737
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I see; thank you.

Reading what you said made me think: if you created a table (and later you'd define a primary key constraint), why did you create a non-unique index? Oracle would create it unique (if you let it). Though, the next sentence explained why, so now it makes sense. (Yes, it is your own preference; I probably put it wrong in my previous message saying that these shouldn't be discussed. Not if it were "just because it looks prettier to me". Sorry. (I'm glad you ignored it!)).

As of index names created by default, heh, anyone who ever searched for an index and found bunch of SYS_C123456s probably changed his own best practice.
Re: Database Design Review [message #484043 is a reply to message #483975] Fri, 26 November 2010 11:29 Go to previous messageGo to next message
IcedDante
Messages: 17
Registered: April 2010
Location: San Diego
Junior Member
Having a little bit of a hard time following the last bit of the discussion, even though I guess it doesn't apply to me: but what constraints and indexes did you define inline in your solution?

Thanks for posting your alternate design. Were I to implement this I suppose there would be more of a software heavy design to validate that both the size_master size_method and sizes_detail size_name values were legitimate which is one drawback. The other is this: Originally we had a table for every row of data in a size chart, now we have one for every cell. Each cell has a primary and foreign key which entails a lot more data and a much bigger database. Is that ok from a performance perspective or will the additional overhead not affect that much?

The advantage is that yes, this design is normalized. I could even add the "max_size" field to sizes_detail since it defines an optional range for a particular measurement.

Thank you very much for your help.
Re: Database Design Review [message #484059 is a reply to message #484043] Fri, 26 November 2010 14:44 Go to previous messageGo to next message
Littlefoot
Messages: 21737
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
what constraints and indexes did you define inline in your solution?
Primary key constraints in both tables, and a referential integrity constraint in SIZES_DETAIL table. Oracle also automatically created indexes on primary key columns.
SQL> select constraint_name, constraint_type, index_name
  2  from user_constraints
  3  where table_name in ('SIZES_MASTER', 'SIZES_DETAIL');

CONSTRAINT_NAME                C INDEX_NAME
------------------------------ - ------------------------------
SYS_C007131                    P SYS_C007131
SYS_C007132                    R
SYS_C007130                    P SYS_C007130


Quote:
I suppose there would be more of a software heavy design to validate that both the size_master size_method and sizes_detail size_name values were legitimate

What do you mean by saying that?

Perhaps you should let Oracle take care about as many validations as possible. This includes all kinds of constraints. You've seen two of them - primary key will automatically ensure that there are no duplicates and no NULL values in that column. Referential integrity constraint won't let detail table column to contain values that don't exist in a master table. Check constraint is another possibility. Also, don't forget database triggers.

Anyway: why would you program something that Oracle does by itself? (From what I heard (which doesn't have to be true), SAP does just the opposite: application takes care about everything, database does nothing (but stores data)).

As of normalization: unless you are creating a data warehouse (which allows redundancy), I believe that you should keep your tables normalized.
Re: Database Design Review [message #484979 is a reply to message #484059] Sat, 04 December 2010 12:30 Go to previous messageGo to next message
IcedDante
Messages: 17
Registered: April 2010
Location: San Diego
Junior Member
Does anyone else think John Watson's design is the right way to go on this, or are there better alternatives? I am still not convinced that his proposal which necessitates parsing several size_names and validating each one, basically treating columns as input, is ideal.

I also haven't selected a DB Vendor and do not think a great deal of trigger and db rules would be portable if I decided to switch vendors.
Re: Database Design Review [message #484986 is a reply to message #484979] Sat, 04 December 2010 13:04 Go to previous messageGo to next message
Frank
Messages: 7893
Registered: March 2000
Senior Member
To be honest, I did not take the time to analyze your design and requirements, but your last remark struck me.
Typically it is not recommended to code for the possibility to switch database vendors. It will result in sub-optimal code, no matter what db you will choose to use initially. Each RDBMS has its own way of dealing with stuff.
For example, Oracle's locking mechanism (readers don't lock other readers or writers) differs from SQLServer's default. T-SQL differs greatly from PL/SQL. SQLServer makes abundant use of temporary tables in which intermediate results are stored, whereas creating objects on the fly in Oracle is a total NO-NO.
Oracle flies at complex, multitable, multilevel, multi-anything queries, whereas SQLServer doesn't like you to combine too much.

In other words: choose your db and code to use it. Otherwise you're spending a lot of money on features you cannot use and you are building a system that underperforms.

[Updated on: Sat, 04 December 2010 13:05]

Report message to a moderator

Re: Database Design Review [message #485036 is a reply to message #484986] Sun, 05 December 2010 12:05 Go to previous messageGo to next message
John Watson
Messages: 8738
Registered: January 2010
Location: Global Village
Senior Member
Hello again - I'm not going to suggest that my model is the best, or only, solution. But you seem to be worried about having to code sufficient intelligence to use normalized structures. I understand your point: nomalized structures do often require more thought to set up. But they are so much easier to work with long term. Consider that with my model, you need only two statements, that you run repeatedly, to insert all the data:
insert into sizes_master values(&Method_id,'&Method_name');

to build up your list of methods, then to insert the details:
insert into sizes_detail values(&Size_id,&Method_id,'&Size_name','&Size_value');


And when you need to add some totally new size taxonomy, no problem: the data entry application can handle it. Your way, you have to write a different module for inserting into each table. It will be the same with data retrieval: you will have to write separate reports against each table. I can write one report, with a filter on the method.

All that having been said, make sure you don't over normalize. A developer straight from college will normalize your data to hell and back, with disastrous results (how do I know? Because I used to do that). The approach I would follow is to use third normal form at the systems analysis stage to produce the ideal model, then at the system design stage you compromise that model to suit the environment - de-normalizing if necessary.

So I don't agree with Frank (which is unusual): I would say you should aim for perfection in the analysis stage, then choose your database, then adjust your design to the database.
Re: Database Design Review [message #485087 is a reply to message #485036] Mon, 06 December 2010 02:14 Go to previous message
Frank
Messages: 7893
Registered: March 2000
Senior Member
John Watson wrote on Sun, 05 December 2010 19:05

So I don't agree with Frank (which is unusual): I would say you should aim for perfection in the analysis stage, then choose your database, then adjust your design to the database.

Luckily, it seems that we do agree after all. I think you are right that the initial design should be db-vendor independent, heck you should not even have to decide whether you are going to use a db at all. In the initial global stages, keep things as open as can be, as long as you're prepared to go more specific during the technical design & build phase
Previous Topic: ROLE OF CORE DBA IN APPLICATION UPGRADATION
Next Topic: Database startup issue after power failure
Goto Forum:
  


Current Time: Wed Aug 17 23:48:59 CDT 2022