Company logo of Crocus Information LtdCrocus Information Ltd

Business Requirements Analysis consultancy to blue chip companies

Logical Data Model product, Feasibility Study stage (slide 24)

The developing logical data structure diagram for the Acme Fashion case study's Feasibility Study phase, s24
case study home | previous | next

Logical Data Structure diagram

The Logical Data Structure diagram above shows the data structures in the in-scope areas for the As-Is business system. Together with the entity descriptions and volumetrics below, it forms the logical data model, (business data entities only).

This is the LDM product being worked on for the feasibility study, only available to the BA and immediate team. For instance, I know I have yet to add the Purchase Order and Purchase Order Line entities (though I have added a foreign key in the Entity Descriptons, below, from the Product entity to the (most recent) restocking purchase order placed).

These were overlooked in this very early draft and noticed when writing the elementary process descriptions (EPDs) and reviewing the data flows on the top level DFD against the LDS (as a completeness check).

...just like happens in a real project!

Entity and Data Item Descriptions

In looking at the tables below, if you didn't know, an underlined data item is a primary key. If there is more than one part to the primary key, all parts are underlined.

Brackets around a sequence of keys indicate that they are taken together and are the constituent parts of the key of the master.

If a non-key data item is the primary key of another entity then it is marked with an asterisk. This is known as a 'foreign' key.

