Snowflake Schema . . . or . . . Lookup of a Lookup

[Donna Kelly]

Help with best practice design for a web database, please!

I have a 3NF logical design that looks like this:

Table Category. Fields CatID (PK), CatDesc
Table Range. Fields RangeID (PK), RangeDesc, CatID (FK)
Table Color. Fields ColID (PK), ColDesc
Table Product. Fields ProdID (PK), ProdDesc, RangeID (FK), ColID (FK)


There are three possible implementation models (physical table designs).

1. the 3NF design. Keep all as separate tables.

2. the 2NF design. Flatten the snowflake into a star by combining Category and Range with repeated Category per Range. This would result in a table like:

Table CatRange. Fields RangeID (PK), CatID, CatDesc, RangeDesc

3. the 1NF design aka "flat schema" aka flat table. This is sometimes used with tools like Excel PivotTables or QlikView to create fast-access in-memory data structures.

This completely denormalised design uses a single table. There are obviously no keys, no joins, and no lookups. Whilst it can be useful for pure analytical applications, it's a pig if any of the dimensions get updated. And in this kind of application, they will!


If this was SQL Server, I'd have no problems at all. But I haven't got a clue about how best to implement this in an Access 2010 web database. Fer gossake, even the 'Relationships' button in Database Tools is grayed out, so I can't even do joins. I'm thinking, that means I have to do Lookups. and in the 3NF physical design, that means Lookups of Lookups.

I built the 2NF design, and it's not very good. Maintenance of the Category and Range data is done with a single form, and the end user has to keep referential integrity in mind, rather than having the machine do it.


What I need from you guys is your thoughts and opinions, please. What's best practice here, and what are your recommendations? Most importantly, can you provide guidance regarding the steps required to implement lookups of lookups?

Cheers, Donna

 

[plog]

If this was SQL Server, I'd have no problems at all.

That makes no sense to me. Proper table structure is proper table structure is proper table structure. The color of your forms doesn't matter, the number of reports you expect to produce doesn't matter, and the database software you use to implement it doesn't matter.

How would you do it with SQL Server? And what gives you reservations about doing it that way in Access?

 

[CJ_London]

Doesn't make sense to me either

'Relationships' button in Database Tools is grayed out, so I can't even do joins

Are you saying you can't create a query?

Or are you wanting the use the dreadful lookup feature in Access table design (which is not available in SQL Server)

 

[Donna Kelly]

@plog

I'm sorry my comment "If this was SQL Server, I'd have no problems at all" made no sense. I will try to be more clear.

To illustrate my comment, I created the 3NF structure in SQL Server. Then I generated the script. This is the code shown below. It fully and precisely defines the DDL of the "proper table structure" for a 3NF schema.

I have no "reservations" about doing it that way in Access. I just do not know how to use SQL as a DDL implementation language for an Access 2010 Web Database. That is why I asked for help on best practice design for a web database.

I would ask you this: do you prefer star schemas or snowflake schemas?

• If the latter, how do you implement lookup of a lookup?
• If the former, how do manage referential integrity in the case of a denormalized dimension table like CatRange?

Perhaps you could elucidate?

Cheers, Donna

