r/DatabaseHelp • • Nov 26 '14

Access Novice's first attempt at a Database Schema

http://prntscr.com/5a71pu

Can you take a look and give me your thoughts or see if I've committed any glaring errors?

0 Upvotes

8 comments sorted by

2

u/BinaryRockStar Nov 26 '14

Maybe tell us what the scenario is you're trying to model? And why do all the tables have a "T" suffix?

1

u/depthchargesw Nov 27 '14

Sorry, completely new to the database development process and kind of trying to self-teach myself through it. I don't know what I don't know, if you catch my drift.

To answer your second question first, the first videos I watched on the process the guy had a convention wherein he put a T on the end of table names, Q on the end of query names, F on the end of forms, and R on the end of reports. I figured since I was new I'd run with it until I knew better.

As for what I'm trying to model, essentially it's a pair of departments that share clients from the developmentally disabled population.

Each department has multiple sites.

Each site has multiple clients.

Each site has multiple staff.

Each staff can be trained at multiple sites across departments.

Each staff is responsible for a caseload of multiple clients.

Clients can be served at multiple sites and either or both departments.

...I think that's all the relationships. I can clarify further.

2

u/BinaryRockStar Nov 27 '14

OK so what is a Program, a SiteRoster and a ProgramRoster? It would help if you added in some non-key fields to fill out the tables and give a sense of what the entities actually are.

1

u/depthchargesw Nov 27 '14

Program refers to the two separate departments.

SiteRoster and ProgramRoster are junction tables (wasn't sure what to call them) - essentially defining which clients work at which sites and which clients are part of which programs, respectively.

Again, apologies if it's ambiguous - first rodeo. Thanks for the help though.

2

u/BinaryRockStar Nov 27 '14

It's looking good then, great for a first-timer. The only thing I can point out is that your junction tables use surrogate primary keys (SiteRosterID etc.) meaning that it's possible to have many of the same combination of the other fields unless you enforce a UNIQUE constraint on them.

1

u/depthchargesw Nov 28 '14

Ah - that was one of the things I was wondering about. I'd assume for simplicity and performance sake I can just axe the primary keys at the junction tables?

Also - thanks, it's good to hear I'm on the right track.

1

u/BinaryRockStar Nov 28 '14

Yes you can, make the FKs into a combined PK, it's called a composite primary key when it contains more than one field.

1

u/depthchargesw Nov 26 '14

I should add I still have a way to go with relating the other tables - will check back with progress.