Database design based on a list

Question

Can anyone help me to design database/table based on below criteria?

An e-commerce website is required which will allow visitors to browse, search and buy films. The following business logic applies:

1. Each film can be available in DVD or Blu-ray formats with different stock codes and prices. Additional formats may be added in the future by the website administrator.
2. Films should have a title, description, year they were released and a “star rating” out of ten stored against them.
3. Films are associated to none or more actor and actors can be associated to none or more films as some films may be documentaries (with no actors).
4. Films can be associated to one or more genre (such as action, adventure, Sci-Fi, etc).
5. The number of genres and actors may change so the website administrator needs to be able to add/edit as many genres and actors as they like over time.
6. Visitors of the website should be able to find films by browsing by actor or genre. When they do they should be able to see a list of all films that are associated to the actor/genre they have selected.
7. In order to buy from the website, visitors must register their details to become a user.
8. Users will have one or more addresses associated to their account. When they log in to the system in future all of their previously entered addresses should be available for them to select for their latest order. They should also be able to add a new address to their account at any time.
9. When ordering the user will select one or more items from the available films (in a particular format). They will need to select a billing and deliver address from those they have previously entered and pay for their order by credit card.
10. As the prices of the products can change over time the system should record what the price of each of the items in their order was at the time when they purchased as well as the total price of the entire order.
11. Tracking of stock levels is not required – all products can be assumed to be in stock all of the time.

Answer 1

If this is homework, or a class project, then you really need to start learning about normalisation. Take a look at the article on wikipedia or this introduction on the MySQL site

If this is a professional project, then you need professional help to design/develop your e-commerce site.

Answer 2

Here is something I could come up with, hopefully it should satisfy all the criteria mentioned in your requirements. I was designed in SQL Server as I do not have MySQL on this machine.

Steps to design the database (entity relationship modeling)

1. Identify the entities from the requirement. Entities are objects that hold information (usually denote real world entities like person, car, bank, employee, etc.). In your case, the entities identifiable are: Film, Actor, User, Order
2. Once you have identified the entities in your requirements, get down to the deciding the attributes (or properties) of the entities. The attributes are something that you associate the entity with. For example, one would identity a car by its manufacturer, model, color, engine capacity, etc. In your case, the attributes for the film entity would be Name, Genre, ActorInFilm(s), Format(s), Price
3. Identify the relationships between the entities. In your case, film has a relationship with actor. The relationship is: One film can have zero or more actors. And, one actor can act in one or more films. Thus film and actor are related.
4. Identify the cardinality of the relationships. Cardinality can be explained in simple terms as how many instance of the entity participate in the relationship. For example, a employer can have 1 or more employees. And an employee can be employed by only one employer. In this case, there are 2 entities: Employer and Employee. They share the relationship employ. In your requirement, Film and Actor are the entities sharing the relationship Acts in (Actor(s) acts in Film). So the cardinality in this case will be one to many (Film to Actors)(one Actor can act in many Films) and zero to many (Actors to Films).
5. Once this part is done, you have your zero normal entity relationship diagram. Then comes the normalization. You can read about it on another post here.
6. After you have normalized the entity relationships (upto 3rd normal form is usually sufficient), you can implement the database design in the SQL design software (MySQL, etc.)

The best way to do the above steps is to take a sheet of paper and write the entities and attributes in a tabular format and then link them to other entities (to denote relationships).

You can refer any good book on database concepts (including normalization) or just search on google (keywords: database, normalization, database design, entity relationship modeling, etc.). What I have explained above is very brief, you will need to discover the rest of the database concepts yourself.

Entity relationship diagram is often abbreviated as ER diagram.