About simple relationship and good practice

[jaryszek]

Hi,



I have two tables:





and relationship between them:


I am doing query to have all cat names and pets:



and question: it is good approach to give nulls values as Foreign key when there is not matched value?

Please help,
Jacek

[ranman256]

(your naming seems off)
tPet should be tAnimals. tCatsName should be tPets. (to hold all pets, not just cats)

tPet would only need 1 field:
PetKind

they are unique enough not to need a numeric key. The name is the key and easier to read.


tCatName (cats?) would have 3 fields:
id
PetName
PetKind

named things (like humans and pets) cannot be unique so the must have autonum IDs.
the Kind of pet would also be part of the table, but not need to link to tPets.
tPets is a lookup table to fill in the PetKind field. It does not need a relationship.

tPets only need a related table if tPets owns something....like a medical history.
tPets --> tPetHistory
because 1 pet has many vet appointments thru the years.

1 person can have many pets
1 pet has many shots...etc.

1 person lives in 1 state. He doesnt own the state...its a filled from lookup table to fill in State.

[jaryszek]

Hi ranman256,

thank you very much !
What if i have only tCatsName beucase there will be only cats (no other pets?).

It is hard to understand, can you provide any sample database with your idea?
Best Wishes,
Jacek

[jaryszek]

Hmm,

awesome link here:

https://stackoverflow.com/questions/...r-nm-relations

Best,
Jacek

[ranman256]

dont limit yourself. always leave the door open for scalability.

[jaryszek]

Hi!

ranman256,

i solveth the problem:



Now i do not have nullable FK.

But there is now new problem.
In my real database model i have Topologies (Animals) and NetWeaver language(Cat_names).

And this NetWeaver language is connected only with one Topology.
I can create NetWeaverToTopology table and do the same like with animals table.



But i will have 2 relationships from PK from Topologies_ID : one to many to Clients table and one to many to NetWeaverToTopology table.
I do not know it is good approach here but i have no other options.

And one more big problem. I can have for example NetWeaver_System which is connected also with Topologies table but not at all with NetWeaver language table.

Should i create another association table (for example NetWeaver_SystemToTopolgies) and create once again relationship from Topology_ID PK to it?

Please help,
Jacek

[jaryszek]

Anyone?

Jacek