Counterfiet Merchandise Tracking system

[rainman89]

Hi all,

Out of one frying pan and into another. Just finished my one database, and am being tasked with another. just want to see if anyone can see some problems with this design before i get going into it. This is always the hardest part for me so bear with me!
Rather than type all of the info out ill attach a photo of what i think the relates and stuff should be, problem is i cant get a M:N relationship to show in my relates...

 

[rainman89]

anyone see a flaw?

 

[boblarson]

Can you explain further what you are meaning when you say:

problem is i cant get a M:N relationship to show in my relates

 

[rainman89]

M:N is Many to many.... all i can show is a 1:M relationship.. through the one and the infinity sign..

 

[boblarson]

But to which elements are you referring to that need many-to-many? That's what I meant. I don't know which elements you are saying need to be many-to-many.

 

[rainman89]

Hi bob,
The m:m relationships that i think are right
Are
A location can have many owners, and an owner can have many locations
A Owner can have many DBA's , and a DBA can have more than one owner
arent those many to many?

 

[boblarson]

You need a junction table of some sort. I think, in essence, a Locations_Owners table which has:

LO_ID - autonumber (PK)
LocationID - Number (Long Integer - FK)
OwnerID - Number (Long Integer - FK)

and have that in between the locations and the owners table.

 

[rainman89]

I confused the hell outta myself, but this is the design that i think may be right? like i said... im confused

 

[boblarson]

Looks pretty good to me

 

[rainman89]

fastest reply ever! thanks bob. ill get to work

 

[rainman89]

Should that relationship between owner and location be different? reason i am asking is because how would more than one owner be available to a single location?
wouldnt the link table cause for a single owner id and location to be returned?
should it be
ownerid (pk) with a locationID(fk)
I am freaking brain dead today

 

[rainman89]

So i have been working on 2 databases at the same time for different things and have somehow managed to mess up majorly( i think)

how i should go about fixing my relationships and tables?

i believe that the fact that i have caseID in the other tables and do not have it linked is a major boo boo! Normalization problems maybe?
but i think i threw that in there to make it work in my data entry.

I have more time to devote to this now and that why im thinking that its completely wrong. any help would be appreciated

 

[boblarson]

Ray:

You have to decide whether Case ID makes sense to be linked to just Location or should it be linked to all 4 (Location, CandD, SearchWarrant, Owner) or just some of those. I'm not really sure what to tell you; it really depends on what is going to be tracked and how it needs to be tracked. If I understood the business rules I would have a better idea, but at the moment I'm so brain fried, I'm not sure I want to even try to understand (sorry about that).

 

[rainman89]

Hi boB<-hope i spelled it right
Dont mind ya being brain dead!

Maybe you could take a look today.
A case has one and only one location, but a location can be assigned to 1 or more cases
A location can have 0 or more owners (depending on if we know the info) and an owner can have 1 or more locations
A location can have 0 or more purchases(depending on if we have done one yet) but a purchase is only ever related to one case
A purchase can have 0 or more shoes but a shoe can only ever be related to one purchase
A location can have 0 or more CandDs performed on it but a CandD will only be related to one case
A location can have 0 or more SWs performed on it but a SW will only be related to one case
A candD can have only one disposistion but a disposistion can be done on many CandDs
A SW can have only one disposistion but a disposistion can be done on many SWs
A Purchase can have only one disposistion but a disposistion can be done on many Purchases

These are the relates i think i need. (after spending the nite thinking about them). Im still kinda unsure about them, and what is going to require what relationships to be changed to what.

 

[rainman89]

if i were to create a link table for this how would i go about assigning more than one owner to a location, and also assign this to a single case?

 

[boblarson]

1. Your note on your screenshot - If disposition is a lookup table just for the descriptions of disposition method, then it really is okay the way you have it showing.

2. To have more than one owner per location, you could have a Location_Owners junction table. It would be like:

LocOwnID - autonumber (PK)
LocationID - Long Integer (FK)
OwnerID - Long Integer (FK)

 

[rainman89]

1. Your note on your screenshot - If disposition is a lookup table just for the descriptions of disposition method, then it really is okay the way you have it showing.

2. To have more than one owner per location, you could have a Location_Owners junction table. It would be like:

LocOwnID - autonumber (PK)
LocationID - Long Integer (FK)
OwnerID - Long Integer (FK)

right but to tie that to the case what table would i use? the location table? or the locown table? thats where i get confused

 

[boblarson]

from your description -
A case has one and only one location, but a location can be assigned to 1 or more cases

I thinik it would be exactly as you currently have it. The only thing that is needed is a junction table between locations and owners.

 

[rainman89]

Alright, i THINK(it hurts ) That i have it now.
i ran into a problem when i wanted to see what owners were the same for a store and got All the owners name. some duplicated. it sucked. hopefully this will solve the problem?>

 

[KKilfoil]

Suggestion: Enable referential integrity (RI) on your relationships, to make the 'one' and 'many' side more clear.

As a matter of general practice, the foreign keys (FKs) on the 'many' side of 1:M relationships need to point to the Primary Key (PK) on the 'one' side of the join. Do not make a new field in the 'one-side' table for each join.

So, for example, your Purchase table should have a [LocationFK] field (or whatever your naming convention for FKs is), with a join to the [LocationID] PK of the Location table. With RI enabled, the 1:M will show up as soon as you make your join.

Once you go through this for all of your joins, I suspect you'll answer most of your questions. After that, come back here if you need more help