I wanna store some vendors (like McDonald ,etc) and it's branches in a table.
a vendor may,or may not have branches.
I have created two tables:
1- A vendors table which have name,logo, etc.
2-A venues table which have location_id, vendor_id, etc.
relationship between venue and vendor is many to one.
suppose I have added a vendor name TastyBurger which is a vendor with lots of branches in all over country.
the other day another store comes up with name TastyBurger which isn't a branch of above vendor. and we create another vendor record for it as well.
the other day the first vendor comes up to save another branch.Now here is the question: How can I determine which vendor record was associated with this?
I made effort to make a real life example to make sense. But if not,just ask to explain more.
Best Answer
As you cannot use the vendor name as identifier, since it may be duplicated (as it happens with the name of a person), you need a true identifier to be set upon creation. When someone identifies themselves in your system, they cannot identify by their name, since it is not enough to uniquely identify them. Thus you don't have any other option than assigning them any proper id when you create the record on your vendors table and identify them by that id.
For this purpose you can create an ad-hoc column (filled in its most simple approach with an auto incremental series), or use a contact email, Tax identifier or any other key that you know is unique, instead of the name.