Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm working on my first web application that will utilize a database. The basis for the application is a design approval system. I work for a company which mainly produces print and online marketing materials, most frequently magazines and websites. I'd like to have all communication run through the site and host all of our files there rather than work through email and various file delivery systems since most of them are too big to send through Outlook.

This is the schema as I've designed it so far (only the magazine part is included thus far):

The way I figure it should work is everyone is assigned a username and password. The roleid indicates if they're a writer, editor, graphic designer, advertiser, or customer. If they're a designer, writer or editor they'll get to see all of the currently unapproved magazines and see what needs to be done as well as make posts and download/upload files.

Advertisers will be brought directly to the page regarding their advertisement and see any posts that our writers/designers have posted as well as download and upload files.

If they're a customer (owner of the magazine) they will get to see all of the editorial as their own section as well as a section for where we are in regards to layout/final product and respond to our writers/designers as well as upload/download files.

If y'all can point me in the right direction I'd be really appreciative.

What about multiple owners per magazine, editorials, posts? For extensibility is better to define MxN relationships with a separate table.
–
AlexandrosMar 10 '14 at 4:19

What happens when one person is involved in multiple magazines with different roles? Are you keeping multiple revisions of the post? It appears that all of the tan colored items are probably integers. How do you have boolean columns colored? You have similar column names (owner, ownedby). Do they serve different purposes? Why do you have a join directly from User to Post?
–
Adam ZuckermanMar 10 '14 at 4:35

@Alexandros By MxN do you mean many to many relationship? I'm not sure I fully understand those but I added what I think it is and you can view the schema change here.
–
user35404Mar 10 '14 at 5:04

@AdamZuckerman You raised a good point in regards to the multiple magazines in different roles. I'm not sure exactly how to fix it though. Should I have a table that consists of userid, magazineid and role to give the role? I don't know that I was clear on what a post is. It would be similar to a thread in a forum. The topic would be 'Ad for <insert company here>' which they would click and then see a list of posts sorted by most recent. Each post within the thread could have a new attachment which would be the latest version of the ad and they could make a post with revision requests etc.
–
user35404Mar 10 '14 at 5:11

@user35404. Yes I meant many-to-many. The improved schema seems to address the issue, so I think it will cover most cases.
–
AlexandrosMar 10 '14 at 5:42