Mysql – Event Management System

MySQLphpmyadminrelational-theory

I'm working on a "Event Management System" Online Software Project where the system requires the following:

Viewers can sign up for their user account
Events can be created by the admin from the admin panel
Each event requires that the student has a set of attributes
The required attribute for an event may or may not exist in the
user's details table
When the user wants to apply for participation in an event, the
system will prompt for the missing attributes
Once the user fills in the missing attributes, the system will
accept an application for the event, from the user.

To solve this one I created the following tables.

users (user_id [PK], username, password, user_level)
userdetails (user_id [FK], name, address, attr1, attr2, attr3, … , attrN)
event (event_id [PK], event_start, event_end, event_title)
eventdetails (event_id [FK], … )

How do I relate the eventdetails and the userdetails, such that the eventdetails

requires a tuple of n attributes from the userdetails table?

I was thinking something like:

The system will add n colums to the userdetails field as soon as a new event is

created by the admin.

Am i thinking in the proper way?

Best Answer

I think you want a lookup table that shows the requirements for a particular event. In this case, generally I would also have attr1-3 in a separate table called 'Attribute' and a user_attribute table to assign attributes to a particular user_detail record. Then, you could assign a particular event to require n attributes through the lookup table-

event_attribute (event_attribute_id [PK], event_id [FK], attribute_id [FK],...).

Related Solutions

Modeling a ticket system based on a set of disparate kinds of tickets

You mentioned the term "template" in your description but don't use it in your design. So, first, you need table that lists all possible template types - template_type with id and name columns. As an example it may contains the following data:

Id    Name
 1    Removing a discontinued product(s) from online storefronts
 2    Resolving product upload error
 3    Fixing a workstation

Then you need storage for configuration of each template type - template. It contains records for each ticket_key which includes into particular template as well as reference to id column in template_type table. Now each particular ticket should have template_type_id column to reference to its template type.

And now, you can implement your authorization system by setting access rights for particular ticket_key in particular template.

EDIT: corrected according comment to the answer.

Mysql – Help with modeling this table

One question I have is how does the Company relate to a phone number? I mean, is there just a list of internal numbers, and will all of these phone numbers be associated to an employee. If they are you can simply just make your phone number table and have it relate to an employee. For example.

create table company
(
company_id int PRIMARY KEY IDENTITY, --(Primary Key)
company_name varchar(100)
)

create table employee
(
employee_id int PRIMARY KEY IDENTITY,  --(Primary Key)
employee_name varchar(100),
company_id int not null  --(Foreign Key)
)

create table phone
(
phone_id int PRIMARY KEY IDENTITY,
phone_number varchar(15), -- 555-555-5555
phone_type varchar(10),   -- Home | Cell | Work
employee_id int not null  --(Foreign Key)
)

employee_id being your foreign key to your employees table. If each employee only works for one company, then to get all of the phone numbers available for that company, you could do something like this

select * from phone as p join employee as e on e.employee_id = p.employee_id where e.company_id = 1

I hope this helps :)

Best Answer

Related Solutions

Modeling a ticket system based on a set of disparate kinds of tickets

Mysql – Help with modeling this table

Related Question