Any help please - database modeling

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mawj
Forum Newbie
Posts: 6
Joined: Thu Jan 14, 2010 8:22 am

Any help please - database modeling

Post by mawj »

I am a new web developing trainer

and I am doing something to learn from

I started building the tables and I need help to see if I did something wrong

I will explain the project, list the tables and wait for help please

Project Summary

"signin page"
- username & password, userID may be admin or a simple student ) (nothing else).

if username and password are valid, if user is admin redirect to admin page

if user is student redirect to student page

admin page Contains:
- Search about student (this will select everything about this student)
- See All information about students Including payments amount,
- ability to add/delete/modify new student
- ability to graduate the student, the student has finished studying

student page Contains:
- Main Information about him Including ( Student table attributes in addition to Degree [bachelor, Master, Doctor], and Major)
- Link for Fees ( Degree cost, payments)
- Link for E-books ( books related to this major under this degree)

Degrees' Costs are mainly constant, but they may differ the next year

I have built the tables, but I think I missed something , can we discuss them together?
thank You

Tables

----------------------------------
Student table

UserID primary
password
Name
date of birth
telephone number
Country
Email
UserLevel
----------------------------

Marjors Table

Major ID primary
Major Name unique
----------------------------

Major selected

Major ID primary
Degree ID primary
user ID primary
Registration Date [current Date]
----------------------------

Ebooks selected

Major ID
Book ID Primary
----------------------------

Ebooks Table

Ebook ID primary
Ebook Name
Ebook Description
Download counter
----------------------------

Degree Table

Degree cost
Degree Name
Degree ID Primary
----------------------------

Payments table

User ID
Degree ID
Payment ID
Payment amount
payment date
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Any help please - database modeling

Post by JakeJ »

This is a bit too much to ask for in forum. I strongly suggest you go through the many tutorials available for both php and mysql.

Start with http://www.w3schools.com or just google for tutorials and you'll find a wealth of information.
Brian Swan
Forum Newbie
Posts: 17
Joined: Thu Jan 14, 2010 11:56 am

Re: Any help please - database modeling

Post by Brian Swan »

I agree that the database design process is complex. I do, however, think this blog post does a good job of providing insight into the basics: http://sqlblog.com/blogs/buck_woody/arc ... ocess.aspx

Hope that helps.

-Brian
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Any help please - database modeling

Post by califdon »

Especially since you will be using this to prepare you to deliver instruction, I recommend that you study some fundamentals of data modeling. It should always begin with identifying the entities that will be represented in your data model. These should be written descriptions of exactly what an instance of the entity represents. If you have trouble expressing the definition, you probably haven't properly identified the entity. As a retired college instructor of database classes, I consider this the rock solid foundation for designing any database. You might find a tutorial I wrote for a different forum to be useful: http://forums.aspfree.com/microsoft-acc ... 08217.html. Just glancing quickly at the tables you've shown, most appear to be reasonable, except for the 'Ebooks Selected' table, also I don't quite understand what relationship you have in mind between Majors and Degrees. In any case, only after generating a written definition for all your entities, the next step is to define their relationships to each other. Again, this should be in writing, such as: "A student may, over time, work toward many degrees; a degree may be associated with many students."

As a matter of teaching style, my firm opinion is that you should start out with the concepts of entities and relationships, before you ever mention tables and fields. This will reinforce the idea that tables are merely the implementation of the data model. A common error made by students is to immediately start thinking in terms of tables and fields and keys, when they have not yet established clear mental images of what it is they are trying to represent.
Post Reply