Creating entities and attributes

Good day,

We are developing a website to provide textbooks to parents, and after finish it as HTML/CSS website (helped a lot in these forums!!), now we have to deal with creating a database to load the information.
This is the idea:

  • we serve different schools (we make agreements and they let us know their textbooks lists, we publish them in our website and parents purchase textbooks online from our website)
  • each school has grades (first, second …). We have here 12 school grades and usually 2 pre-school grades (for small people)
  • some schools has one class per grade, but others have more, identifying them, for instance: 1A, 1B, 1C … In other words, different shools have different number of classes (groups of students)
  • each school publishes each year the list of textbooks for every grade and class, for instance: textbooks for 1A, for 1B, for 1C … and so on
  • sometimes textbook list for different classes for the same grade (in the same school) are the same, other not. For instance texbooks for 1A = 1B, but 1C is different
  • each textbook has title, code and price

When parents go to our website to purchase textbooks for their children, they go to the page where are all schools we work with:

  • they select one school
  • they select one class from a list (for instance radiobutton)
  • with this information, a query shows the list of textbooks suggested by the school for this specific class
  • is it possible for parents to select one or more textbooks to purchase
  • before checkout, a contact e-mail, phone and student name have to be provided by parents

Is it possible to suggest me what to consider entity and attribute, and where to introduce keys?

Thanks a lot!!

Good day,

I have made my first database model. here the result (Visio 2010 file):
www.libromania.cl/database/model.vsd

Here what I thought (and questions):
1.- Entity “Schools” to register all schools working with us
2.- Entity “Grades” to register all grades for all schools (because even when we have 12 school levels in all schools, some schools have pre-school grades, and other have more than one class per grade: 3A, 3B, 3C …)
Should I considerate Schools as an attribute of Grades and put all in the same table? For instance:
ID_Grade School Grade
1 Saint Peter’s 1A
2 Saint Peter’s 1B
3 Saint Peter’s 1C
And so on … and then for every school all grades.

3.- Entity “Books”
List of books for sale, including title, ISBN code and price.
It is linked with “Grades” in order to be able to know which books are related to every grade and shool.

4.- Entity “Purchases”
To register purchaser contact information gathered from website (student name, email and phone).
It is connected with “Grades” to be able to register also the student grade and school.
It is connected also with “Books” to be able to register all purchased books (not necessary all books suggested by the grade and school)

Is it ok to have a link between “Purchases” and “Grades” and also between “Purchases” - “Books” - “Grades”? I guess one could be redundant.

Identity “Transactions”
To register transacion code (for instance from Paypal) and also the total payment (currency)

Identity “Payments”
To store the different payment methods (Paypal, bank account, …)

I’d appreciate any help!!

1.- Entity “Schools” to register all schools working with us
2.- Entity “Grades” to register all grades for all schools (because even when we have 12 school levels in all schools, some schools have pre-school grades, and other have more than one class per grade: 3A, 3B, 3C …)

Just an unimportant detail, but isn’t this entity ‘Classes’ rather than ‘Grades’? I might be wrong (english is not my native language) and it’s not that important anyway.

Should I considerate Schools as an attribute of Grades and put all in the same table? For instance:
ID_Grade School Grade
1 Saint Peter’s 1A
2 Saint Peter’s 1B
3 Saint Peter’s 1C
And so on … and then for every school all grades.

No. Unless (maybe) you only store the school name in your database. Do you?

3.- Entity “Books”
List of books for sale, including title, ISBN code and price.
It is linked with “Grades” in order to be able to know which books are related to every grade and shool.

No. “Books” should contain the books info, like you say. But it should not be directly linked to “Grades”, because between Grades and Books there is a many to many relationship (one book can be used in more than 1 school/grade, one grade uses more than 1 book).
So you’ll need another table, GradeBooks or something like that, containing the gradeID and the bookID, one row for each book used in that grade.

4.- Entity “Purchases”
To register purchaser contact information gathered from website (student name, email and phone).
It is connected with “Grades” to be able to register also the student grade and school.
It is connected also with “Books” to be able to register all purchased books (not necessary all books suggested by the grade and school)

