Creating an "Events Database" -- Is my structure Correct?

[KingOf206]

So I have made a couple databases before but they have all been pretty simple. This one has proven to be difficult for me to begin.



I have gotten some advice from "orange" & "June7" and I seem to be at a road block. They have given me some reading material which i have went over. I was suggested to use the "events database" that access has as a "pre-formated" but that is too simple for my needs and I don't believe it will work.

I am trying to structure my database correctly and am not sure where to go from here.

Here is my "narrative" for what I want my database to do.

"NARRATIVE"
I am wanting a database that will keep track of my "events". Each event will have an "event location", cost, group type, address, contact name, etc. I will have a "group type" for each event, this will range from age ranges, to a theme, etc. I will also have a list of clients and each client will have a gender, name, phone #, email, etc. Each event will have many clients (there will be a capacity on each event). A client can sign up for multiple events. Also I would like an "record entered by" field in each table so that once i expand my database and have other users in it, i will know who entered which records (hence the "DataEntryUser" table at the bottom with the "?")

Here is my list of tables and what I believe to be the relationships. I don't think I have my relationships correct. What I cannot figure out is how to make the relationships to my tables more linear. IN my rough draft everything table seems to go back to the "events" table. Is this the correct structure?


Here is an image of my structure:





I have attached a basic excel spreadsheet for anyone to open and modify what I have to show me how it should look like.

EventsTableSpreadsheet.zip

I have spent some time on this and cannot figure out if my current structure is correct or if there is something I am missing

[ItsMe]

Usually, tables with one Key column is the one side and tables with multiple key columns are the many side. Values in foreign Key fields will be repeated many times.

[JamesDeckert]

I changed your relationship descriptions to the way they would actually work.
What this layout is saying is that each employee can be involved in more than one event, but each event can only have one employee.
same with group, event location and clients.
If this is not right then the structure needs to change.

RE: data entry user. You can put a combobox on each data entry form which chooses names from the DataEntryUser table or go through the lookup wizard for each enteredby field of each table that wants to get info from that table.

[KingOf206]

Ok great. When i get home later tonight, I will put this together in access and begin my table structure, then I will post pics of how it looks in access.

Questions:
-I assume that the "data entry user" table will not be connected to anything?
-Should i Put the 'DataEntryUserID" feild in ALL of the other tables?

I changed your relationship descriptions to the way they would actually work.
What this layout is saying is that each employee can be involved in more than one event, but each event can only have one employee.
same with group, event location and clients.
If this is not right then the structure needs to change.

RE: data entry user. You can put a combobox on each data entry form which chooses names from the DataEntryUser table or go through the lookup wizard for each enteredby field of each table that wants to get info from that table.

[ssanfu]

1) Don't use spaces in object names -
table name: "Event Locations" has a space
Field: "Contact Name" has a space

2) Don't use special characters:
"Contact #" has space and the hash. Better would be "ContactName" or "Contact_Name"

3) "Name" is a reserved word in Access.
table Employees: "FName" & "LName" would be better.
Tables EventLocations & "Clients": "Name" --> "EventName" would be better and more descriptive.
List of reserved words: http://allenbrowne.com/AppIssueBadWord.html

What this layout is saying is that each employee can be involved in more than one event, but each event can only have one employee.

4) You might have misunderstood what JamesDecker said/implied.
If one employee can be involved in one or more events AND one event can have one or more employees, you need a junction table.


5) Regarding table "DataEntryUser". Who can make entries to the database? Only employees? Can a non-employee create entries? If not, then why not delete table "DataEntryUser" and use the employee table?

[KingOf206]

In response to your Number's:

#1- Yes, understood
#2- Yes, understood
#3- Yes, understood
#4- Yes that is exactly true, but how would I create a "junction" table with what I have? I have never heard of that.
#5- Yes, that is a good idea, I will delte the "DataEntryUser" table and I will only use the Employee table to pull in users that make entries to this table, that is a good idea.

1) Don't use spaces in object names -
table name: "Event Locations" has a space
Field: "Contact Name" has a space

2) Don't use special characters:
"Contact #" has space and the hash. Better would be "ContactName" or "Contact_Name"

3) "Name" is a reserved word in Access.
table Employees: "FName" & "LName" would be better.
Tables EventLocations & "Clients": "Name" --> "EventName" would be better and more descriptive.
List of reserved words: http://allenbrowne.com/AppIssueBadWord.html



4) You might have misunderstood what JamesDecker said/implied.
If one employee can be involved in one or more events AND one event can have one or more employees, you need a junction table.


5) Regarding table "DataEntryUser". Who can make entries to the database? Only employees? Can a non-employee create entries? If not, then why not delete table "DataEntryUser" and use the employee table?

[JamesDeckert]

