I would recommend the "lookup table" approach. This will save you from creating many tables that serve the same purpose and it will make queries easier to build since you don't have to remember...

I would say that the number of fields in a table has very little impact on database performance. I don't think you will notice a difference between the query speed of a table with just a few fields...

Since the relationship between the tables is a one-to-many relationship, you do not need an intermediate table. You only need to have the following structure:

County: ID (PK), Name, etc.
Voting...

I'm thinking that having three entities may be the way to go to keep things not overly complex while keeping things flexible too:

Company: ID (PK)
Property Type: ID, Description, Data Type,...

If the properties you are referring to apply to each record, then you can add them as fields (columns). For example, if you are tracking products you are selling, then a height, width, length and...

I would go with something similar to option 2. Option 1 would not be a noramalized database design, so I would stay away from it.

I see you requiring three entities:
1) Company Entity
2)...

Here is the SQL for what you are trying to do in one query:


SELECT Sum(Count) AS SUMofLessonsAndTests
FROM
(
SELECT count(lessons.lesson_date) AS Count
FROM lessons
WHERE...

There are several things wrong with the query.

1) You have quotes on either side of the &. They are not needed since you are looking for the string "Examiner & Instructor"
2) In the Instructor...

You still need a Student/Module table and tables to deal with the prerequisites.

What is the difference between the lecture entity and the session entity; isn't a lecture just a type of session? Same with practical session and session.

I see you removed the relationship...

Okay, I read the original assignment and drew an ER Diagram based on the assignment. After drawing the ER Diagram, I counted the number of tables required and came up with 18 tables.

There are...

What attributes will be in the academic record entity? Since you will have a credits earned attribute in the studies entity, I was thinking you could get rid of the academic records entity.
...

Instead of using the instruct_examiner table, I would recommend that you just add a record to the examiner table and one in the instructor table. People that appear in both tables are people who are...

1) The way you have your ER Diagram drawn, each studies record will have a course foreign key. From the course entity, you can find out which student is associated with the studies record. In...

Thank you for the attachment. Do you have any specific questions on how to build the queries you need? I don't mind helping, but I don't want to do the assignment for you.

Secondly, in your...

Your current ER Diagram is a good start, but let me ask you some questions that may make you want to change your diagram a little:

1) How can you track if a student wants to take a module that is...

Attached is the ER Diagram I would use. Please notice that any person is able to be a student, instructor, examiner or any combination of the three. Also note that my diagram does not include...

I reviewed the E-R Diagram and it is a good start.

I'm not sure a booking entity is needed as long as you have a booking date attribute in the test and lesson entities.

What is the Test Type...

There are three types of people:
Students, Instructors and Examiners.

I was suggesting that you use a people entity to track the name, address, phone number, etc. of all the people you record,...

If a roommate leave, you can update the BillPeople table to redistribute the amount owed; once any individual bill is split up, there's no need to refer to the table except for future bills. Of...

Just to expand on my previous comment... you could then put some code together so that after a bill is recorded, the database breaks out the bill and adds transaction records based on how the bill...

Yes, my original suggestion did not take into account how to divide the bill. Someone would have to make that determination each time a bill was entered.

I like your idea of the billpeople table;...

This is how I would approach the issue. I would have one table to track the bills, one table to track the people and one table to track the transactions:

Bills (ID, amount, date billed, date due,...

I look forward to seeing what you come up with. It should be straight forward for the most part. I think the payment tracking will be the biggest issue.

People: ID, Name, Address, etc...
Students: ID, People ID (this table identifies who the students are)
Instructor: ID, People ID (this table identifies who the Instructors are)
Examiners: ID,...

Close... here's the table structure:

TransactionID - int
Amount - Currency
TransactionTypeID - int

The TransactionTypeID will either be 1,2,3, etc. (1 for Purchase, 2 for Transaction, 3 for...

Can you explain what you mean? The referential integrity would still be intact with this method. We are simply creating a foreign key in the transaction table whose parent is the transaction type. ...

Just to expand on pritaeas's comment...

Create a number field in the "transaction" table. You will record either a 1, 2 or 3 in the field. A 1 will indicate that the transaction is a purchase...