I was wondering what would be the best way to store contact details of supplier and clients.
What I was thinking was that there would be
GUID, ParentGUID,Type,TypeDesc,Details
The type would be a integer,typedesc would be for cases such as others and the details would obviously be the data.
the problem that I am finding is how to have this all linked.
This being that A client -> Contact(Title, Name,Middle,Surname) -> Phone -> Alias
This could be a hierarchy case.
As of now Supplier and Client tables cannot be joined due to backward compatibility. But will be done in the future.
Also due to certain client limitations MSSQL 2005 must be assumed as otherwise there wouldn't be a problem as this was solved in 2008 with getroot() and other useful functions.
I would have probably many cases where I would search on one of the fields. And require to know the client or supplier.
I eventually used recursive CTE for retrieving the data