Im new to these parts. First post in fact. That said the nerd in me actually enjoys the database class im taking and If I think I can help I will.
I've been kicking around a design concept for a few weeks now. Ive given it quite some thought. I normalized it and then started creating tables in SQL.
Heres the dillema.
I had to create my tables in a very illogical order. In order for my foreign key dependancies to work I needed to create certain tables first and more critical ones afterwards. I suppose my first question would be.... is the order by which tables are created a big deal?
Secondly... Im designing a county court system. I need 6 entities. At first i thought. No prob. There are more than a couple functions that will require their own entity.
I had 8 to begin with. The more I work my concept tho the slimmer it has been getting.
i was working with
Defendant
Court
Judge
Supervison Agent
Jail
I had a bridge entity between defendant and court as there are many defendants, many courts and each defendant has several court dates.
the bridge was Appearance
I also thought about creating Defendant as a supertype with Probationer and Inmate as subtypes.
Basically im uncomfortable with a few things. My defendant is the 1 side to quite a few manys. It makes sense. I want foreign keys that will bring up information about the judge, court, probation officer, jail that pertain to a specific defendant. However, by looking at examples in my book I'm not finding anything that resembles this.

Another thing... As the court system is as much a business as anything I thought it would be appropriate to include a financial division.
I have worked through some relational algebra equations that will calculate debt owed at any given time. However... all of these attributes seem like they should be located within my defendant entity. From Current_Date, Supervision_Startday, Supervision_End and origional fine. I am able to compute and report enough info to suit the purposes of my assignment.
Since the attributes i need to make the computations reside in the Defendant Table I dont kno if i can even make a Financial Division entity... even though it seems appropriate.
The only values i would have to insert would be... phone, address, a couple foreign keys but most of what i would want in there already belongs in Defendant.
Well... thats that. I appreciate any fresh insite or suggestions. Im not looking for anybody to do my homework. In reality I would way rather fail the class and have a clear functioning understanding than pass and not understand at all.

only if you want to define your FKs in the CREATE TABLE statements

many people put off that task and do it after all the tables have been created, using ALTER TABLE statements to add the FKs

i suppose im getting kinda frustrated by my database.
There are classes after this that get more involved and technical. I want
to take them but this project is making me question my abilities. I mean
i'm doing well so far in the class im taking. Giving it proper attention and all. I've taken tests and done really well in creating ERDs for the questions given but now that im trying to design my own hookup I dont feel so brilliant.
In my database there are a few entities that i left out that i should probably include like Prosecutor... however....
Even though there are many Defendants and more than 1 prosecutor each case is kinda self contained. Its an affair between 1 defendant and 1 prosecutor. Defendants aren't bunched together in real life. Even though in the big picture many defendants see many prosecutors it almost seems like since the court works on a case to case basis it should be 1 defendant to 1 prosecutor.
I think im over thinking all of this

right... i could totally do that too. I just look at my ERD and then look back at my SQL and as i have been putting my FKs in the create table statement I feel uncomfortable with the order i was having to create tables. It seems like the SQL create table progression should mirror my ERD but i suppose what you say makes perfect sense.
THX

It might be well worth documenting the difficult decisions you had to make and what your choices were. That should make interesting reading for the lecturer and shows your thinking process. It's also worth considering using a single user table and using a user_type field - not sure whether it would help or not in this case. Do judges etc remain constant for all the court appearances of a particular case or can they change?

Wouldn't that make an interesting Law And Order episode...

Hi
It would probably make your life easier if you defined your requirements first, then designed a Data Model and finally generated the SQL from the Data Model.

In the meantime, here's a Case Management Data Model that might help you thinking thru the problem :-
Case Management Data Model

and here's another one that looks at Judges and Courts :-
Judges and Courts Data Model

HTH

Barry

Barry - Generally it's frowned upon to just supply answers on a plate to students. This particular chap seems more than bright enough to produce his own design and, through building that design, he'll learn a lot about the subject. There's also no guarantee that a db you take from the web will match the students requirements.

mike, he didn't just "take" them from the web, he wrote them

and he did say they were intended to provoke thought

Sorry - that bit was meant to be for abcs but I didn't make it very clear. Abcs sounds intelligent enough to not plagiarise someone else's design but I certainly wouldn't say the same for most of the students who post their coursework here and just hope for an easy answer. If that wasn't the case then your NZDF solutions would be redundant.

you're confusing me with patp

i may have coined the term (nzdf), but they're his solutions

i hardly ever indulge

Wouldn't that take VAST amounts of tequila and hallucinogenic's?

much thanks to all. I have taken care to document my logic and naturally it is a work in progress.
I am beginning to see a much clearer picture of how this will all come together. I redesigned my ERD. I am fairly new to all of this and i appreciate the insite.

A couple things. For all intents and purposes I think im getting close. My first normalization attempt is still functionally in tact. I still have a composite PK composed of defendant and court. From court i will be able to establish everything that is not defendant related information. I have decided to include a financial dimmension. Seemed more than appropriate. Herein lies my last real problems. They are basically relationship related and i suppose there is more than 1 way to interpret this. I am trying to mirror the court model as closely as possible. To recreate how it is actually done. Circuit court probation officers do not supervise defendants who are processed in the District court. If you are prosecuted and found guilty in circuit court you end up reporting to circuit court probation. Pretty sure same rings true for circuit court prosecutors.
So... My county has many courts; district,circuit,family ect...There are many prosecutors who work cases but they do not jump from court to court. A district court prosecuting attorney does not try cases in the circuit court. So there are many of both but there is a condition that dictates that while there exist many they only work at 1.

Would i be correct in my assessment that this is a 1 to many relationship? I feel like I kno that it is however i keep second guessing myself. If someone could review my logic and reaffirm it or disqualify it I would be eternally greatful. Otherwise it will involve an un-necessary bridge entity.
Either way im almost there. Once i hash these relationship issues out im pretty confident that i will have this one beat.