Need some help/insight on structure of database (1 Viewer)

[rhett7660]

Hi All..

I am trying to design a database that tracks everytime an employee is intered into the DMV lic confidential system. Here are the tables I think I would need... Scribbles a bunch of stuff on paper and also used the actually form to get information I need....

I have come into a few problems that I don't know how to work out.. They mainly deal with relationships..

The way i was going to set it up was have the main talble and then have a couple of talbes that are related to the main table... Or if I need to have another table in....

I have it setup with the main employee info as the tblMain......with the assumption he/she can have more then one car/one spouse/one child.. this is the part I am getting confussed on......

If you don't mind can you take a look at it..

Thanks again for your help..
R~

 

[MarkK]

My primary observation is that all people belong in the same table since they are the same kind of thing.
The tough part will be the table of relationships which relates one "EntryID" to another. Preserving all the relationship, not to mention keeping your data current, might get complicated because spouses have kids, get divorced, remarry, and have other kids, who might marry previous parents and so on.

One idea...

Table: tRelationshipType
Fields: rTypeID, TypeName
Data Like:  1,Parental; 2,Spousal
Notes: Stores the two fundamental relationship types you want to track

Table: tRelationship
Fields: relID, rTypeID, personID, relatedID
Notes: Preserves any number of typed relationships where 
       EntryID = personID and the related person's EntryID = relatedID

So, each individual in the your tblMain may have many typed relationships to other members of tblMain, but obvious problems are, to name a few:
1) for any new spousal relationship you'll need to a) add two records to tRelationship b) remove previous spousal relationships for both, which c) might cause cascading relationship removals.
2) for any new child, the count of parental relationships to add may be 0, 1 or 2, and if 2, the parents may not be members of the same spousal relationship.

But anyway, that's the direction I'd head in if it was my problem to solve.

 

[rhett7660]

Lagbolt..

Thank you............ I was thinking about that too... After asking some questions...and discussing it.. We have decided that we want to keep all the info in the database..
and have a check box (possible) that would indicate if this is the current spouse/child?

Also current vehicles etc..so if said employee had 6 cars entered but three of them have been removed we still want to see the three that have been removed... We want to keep all the data..so we are not to worried about deleting/getting rid of the old data....

With that in mine..... How else could you see us setting up the database??

The relationship part is turning into the hardest part for me to grasp...

R~

 

[MarkK]

The relationship part is turning into the hardest part for me to grasp...

...and the most important part to get right. If the relationships don't work in respect to what you want to store and retrieve, you've got trouble.
That being said, the relationships of owners to automobiles seems a simple Many-To-Many relationship since one EID might sell VID to another EID.

tblPerson...
EID(PK), First, Last, Address, ...
Notes: table of possible owners

tblVehicle...
VID(PK), Make, Model, Colour, ...
Notes: table of the things possibly owned

tblOwnership...
linkID(PK), EID(FK), VID(FK), PurchaseDate, SoldDate
Notes: characteristics of the owner/owned relationship

The people relationships are more difficult, because the different types of relationships have different inherent information and structures. Children, for instance, are always current, and the start of the relationship is DOB of the child. Spousal relationships are subject to change, and start and end dates differ from birth dates stored elsewhere.

The structure I suggested previously might work, but there might be a benefit to having seperate tables to maintain the Spousal and Parental relationships since they have notable differences.

tblPerson
EID(PK), First, Last, Address, ...
Notes: All people

tblSpousalRelated
sprID(PK), EID1(FK), EID2(FK), MarriedDate, DivorcedDate, ...
Notes: links two members of table main with details of their spousal
  relationship.  One record per marriage, but you'll have to search both fields
  (EID1 and 2) to see if any individual is married.

tblParentalRelated
prrID(PK), EID(FK), optional momID(FK), optional dadID(KF), optional spID(FK)
Notes: if parent is not present in the system, a person may have no record 
  in this table, or one or both parents might be present.  This data never
  changes.  You could relate the child to the tblSpousalRelated record
  representing the marriage they are from, but single parents and divorces
  make that seem tricky.

Representing these data structures is harder, but also probably secondary to your main objective? How much time and energy do you want to spend managing this data and for what gain?
And since there are a few acceptable ways to structure the people relationships, you'll have to decide first what your goals are, and what data is relevant to your purpose.

Hope this helps,
Mark

 

[rhett7660]

LagBolt...

Thank you for your help.. I really appreciate it.... I have come up with some tables based off of your recommendations.

I was wondering if you could take a look at it and let me know what you think...

Just to give you a background (very brief I hope)..

What we want to do is this... Track all employee's who have turned in a DMV confientiality sheet. Whether it is to add or delete a spouse/child/veh... There is no reasons given for adding or subtracting... The veh must be registered to the employee either jointly (child/spouse) or soley by the employee..

So the employee cannot add other members of the family IE sister/brother etc...

If the employee wants to delete a child/spouse/veh from the DMV Conf Sheet then they have to submit another form with the deletion.. We want to track this too..Hence the last updated