I would call it “Customers”, or “Students” (depends: does each student have to register? Or is it possible that parents register to buy books for their children? And what if they have more than 1 child, and those children go to different grades?

The ‘connection’ with Books would be an Orders table and an Orderdetails table, I think?

Identity “Transactions”
To register transacion code (for instance from Paypal) and also the total payment (currency)

Identity “Payments”
To store the different payment methods (Paypal, bank account, …)

This would be info to be stored in the Orders table.

Since my last post I have been thinking more, and now I have simplified the model:
www.libromania.cl/database/modelv2.vsd

1.- Classes, with info about schools and classes per level

2.- Books, with info about every book for sale

3.- Lists, where manually create lists of books defined per every class in every school

4.- Purchases, with all purchase information.

I still have an important question. I am not sure how to store which books were purchased in every sale, as it is not mandatory to purchase all books defined for every class.

I hope to receive a help!!!

Hi Guido,

Thanks a lot for answering!!!
After post the first diagram (which you reviewed), I keeped thinking and reading more, and made the second one, already posted now.
I have checked your answer and I’m glad to have reached some of your suggestions. Let me check it one by one:
1.- you are right, I replaces “grades” per “classes”. I wrote ok in the paper version but then did it wron in Visio version :slight_smile:

2.- I think to store both the school and also the class is important, because different schools have different classes names and quantities. Please let me know if this is right or not.

3.- yes, I did that. Now I have one “Lists” table with info from “Classes” and “Books” (now containing schools and classes names). This table contains the suggested books per every class in every school working with us. It has to be filled manually I guess, according to the lists make by every school.

4.- Website is thought for parents purchasing textbooks lists for their children.
In this first version we are not registering customers, because we want to make it as easy as possible to parents.
We think to add a password could be annoying for some of them, specially if they are not very related with computers.
If we do not add a password, we cannot display stored information when they enter again.
So, the disadvantage here is they will have to purchase textbooks for every child one by one, and type the contact information every time again. As it is once per year and for a couple of children, we think it is not too hard.
The idea is to have an HTML webpage with schools and classes, and based on selection, display the textbook list accordingly.

In the new version of the model posted, I merged “Purchases” and “Payments” tables, as there is one payment per every purchase.


My great concern is how to store the purchased books in every transaction. I guess I should have another table with one extra row per every book purchased, linked with “Purchases” table . Is it what did you say when suggested to add “Orders” and “Ordersdetails” tables? Could you please let me know which fields are you considering for them?

Thanks a lot again!!!

I don’t have Visio, so I’ll have to do without the diagrams :slight_smile:

What info about the schools do you store? If it’s more than the name (for example address, telephone, contactperson, etc), you really should make two tables, Schools and Classes, otherwise you’d have the same data stored in multiple rows.

ok

Ok.
Do try to keep database design and technical solutions (manual input) separated. You start by making an inventory of all user types of the system and what actions they will perform (use cases), then you identify the entities (which will become tables), their attributes and the relationships between them. Some normalization and you’ll have a ERD (entity relationship diagram).
Once you have that, you can start thinking about how to implement the application.

A Purchasesdetails table, with a row for each item bought during that purchase. This will give you the opportunity to let people buy different books in one purchase.

Ok.

See answer to point 1 above.

Ok. See also the answer to point 3 above.
As a technical solution you could provide an admin screen to input the lists manually by every single school. Or the possibility to import csv files that contain the lists. But that has nothing to do with the database design.

ok

Take a look at cookies.

One by one? Really you don’t mean that? Why not let them choose all the books they want to buy and put them in a shopping cart. That way they can make one purchase and type their contact info only once.

Ok. If the list is very long, you might want to add a search/filter function.

ok

yes

At least:

PurchaseID
BookID
Quantity
Price

The last one might seem redundant (I’m sure that info is stored elsewhere as well (the Books table for example)), but prices might change over time, so to keep a history of the info at the time of purchase, I would store Price here as well.

Hi Guido,

Thanks a gain for your help!!!

I have included all your instructions. Here the results.
Please keep in mind this website is only for parents from specific schools, purchasing the texts defined by the school for every class.

Here the image of the tables, as you requested:
http://www.libromania.cl/database/modelv4.jpg

Tables:

  • Table Schools

  • Table Classes

  • Table Lists
    Here I have a question.
    As each list per class means a group of books, I cannot add a correlative index ID_List per each book of every list, as in this case I wuold have many different ID_List per list. So I considetrated two variables:

  • Correlative_List: one number per each row
  • ID_List: one number per each list, it is, per every group of books defined per class/school

Please let me know what do you think.

  • Table Books

  • Table Orders
    As you suggested, to have a table to register one row per book sold.
    I included “book_price” to register the price paid per book, as it will be different during the time in Books table.

  • Table Purchases
    As you suggested, a second table to register all information about purchase. Some fields are information I expect to receive from PayPal and other online payment services, don’t pay attantion to them, as they likely will change later.
    I added foreing key from Classes and Customers tables in order to be able to have reports considering fields from those tables.
    Is it ok?

  • Table Customers
    To store information delivered by parents during the purchase process (contact infiormation and student name).
    It could be also within Purchases table, but I prefer to have it out.

I really appreciate your help. Thanks a lot!!!

A few considerations:

  1. Lists: I wouldn’t create a listID as PK, instead I would use a PK composed of the two columns ClassID and BookID.

  2. Books: what is that FK1 (ID_Grade) ? A leftover from a previous model?

  3. Books: Since the ISBN number is unique, you could use that as PK instead of a autoincremental BookID column.

  4. Purchases: that FK1 (ID_Class) means parents won’t be able to buy books from more than 1 list in one purchase (they might have children in different schools/classes). It’s not wrong, but it’s a constraint that has an impact on the use of the application, so you must be aware of your choice.
    If you need to make certain reports, all you have to do is join some more tables to get there.

  • Table Customers
    To store information delivered by parents during the purchase process (contact infiormation and student name).
    It could be also within Purchases table, but I prefer to have it out.

A Customers table would be the right thing to do if your customers could registrate, and thus make multiple purchases. Since you don’t have a registration of users, you will get duplicated data in the Customers table, because they’ll have to enter their contact data every time they buy something on your site (as you said yourself). That makes the customer data dependent on the Purchase entity, and doesn’t need a separate table. But if you prefer it that way, it’s no problem.

Hi Guido,

Here an updated diagram:
http://www.libromania.cl/database/modelv5.jpg

About your comments:

  1. Lists: I wouldn’t create a listID as PK, instead I would use a PK composed of the two columns ClassID and BookID.
    I’m afraid I don’t understand. Could you please give me an example?

  2. Books: what is that FK1 (ID_Grade) ? A leftover from a previous model?
    Yes, sorry. It was already deleted.

  3. Books: Since the ISBN number is unique, you could use that as PK instead of a autoincremental BookID column.
    Good point. The problem is this information is not always available, so I’d like to have an alternative key just in case.

  4. I see. Even when indded this is the idea (parents purchasing books from one class/school per purchase), maybe it will be a problem if in the future we implement a shopping cart, it would not be true.
    Let’s imagine we delete this FK2 ID_Class from Purchases table. Would it be also possible to connect Purchases table with Classes table in order to know from which Class is the purchase?

Thanks a lot!!!

Each class can have only 1 list, right? So the lists are already identified by the classID. No need for a listID.
Create the Lists table with only two columns: classID and bookID. The primary key will be classID+bookID. classID and bookID will also be foreign keys to their respective tables.

Good point :slight_smile:

No, you’re right, some info is missing. Move the FK on ID_Class from Purchases to Orders. That way you’ll have the connection with Classes and still have the possibility to order books from several lists in one purchase.
It isn’t the purchase that is linked to the class, but the single books.

Hi Guido,

Once again, thanks a lot for your help!!

Here the current version of the diagram:
http://www.libromania.cl/database/modelv6.jpg

When you said:
The primary key will be classID+bookID
How can I implement it?

Move the FK on ID_Class from Purchases to Orders
Done

One last question. I understand the primary keys in each table but Lists are autoincremental integers. Could you please suggest what kind of variables to use in other cases?

Thanks again!!!

Looks good :slight_smile:

What database are you using? MySQL?

The only table that hasn’t got an autoincremental PK is Lists (at least in your diagram) :slight_smile:
In the other tables I think autoincremental integers are just fine.

Hi Guido,

Yes, I’m using mySQL.

Sure, for PK/FK integers are fine. My question was for other text fields. Do you think varchars are ok?
What about for currency fields?

Thanks a lot!!!

Hi Guido,

I am working with MySQL.
My only last question is:
Each class can have only 1 list, right? So the lists are already identified by the classID. No need for a listID.
Create the Lists table with only two columns: classID and bookID. The primary key will be classID+bookID. classID and bookID will also be foreign keys to their respective tables.

If this table has two columns: classID and bookID, where does the primary key (classID+bookID) exist?
I gues I do not understand what do you mean when you say “classID+bookID”.

Thanks a lot!!!