If a data item is optional (i.e. a value isn't always present) then it is marked with an 'O'

For more info and a fuller description, see logical data structure notation


Used to describe / qualify the products Acme sells. See also Style and Size. There are a few tens of Colours.

Data items Comments / Description
Colour Code
Colour Name


Customers of Acme are presently high street retail outlets or shops within shops (aka concessiosn). Acme has around 1000 very active customers and around 2000 who place orders fairly rarely.

Data items Comments / Description
Customer Account Number
Assigned by the Accounts department when creating a new customer account
Customer's trading name
Invoicing Address
(used by Accounts only)
Accounts Telephone Number
(used by Accounts only)
Delivery Address
The default delivery address
Credit Limit Credit Limits are set by the Accounts department
Discount Allocated subjectively by the Sales Team Leader, depending on sales turnover to the customer
Normal Buyer Name
Telephone Number The telephone number of the normal buyer

You can usually assume (but always check) that the attributes for an address are fully understood by the physical design team.

IMO, nothing is gained by dividing it down into house / flat number, street name, etc. during requirements analysis and specification. Physical design, yes.


A Product isn't really a product unless fully described by a style, a colour and a size. For example:

  • Snug-fitting ladies blue jeans with hipster pockets. Style: Hipster, Colour: Blue, Size: Small / L30 W30
  • Wide fashion belt with star-shaped and bling buckle. Style: Bling Star, Colour: Shocking Pink, Size: XSmall (fits 28W)

    There are approximately 10,000 products in the catalogue

Data items Comments / Description
Product Code
The first part of the code as it appears in the catalogue.
Colour Code The colour part of the product code as it appears in the catalogue
Size Code The size part of the product code as it appears in the catalogue
Style Code The style part of the product code as it appears in the catalogue
Short Name
The product heading from the catalogue
Description (comes from the catalogue)
Discontinued Yes, if a Product is no longer available for sale
Stock Level The number currently in stock
Min Stock Holding Level If stock falls to the Min Stock Holding Level, Pylem Hye, the Stock Manager, raises a purchase order for the Reorder Quantity
Reorder Quantity (see preceding row)
* On Order Reference If a purchase order has been placed to restock this product, then this will be its purchase order reference
Expected Delivery Date If a purchase order has been placed to restock this product and the delivery has been held up, then this will be its expected delivery date
Base Selling Price
This is the standard selling price. High turnover customers are given discounts against this price.

Product Sources

A product may be available from more than one supplier. Where it is, the preferred supplier is marked as such and the other(s) marked as alternatives.

Data items Comments / Description
(Product Code)
(Colour Code)
(Size Code)
(Style Code)
The key of the Product master
Supplier Code The key of the Supplier master
Price The price of the product, after any discount from the supplier
Delivery Number of days betwen ordering and delivery if different from the standard delivery period for the Supplier
Order Code This is the product code by which the supplier identifies the product
Preferrred or Alternative If marked as preferred, this is the supplier to buy the product from. If marked as alternative, then this will be the supplier to turn to if there are difficulties getting stock from the preferred supplier.

This entity resolves the many to many relationship between Product and Supplier: each product is avaliable from many suppliers; each supplier may supply many products.

But also note that there are real, business data items associated with the resolver entity - for example, the supplier's code for the product, their price and whether they are the preferred one for that product or not. Would these have been noticed if we hadn't looked in to the M:M?


When a customer is unable to sell goods, they are allowed to return any in good condition and have their account credited, less a small handling fee.

Data items Comments / Description
(Customer Account Number)
(Returns Number)
The Returns Number is allocated by a sales assistant when a customer phones up to arrange a return. It is unique within Customer
Date Returns received This is stamped onto the returns note by Goods In
Returned by The contact within the customer in case of query
Checked in by The Goods In member of staff who checked in the goods
Comments Any comments by the checker - for example if goods are in unsatisfactory condition

Returns Line

Each Returns is made up of lines that show how much of each product is being returned. There is an average of around 2 or 3 lines per Returns with between 3 and 5 of each product being returned.

Data items Comments / Description
(Customer Account Number)
(Returns Number)
Key of Returns master
(Product Code)
(Colour Code)
(Size Code)
(Style Code)
Key of Product master
Quantity Returned

Sales Order

There are around 175 orders taken per week. Most of these are phoned through (from stores and the travelling sales team) and some are received by mail.

Data items Comments / Description
(Customer Account Number)
(Sales Order Number)
The Sales Order Number is allocated by the sales assistant who raises the order. It is unique within Customer
Delivery Address
Special Instructions
Date Raised
Raised By
Date Dispatched
O Order discount given Usually it will be that held against the Customer entitiy but can be over-ridden on an order by order basis

Sales Order Line

Each sales order is made up of lines that show how much of each product is being ordered. There is an average of around 7 lines per order with 10 to 30 or so of each product being ordered.

Data items Comments / Description
(Customer Account Number)
(Sales Order Number)
Key of Sales Order master
(Product Code)
(Colour Code)
(Size Code)
(Style Code)
Key of Product master
O Discount given
Product Description
Base Selling Price

Some would argue that Product Description and Base Selling Price are derived items (they come from the Product entity and therefore should not be allowed for here).

The contra argument is that we don't want every historical order line's price to change if / when Marketing decide that they are going to change the base price of a product (similarly for Product Description).

That would be crazy. We need the price and description used at the time the order was taken.

An alternative would be to create a new version of each product, every time it is changed. Then a sales order line can reference the product and version.

If versioning is required, it can be dealt with at the physical design stage, but you will need to document which entities need versioning.

Of course, there will be a cost associated with versioning in terms of more to implement, more complex testing required, reports become more complex to write, on-going maintenance is more difficult...


Used to describe / qualify the products Acme sells. See also Style and Colour.

Data items Comments / Description
Size Code
Size Name


Used to describe / qualify the products Acme sells. See also Colour and Size.

Data items Comments / Description
Style Code
Style Name

Style Description


Companies that Acme buys its products from. There are approximately 4 major suppliers that Acme uses with alternatives sources also documented.

Data items Comments / Description
Supplier Code
Assigned by the Accounts department when creating a new customer account
Supplier's trading name
Standard Delivery A description of the normal delivery period from placement of an order
Sales Contact Name
The name of our account manager at the supplier
Contact Number Our account manager's telephone number
O Credit Limit We may have been given a credit limit
Discount The normal discount we get from this supplier.
Account Number Our account number with this supplier
Telephone Number The telephone number of the normal buyer
Fax Number The number to use for faxing orders through

Go to Top
exercises on modelling process and data