Basically.. Enter the info that is on the DMV Conf Sheet...... Time Stamp it..... If the employee comes back say 5 years later wants to add/delete search via employee number have current data pop up in a form.....

For veh have the ability to add another veh....if they are deleting a veh check off not current but the data is still available...

We CAN'T have an employee's spouse/child with different address nor can we have a veh registered to a different address. We don't track veh make/model just the LIC plate number.

I tried to do a relationship between the tables...but I don't think I have it right..... (database1.jpg)

 

[MarkK]

I don't understand your purpose. I don't see why you need three tables with almost exactly the same structure.
What is a DMV confidentiality sheet? Why would an employee file one with you?

 

[rhett7660]

Hello..

The dmv sheet is to hide home address from employees who work for the local police/county/government agencies. They would file one so that their information is kept from public access.

The reason I put the info in multipal tables...was to have this...

The main table would be the employee..
EntryNumber
EmplNumber
LName, FName MIntial, address, DriversLicNumber etc...

The second table would be the spouse
LName, FName DrivesLicNumber etc.. minus the address info

The third would be the child
LName, FName DriversLicNumber etc.. minus the address info

The fourth would be the veh info associated to the employee/spouse/child....
EmplNumber VehNumber... The employee can have say 6 veh.........1 wife.....3 children...

We want to keep track of the veh/spouse/child/employee info....

Say emply 143 entered the system as a single guy enters info with one veh..two years later said employee enters another veh.... 3 months later gets married and enters a wife and another veh..... 8 years later said employee enters a child and another veh..........1 month later has 1 veh removed and adds another...etc etc...

We want to keep track of each employee that fills out the dmv sheet..and everything that is associated with that sheet...

Report...(maybe this can help you where I am going with this)

Employee Name/Spouse Name/Child Name/Address/VehLicNumber/Last Updated..

That is all I am trying to track... I hope I am not making this harder then it should be??

R~

 

[dsigner]

You can only get so far by looking at different table structures. I think it is time that you looked at some forms and reports. i.e. what the users expect to see. Rough these out with as much involvement from the actual users as you can get. Then look at the alternative table structures. You will soon see the advantages and disadvantages of the various proposals.

 

[rhett7660]

dsigner..

I have come up with the following setup for the database... I am still very new at the creation part..........

I did a couple of test forms...with the input I gathered orginally... One of the problems I am seeing is this...

I don't know if it has to do with the setup..... When I setup a form I would have to create three seperate forms...

One with employee info and spouse
One with employee info and child
One with employee info and veh....

Do I need to change the way I have it setup? Or if I use a tabbed type form to do it? I have been trying to do a tabbed type form but my forms are coming out blank.....

R~

 

[MarkK]

Looks good. And dsigner's right too. Push it through and see what you run into. Sorry if I over complicated things for you, but I didn't get what you were up to at first.
Cheers,

 

[dsigner]

Have looked at the tables
One specific question - Why is "current" needed for spouse and child but not for main?

The form structure for these tables seems simple enough. One parent form main with two subforms and one linked but independent form for vehicle.

Because this is new to you, you see Access as the problem. It may be annoying and cantankerous but it is capable so shift the focus. Forget Access for the moment.

Use a bit of A5 paper as your screen, go through a day in the life of a user. One A5 paper screen for each task. What do they need to see, what do they need to enter, edit.
Pencil in the rectangles and get the flow of the users eye and cursor. You will have lots of screens even for a simple application like this one. Some of the paper screens may end up sharing a real screen because they are similar. Don't worry about that just now.

Then take all these screens back to the coffee pot. Identify a list of every unique bit of data so that everything is included but once only. Check this list against your tables. Change tables if necessary.

Now take the data list and work out where the info is going to come from. Same paper design will give you a set of input screens.

Then back to users to rough out reports. Not Access, use a table in a word processor to lay out data rows, headings, summaries, sort order and grouping. Remember different departments and levels may need different reports.

When, and only when, you believe that your paper system is solid go back to Access. Systems designed this way are much simpler to build and much more fun because there are less rewrites.

 

[rhett7660]

dsigner..

I reworked the datastructure.. I combined the tblspouse and tblchild into one table... So I have three tables instead of four.

tblMain
entrynumber (pk)
entrydate
empnumber
driverslicnumber
Lname
Fname
Minitial
Address
streetname
City
State
Zipcode
EmployeeStatus
Note
etc

tblrelated
sprId (pk)
entrynumber
Lname
Fname
Minitial
DriversLicNumber
Current
Note
LastUpdated



tblVeh
Vehid(pk)
entrynumber
LicPlateNumber
Current
Note
LastUpdated

Am I still missing something? I am having a hard time creating any type of query that is giving the correct info. Do I need a connecting table etc? Or is this setup correctly?

Thanks
Rhett

 

[dsigner]

Sorry about not answering earlier - been a bit tied up with other things.
Have you set up the relationships between the tables? If so it should be easy to get the queries going.
The tables look OK but then I don't know what you want to see as a report etc