Database design (1 Viewer)

[pwbrown]

Hello,
I’m looking for some advice on designing my database for an app I’m creating. Every time I do a design it looks wrong and I keep thinking there is a better way to go about it.

I can’t tell you what the actual app is going to do but it’s similar to a user being able to post a job and others applying for it. But a user can post a job and apply for a job. (Obviously not their own job) There will also be an admin but I haven’t finalised what the admin can do yet.

The users will have a profile, be able to message other users and leave feedback after a job has been completed.

This attached picture shows one of my many starting designs but I’m sure it’s not the right way to go about it.

The client and worker will have a lot fields which are the same but remember one user can be a client and worker.
Client and worker will have the usual stuff stored like username, password, forename, surname, gender, age, email, address and a picture.

A job would hold data such as job title, description, location, type of job, payment. Also additional information would be saved such as job completed and job paid.

Profiles will have general info of users stored including feedback as a client and as a worker (it’s different type of feedback). Also stuff like the amount of job completed and offered.

Not sure how to store messages between users but it’s not vital right now.

Any help is very welcome.

Kind regards,

Peter

 

[plog]

As a general rule, table structures shouldn't look like spider webs. That is, they shouldn't circle back and make redundant connections, it looks like yours does. Its not a perfect analogy, but a tree would be a better way to visualize a database.

First identify the trunk (main table), I believe yours is Jobs. Directly off that are Users, Feedback and Applications. That's it. You are complicating what you think is your table structure by creating entities which aren't actually distinct tables.

For example, I don't believe you should have Client, Worker nor Admin tables. That data should all be in Users with the role they have in a job defined in the Jobs table. Nor should you have 2 Feedback tables, just one and a field to designate who the feedback is from.

 

[pwbrown]

For example, I don't believe you should have Client, Worker nor Admin tables. That data should all be in Users with the role they have in a job defined in the Jobs table. Nor should you have 2 Feedback tables, just one and a field to designate who the feedback is from.

You see that's what I originally had but I didn't want lots of blank fields in a table. A client can leave feedback on a worker and vice versa but the feedback is different.
One is a rating system and a message and the other just a message.

Admin won't have most the data the other users have. Just login details and a field to identify the user is an admin.

Attached a picture of what I guess you're talking about?

Thanks for your feedback so far.

Kind regards,

Peter

 

[plog]

That's still a spiderweb. There should be only one way to transverse 2 tables. In yours,there are at least two options to get from every table to every other table.

Feedback should only be directly linked to Jobs. A client/user won't be able to leave a comment unless a job exists that connects them, correct? That means Feedback is indirectly links to Users through Jobs.

 

[pwbrown]

Ok I get you now. I guess it's the same for job app link because you can't apply for a job if there isn't a job.
I updated the new design and added a message table.

Now a few questions I hope you could answer:
1. Would I only need one table for a messaging system? I guess I would and it could hold message id, message title, message description, sender and receiver.

2. Is it wise keeping admin as part of the user table too because there will be many blanks fields for an admin record? For admin I only need to store login details and a field to notify they are an admin.

3. Would you create a separate address table for users?

4. How would you go about storing the extra information for profiles e.g. jobs completed, jobs offered

5. Say I wanted to expand and include companies being able to post jobs too, would you create a new table for companies or include them in users? I think I would have a new table because I may want workers to be linked to a company.

Once again thanks for your help.

Kind regards,

Peter

 

[plog]

