Embedded Files
Online Store explained
Online Store explained
The following schema, which is not the most efficient one due to educational purposes, describes a shop of electronic articles that operates through Internet. Therefore, the catalog of available products is extracted from a query of the DB and a HTML page is built with the information. Besides, it is supposed that a set of forms and processes to store baskets and orders have been implemented. Also, the information of the registered users is required.
The articles are identified by a code (“cod”) and there is some extra information such as the retail price of the product (“rrp”). This price is shown in the catalog to the user but it is not necessary the same price in which the product was sold at any moment. Suppose that there is an increase of the prices of the products in March. However, this product was already sold in February too. There is also information that describes the article including a miniature image (“image”,”urlimage”). Both are two possible methods to access the same image, the folder with the set of images is in the server, for instance.
The articles have also a brand, as it is shown in the relationship “manufacturer”. The articles has always a brand and only one. The objective of this relationship is storing extra information about the brand, such as the name of the company that manufactures the brand, and the logo, that can be an image shown in the catalog.
Some articles are available in the warehouse and some are due to renewal. Available articles are “stock” type, they will be part of the specialization “stock”. This must be understood as if the article is not in “stock” table, it is not shown in the catalog, or, the user is informed that the product is not available.
The articles are grouped together by categories. In the complete list of articles we find that some of them are cameras, some lenses, some tvs and some memories (SD memory cards, Compact Flash, etc.). This part of the E-R schema is showing that all the articles have code, name, rrp, image, urlimage and specifications. However, if the article is a “lense”, we have also information about the type of lense, the frame, the minumum and maximum aperture and the special elements. These attributes are specific of the lenses, but they are not part of another types of articles. For example, they does not exist in the tvs that the tvs have its own categories like panel, resolution, etc.
A special type of category is the “pack”. The “pack” articles are not real products, but they represent a set of articles that are sold together with a special price (a "pack" is a type of article that has code, name and rrp like any other article). Think about a reflex camera (1000 €) that is sold together with a lense (500 €) and a memory card (30 €): the total amount of these articles separately is 1530 € but if the client buys the pack "A001", for instance, the company can offer it at a different price, for instance 1250 €, and it will buy the three products together.
Therefore, one thing is the pack article, that is stored in “pack” entity, and another thing are the articles that are part of a pack: this list of articles grouped together is represented and queried from the relationship “PART_OF”; a pack can have various articles and the same article can be part of various packs.
About the users we will stored the typical information. Specially important are the “email”, that identify the users, the name, surnames and DNI, that are compulsory attributes, all the users have to provide this information. Besides, we store the address and birthday.
Optionally, some users will have an alternative delivery address, that means that the products will be delivered to this second address but the invoicing will be done to the residence address.
In order to avoid confusions, the town that is referred in the main and alternative address is stored in another entity. Therefore, both in the “user” and “deliveryaddress”, it is know the place of the postal addresses. For this reference, the “codm” is used, together with the name of the town: ('1225', 'San Vicente del Raspeig"), for instance.
However, the identification of places needs also the code of the provinces. This data is provided by the identification dependency of “belongs”. Therefore, San Vicente del Raspeig is identified by ('03','1225') what is obviously different to ('45','1225'), Olías del Rey, that is located in Toledo.
Finally the orders. It is supposed that the users can used the tool through the “basket”. This is a list of articles in which the user is interested in and they can be ordered or not. In fact, the basket is a set of pairs email, code of the article, plus a date when the article was chosen. This information is not related with the final orders.
The orders have a heading (“ORDER”) that informs about the number, date and user that makes the order (relationship "buy"). An order only, and always, has a client, and a client can make as many orders as he/she wants. The detail of the order, the list of articles to be bought and their prices are stored in “LINEORDER”. For example, the order 1 can contain 3 lines (1-1, 1-2, 1-3) and the order 2 can contain 2 (2-1, 2-2). Again, we need the identification dependency: the lines of the order need the number of the order they belong to.
Each line if always referring to an article (relationship "ordering") and it always specifies a retail price. Again, we focus on the differences between the price of the article and the rrp (ARTICLE.rrp) because they can be different.
Page updated
Google Sites
Report abuse