CREATE DATABASE [ACCESS2010]
GO
CREATE TABLE [dbo].[Category](
[CatID] [numeric](18, 0) NOT NULL,
[CatDesc] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
[CatID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[Color] Script Date: 20/06/2014 18:33:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Color](
[ColID] [numeric](18, 0) NOT NULL,
[ColDesc] [nvarchar](50) NULL,
CONSTRAINT [PK_Color] PRIMARY KEY CLUSTERED
(
[ColID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[Product] Script Date: 20/06/2014 18:33:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Product](
[ProdID] [numeric](18, 0) NOT NULL,
[ProdDesc] [nvarchar](50) NULL,
[RangeID] [numeric](18, 0) NOT NULL,
[ColID] [numeric](18, 0) NOT NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProdID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[Range] Script Date: 20/06/2014 18:33:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Range](
[RangeID] [numeric](18, 0) NOT NULL,
[RangeDesc] [nvarchar](50) NULL,
[CatID] [numeric](18, 0) NOT NULL,
CONSTRAINT [PK_Range] PRIMARY KEY CLUSTERED
(
[RangeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_Color] FOREIGN KEY([ColID])
REFERENCES [dbo].[Color] ([ColID])
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Color]
GO
ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_Range] FOREIGN KEY([RangeID])
REFERENCES [dbo].[Range] ([RangeID])
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Range]
GO
ALTER TABLE [dbo].[Range] WITH CHECK ADD CONSTRAINT [FK_Range_Category] FOREIGN KEY([CatID])
REFERENCES [dbo].[Category] ([CatID])
GO
ALTER TABLE [dbo].[Range] CHECK CONSTRAINT [FK_Range_Category]
GO
USE [master]
GO

 

[Donna Kelly]

Doesn't make sense to me either
Are you saying you can't create a query?

@CJ London. Again, I am sorry for not being clear.

No I am not saying that I can't create a query. I am saying that when I open the table in my database (equivalent to Product in my example), in the Database Tools tab of the Ribbon, the Relationships button is grayed out.

In desktop mode, I would use Edit Relationships in Design of Relationships Tools to establish relationships between the tables. In Web mode, it would seem I cannot. As far as I'm aware, that's not a feature of Access Services. Unless I'm missing something, which is of course perfectly possible.

Or are you wanting the use the dreadful lookup feature in Access table design (which is not available in SQL Server)

I do not understand why the "lookup feature" has such a bad rep in the Access community. Assuming that one employs the "proper table structure" such as I defined in my SQL DDL, then PK->FK relationships are properly maintained. Notwithstanding that, here is what Microsoft has to say:

Create a relationship between two web tables
To create a relationship in a web database, you use the lookup wizard to create a lookup field. The lookup field goes in the table that is on the many- side of the relationship, and points to the table that is on the one- side of the relationship.

Create a lookup field in Datasheet view
Open the table that you want on the many- side of the relationship.
Click the arrow next to Click to Add, and then click Lookup & Relationship.
Follow the steps of the Lookup Wizard to create the lookup field.

Is that "dreadful"? I dunno how else to maintain relationships between two SharePoint lists (for that is what these 'tables' will become when published).

Again, please forgive me if I'm rather naive as to the wizardry of Access.

Cheers, Donna

BTW, the Microsoft page is called "Build and publish an Access database to SharePoint".

 

[GinaWhipp]

Maybe this will help clear things up...
http://office.microsoft.com/en-us/a...r-an-access-desktop-database-HA104136335.aspx

 

[Donna Kelly]

should-i-create-an-access-2013-app-or-an-access-desktop-database

@GinaWhipp. Thank you for this. It's kind of you.

Unfortunately, Desktop or Cloud is the fundamental design decision, and my original post was to do with "Help with best practice design for a web database, please!". This application needs to be available to users in different cities, on different networks, and a Web Database it must be.

I would still like to know the answers to stars, snowflakes, and lookup of a lookup . . .

Cheers, Donna

 

[GinaWhipp]

@Donna Kelly,

I was not posting that in answer to your question, I was psoting it based on the replies I saw. Thought it might add some clarity as everyone taking a peek at the question may not be aware of the differences.

Now, taking a stab at answering your question. Set up it up the way you would any other database, the rules don't change because the platform does.

 

[CJ_London]

I don't have much call to use Access web db so not too familar with it.

I do not understand why the "lookup feature" has such a bad rep in the Access community

The problem with the lookup is that what you see is say text e.g. customer name, whereas what is stored is a number - e.g. the customer ID. This can cause confusion when creating db's. If it was such a good idea, I'm sure MS would have implemented it in SQL Server - but they haven't.

Lookups can also slow performance because when a table with lookups is opened, it also has to bring through the related tables.

Perhaps it is just a difference of opinion between the 'old timers' and the new ones on the block

 

[GinaWhipp]

To add to what CJ_London posted...

http://www.mvps.org/access/lookupfields.htm

 

[CJ_London]

Unfortunately, Desktop or Cloud is the fundamental design decision, and my original post was to do with "Help with best practice design for a web database, please!".

As Gina says, best practice design is the same, whatever the underlying platform.

Perhaps your question should be

What is the best underlying platform to meet the application needs of being available to users in different cities, on different networks?

With regards your question re 3NF, 2NF etc I would use 3NF but the answer partly depends on the anticipated number of rows in the lookup tables and number of FK's. For example colour - if there are only 7 or 8 colours then it may be more efficient to simply store these as a text value in the 'main' table (which can be maintained using a value list in a combo or list box) rather than maintaining indexes and PK/FK relationships.

You can take things too far - in theory minimising the size of the db, by creating lookup tables, but then perhaps needing additional indexing and more complex queries (which will take longer to process) to produce the required output.

My own belief is that performance is king - in simple terms, the faster the user can get results or process data the better. This needs to be balanced by the work that the server needs to do - so your 1NF schema is probably fastest to return data but slowest (and riskiest) on updating and takes more space.

Across the web, performance is typically slow compared with working across a network so you need to keep the amount of data transfer between server and client to a minimum - so no long lists as you are probably used to seeing in network based solutions. Instead, you pass perhaps 20/30 records at a time and refesh the screen. Similarly forms/reports are not designed with a recordsource of a full table, but based on a query which returns a single row or a few rows.

Does this answer your question?

 

[Donna Kelly]

As Gina says, best practice design is the same, whatever the underlying platform.

@CJ London

No-one could disagree with this as a statement of principle, particularly when discussing logical design. However, I feel that best practice physical design does vary dramatically depending on the implementation purpose and platform. As you so validly point out, techniques also vary with details like data volumes and need for network traffic minimisation.

For example, if I'm creating a data warehouse using Oracle RDBMS, then I'm likely going to use Materialized Views based on a partially denormalized 2NF Star Schema. Alternately, if I'm going to feed a Microsoft Analysis Services hypercube, then I will use a pure 3NF Snowflake Schema and depend on the Analysis Services Data Source View to stitch together the dimensional components. I've already mentioned fully denormalized 1NF structures for things like PowerPivot. And of course, if I'm going to build an operational / transactional system, then my database design with be fully normalized 3BNF with the occasional 5NF if I need any chart-of-account or bill-of-materials structures.

Best physical design practices . . . depend on physical needs.

In the case I was seeking help on, an Access 2010 Web Database, I wanted your opinion on how best to manage the lookup of a lookup issue. As I pointed out, regardless of the 'evils of lookups' debate, using the lookup wizard is the only way to create a relationship between two web tables.

I could only see two ways of implementing the required logical design.

1. Use a 3NF structure like T1->T2->T3 (which means lookup of a lookup) or
2. Use a 2NF structure like T1(repeating)withT2->T3 (which means T1/T2 maintenance is a pain and a pig for the end users).

Indeed, these remain the only two ways I can see of physically structuring the data.

Regarding volume issues like volume and traffic, please rest assured that I have taken them into account. I have some experience in this regard, and performance is not a challenge for this application.

My original question was with regard to best practice design for a web database, and to this point I still do not know whether the better solution from the end user viewpoint (3NF) is practical, because it depends on the viability of lookup of a lookup, and I do not know how to do that in Access 2010. Although I could create a cascading relationship between SharePoint lists, so I know that the target is indeed feasible! (Example below.)

You mentioned

Across the web, performance is typically slow ... so you need to keep the amount of data transfer between server and client to a minimum

and asked

Does this answer your question?

My answer must be no, at this point I do not have an answer to my question which was specifically about Access 2010 Web Database i.e. physical design. Being as specific as I can, I do not know how to create the required data structure in an Access 2010 design surface, for an intended implementation as a Web Database (i.e SharePoint 2010 lists and not an ACE engine).

By the way, my sincere thanks for sticking with this thread, even if there's no conclusion as yet.

Cheers, Donna

As an example, you may wish to look at dataqueen.unlimitedviz.com/2012/03/best-practices-for-cascading-sharepoint-lists-using-infopath-forms/ The pictures on that web page show exactly the kind of data structure I'm trying to implement.

 

[CJ_London]

I fail to understand your requirement.

My original question was with regard to best practice design for a web database

This has been answered from my perspective.

You then say

, I feel that best practice physical design does vary dramatically depending on the implementation purpose and platform.

You have not provided any information regarding purpose or platform

The fact you that in access 2010 web you are unable to create relationships between tables is irrelevant.The nature of what you are trying to achieve does not require a 'defined' relationship and Sharepoint is not a 'proper' database that utilises database rules - it is simply an environment that stores tables of data. Personally I struggle to use Sharepoint in an interactive way and consider it to be closely aligned to Excel in useability.

And you ask

I wanted your opinion on how best to manage the lookup of a lookup issue

But this was in the context of defining relationships and as has been mentioned before and above, a formal relationship is not required, you simply create the required query