That's looking better, but there's a few things to discuss--you know more about your data than I do, so I'm not certain what the right answers are. I'll get to your questions but first here are mine (I'll use letters).

A. In your relationships you need 2 instances of the User table (one to represent the clients and one for the workers). You only need one actual table, but you need it represented twice in your relationships. Clients will always be directly related to a job, but I'm not certain workers will be. I assume workers will first put in an application and a specific application will be chosen for each job. If that's the case, you would have the worker data directly linked to applications.

B. Messages shouldn't be linked as shown. Will messages be linked to jobs? Or is it a place where anyone can communicate with anyone? If anyone can talk to anyone, then you should have a third instance of users and then Messages linked to it.

Now for your questions:

1. I'd need more info. Would messages be seperate from jobs? Do you want to group messages together like in a thread? Will messages only be between 2 people or could more people come in on it? Also I'd put a date/time field in there.

2. Could an admin be a user/worker? Even if so, do you want them to be able to use the same account? Or would they need 2 accounts--one as admin the other as client/worker.? I don't think with this issue there's any right/wrong way. Each has its benefits, but answering those questions might shine some light on it.

3. No, I'd put address in same table.

4. Jobs completed would go in the Jobs table--I'd use a completion date field. If its null its not completed, if filled in its completed. What about other statuses? Do you want to know that a Job is open for applications, in progress, cancelled? If so, then you need a field for that in Jobs. What would Jobs offered entail?

5. That gets tricky. Personally I would make it so that its always a specific user and not a company posting jobs. But I would put a field in Users that is a foreign key to a new table--Companies and that way you can tie all users to a company if they are tied to one.

 

[pwbrown]

A. I haven't had to make a database this complicated before so I'm not sure how to go about doing instances? Workers will first put in an application and a specific application will be chosen for each job.

B + 1. I don't think they will be linked to a job. Just a place where people can communicate 1 vs 1 only. Again not sure on instances.
Ideally it would be better to have messages grouped like a thread but it's not necessary. Yeah there will be a date/time just forgot to put that in.

2. No admin will be separate. I was thinking of having a login table to hold all login details for every type of user then that can link to user. In the login table could have a field to specify if a user is an admin.

3. Ok

4. I'd have all that but you misunderstood what I meant. I was talking about a total number of jobs completed and a total number of jobs a client has created.

5. Ok

I'm starting to hate this database. Much trickier than I first thought.

 

[RainLover]

In Access it has a Relationships Window.

This is what I would like to see you use to explain your structure.

I think Plog is being very nice to you. He should be demanding that you do more in order to explain your DB.

But then Plod is good when it comes to this sort of thing.

 

[pwbrown]

In Access it has a Relationships Window.

This is what I would like to see you use to explain your structure.

I think Plog is being very nice to you. He should be demanding that you do more in order to explain your DB.

But then Plog is good when it comes to this sort of thing.

Damn computer crashed just as I was about to submit my reply message, can't remember what I wrote now.

Anyway I'm designing the database now and I don't start creating a database until I have a good design for it. I don't even touch Access until I have a design ready to implement.

I have been doing all my designs on paper or a whiteboard and created a diagram in Photoshop just to show one here.

The main structure has been noted in the first post and yes it could of had extra detail but I didn't think it's needed.

I know Plug is being nice and I have thanked him every time!

Kind regards,

Peter

 

[RainLover]

Peter,

We all have different ways of doing things. I just found that your diagrams did not supply a lot of information.

I like to see names of Primary Keys, Field Names Field Types etc when giving help.

 

[plog]

Ha ha, you guys should see my point history--I get five +1's in a row for providing complicated solutions, then a -1 for being curt/impatient/sarcastic, 5 more in a row for help, then another -1 for impoliteness. My constructive criticism is equal parts constructive and criticism. I just might be the leader for most cumulative points lost without being suspended/kicked off this board.

With the answers you've given me I would make the admin a seperate table and have it completely seperate from the Job relationship we have been working on. I would also make the messaging system independent of your current Job relationship.

As for #4 (total jobs by user), this won't be a table, it will be done in a query. By placing that status field I mentioned in your Jobs table you will be able to create a query to determine total number of jobs by status for each user. So that doesn't have to be in your relationship layout.

Attached is a real ugly/primitive version of what I think your relationships should be. See how I brought in 2 instances of the User tables in the Job relationship? Its the same table, it just is used in different manners. Clients are directly related to jobs and workers are related to applications which is related to jobs.

 

[pwbrown]

So you wouldn't create a login details table? It would hold the login details for every type of user.

 

[essiar]

Not sure this should be posted here but I dont see a better place and cant start a post. When I start up access, my tables fill the entire screen. No matter how small or large I make access, the table stretches with it to fill it. This happened once before and I found a post somewhere that had a very basic fix, but after searching for 3 hours this morning, I cannot find it. Can anyone please assist me. Thanks!

 

[Cronk]

essiar, I don't know your issue with not being able to start a new thread but I won't go off (this) topic to answer, which I might in a separate thread.

pwbrown, there is nothing wrong in recording login details but it is supplementary to the main purpose which I know plog is focussed on. Depending on the situation, I have recorded just the last logon for each user or a history of individual logons by each user.

 

[RainLover]

Peter Brown.

What is happening.

Have you given in.

 

[essiar]

Rainlover, I moved this discussion to a new thread within Tables named Table Fills Screen. (since I've only posted 6 times here I can't post a link). I did get one response that allows me to adjust the table size for each database individually but I know there is a general very simple step that will fix all the databases so when you open a table, it is not filling the screen. To where you can strech the table smaller or larger within the Access window, as opposed to the table being maximized no matter how large the Access window is. Does this make sense? Please reply at the new thread because I am aware this discussion doesn't belong here. Thanks!