a junction table is a third table connecting two other tables. the junction table is connected as a one to many to both of the other tables. Therefore the two outside tables end up with a many to many. Don't do this unless you need to. Consider each of the relationships and decide if one to many is sufficient, or if they need to be many to many. The primary key for the junction table is a composite key combining the primary keys of the two outer tables. Or you can put an autonumber primary key instead in the junction table, but the two PK's from the outer tables must be FK fields in the junction table.

[KingOf206]

How would I know if what I have would be sufficient? . What would be the benefit of having a "junction" table in my scenario?

[KingOf206]

Ok, so I have done 2 things:

*I* With help from JamesDeckert I have now entered my tables into an Access database and I have put together the relationships I believe that I need.

AND

*II* With help from ssanfu I have added 3 "Junction tables". From what ssanfu has explained this may be the direction I need to go. My 3 Junction Tables are:


A) Clients & Events Junction: This is because 1 client may sign up for many events.
B) Employees & Events Junction: This is because 1 employee can work many events.
C) Event Locations & Events Junction: This is because 1 event location can host many events

(This does look a bit ugly, but it was the best view I could get in order to see all the relationships at-a-glance, there is a copy of this in a zip folder at the bottom of this post)




QUESTIONS:
1)Please someone let me know if my logic from A-C is correct, Have I created the correct Junction tables to define the relationships between my tables?
2)Do I need to have these junction tables in my database? And if so do I need all 3?
3)For the very last field in each of my tables I have a "Record Entered by", with the suggestion from ssanfu my plan is to use the "employee" table to pull from a list of employees that will be entering records. My question is do I need to make some sort of relationship for this?


Here is a copy of the database, I am not sure that I have things correct, but I am hoping to get some feedback from the experts as to how close I am.

Leland's Event Management Database.zip

[orange]

I looked at your relationships diagram, and have removed a few relationships. I have not tested this model against your posts. The relationships I seemed to be your originals before adding the junction tables???

You can vet/test the revised model using some sample test data and a few what if scenarios. (you can do this with pencil and paper)

Since you have placed separate PKs in the junctions, I would consider adding unique composite indexes on the combination of PK fields from the original tables.

[JamesDeckert]

>>How would I know if what I have would be sufficient? . What would be the benefit of having a "junction" table in my scenario?
a one to many relationship says that for example each Event Location can have multiple events, but for each event there can only be one location. A many to many would say that for each location there could be multiple events as well as each event could occur at multiple locations. So you need to go through the process of deciding if you need many to many for each of your relationships. If you don't then you don't want to add the additional complexity to your design by adding junction tables.

The way you have it designed now each client can book multiple events and each event can have multiple clients. Think through this process for each relationship to decide if this is correct.

[KingOf206]

Yes that's exactly right. . Each event client can book. Multiple events and each event can have multiple clients. . That is exactly. My goal!

>>How would I know if what I have would be sufficient? . What would be the benefit of having a "junction" table in my scenario?
a one to many relationship says that for example each Event Location can have multiple events, but for each event there can only be one location. A many to many would say that for each location there could be multiple events as well as each event could occur at multiple locations. So you need to go through the process of deciding if you need many to many for each of your relationships. If you don't then you don't want to add the additional complexity to your design by adding junction tables.

The way you have it designed now each client can book multiple events and each event can have multiple clients. Think through this process for each relationship to decide if this is correct.

[KingOf206]

Which tables would you recommend I add a unique index too? . I assume this will prevent me from adding the same client twice to 1 event? And prevent me from adding the same. Employee twice to 1 event?.. Is this the benefit?

I looked at your relationships diagram, and have removed a few relationships. I have not tested this model against your posts. The relationships I seemed to be your originals before adding the junction tables???

You can vet/test the revised model using some sample test data and a few what if scenarios. (you can do this with pencil and paper)

Since you have placed separate PKs in the junctions, I would consider adding unique composite indexes on the combination of PK fields from the original tables.

[JamesDeckert]

Any time you can prevent accidental duplication in a DB, it is good.

[KingOf206]

Ok so i have modified my relationships to match the picture you gave. I also did a few other things:

-I modified the 'Tbl_EventLocations" so that I cannot have duplicate "NameOfBusiness"
-I modified the 'Tbl_GroupType" so that I cannot have duplicate "GroupType"
-I also modified the "Tbl_Events" with 2 unique index's
-------I made it so that I cannot have duplicate "ClientID" attend the same event (I do not want to add a clients name twice on 1 event)
-------I also made it so that I cannot have the same employee appear twice in 1 event. Here is a pic of what I added:


Are there any other obvious duplicates that anyone can see that I should be adding?

I looked at your relationships diagram, and have removed a few relationships. I have not tested this model against your posts. The relationships I seemed to be your originals before adding the junction tables???

You can vet/test the revised model using some sample test data and a few what if scenarios. (you can do this with pencil and paper)

Since you have placed separate PKs in the junctions, I would consider adding unique composite indexes on the combination of PK fields from the original tables.