database

design

basic structure of the entities

The structure of the database will obviously need to take into account the characteristics of the given data set or the importation of the data set will fail miserably. So then, this section of my design is crucial to getting any significant output of information from the database.

The field names will have to be exactly the same as those contained within the first row of the dataset unless I adjust them to what I think is more fitting - but for time's sake I'll just leave them be.

Now, the data within the data set will need to be divided up into seperate entities - this is clear - what was not clear at first, was how many different entities, I will explain below...

two possible structures...

In my opinion there are actually two equally viable possible entity structures. When I was dicsussing with my colleagues the design of the database two different solutions emerged. I will call the first one the 'two table theory' and I'll call the second the 'three table theory'.

the two table theory

The two table theory consists of two entities (hence the name). The proposed titles of the two entities are CD and Order. Within 'CD' all the fields relating to the actual CDs will be present and within 'Order' all the other fields, including customer fields will be contained. This is shown more clearly below:

CD	Order
CD Number (PK)*	Order Number (PK)*
CD_Title	CD Number (FK)**
Artist	Order Date
	NewCust_Title
	First Name
	Last Name
	Address Line 1
	Town
	County
	Post Code
	Card Type
	Card Number
	Expiry Date

*(PK) means primary key. ** (FK) means foreign key.

the three table theory

The three table theory arose from someones disatisfaction in not having a Customer entity. This person felt that clearly the majority of the fields in the two table theory's Order entity are much more closely related to customers than to orders. This structure is shown below:

CD	Order	Customer
CD Number	Order Number	NewCust_Title
CD_Title	Order Date	First Name
Artist		Laste Name
		Address Line 1
		Town
		County
		Post Code
		Card Type
		Card Number
		Expiry Date

This solution did look sound at first, and I was planning to use it. But in our discussion two problems materialized. The first and most prominent was that there was no clear primary key for the Customer entity, and the second was that the whole process of relating them was very complex.

Someone did attempt to solve the first problem, suggesting that the Card Number field be used for the primary key or that a compound key should be used. But time was moving on and there wasn't any issues with the first solution so I opted for that.

Click next to continue...

< BACK | NEXT >