Help me with this database design dilemma, /g/. I have a table

>>56353417
>tenants wants to add current addresses

Meant to say, "tenants want to update current addresses"

>>56353417
add a status field

File: tables.png (60KB, 870x771px) Image search: [Google]

60KB, 870x771px

>>56353453
I don't think you got me. See pic related. First table is the permanent address table. Then there's a complete clone of it with an optional foreign key if you modify an address.

Is this a good approach?

>>56353572
i still dont understand what youre trying to do? duplicating anything is not a good idea in a production database 3nf and all.

you either need to keep all addresses in one table and all tenants in another table and create the links between the two in a third table
or
create a view or somesuch which will pull together the permanent and temporary addresses or whatever

>>56353626
All addresses that are shared are already in a separate table. The address table is mostly used as source for autocomplete.

>duplicating anything is not a good idea

What if I get rid of the foreign key? This way if a client duplicates an address it will have higher priority than the one in the permanent address table.

>>56353651
why would your client have to duplicate an address if you can assign the same fk to multiple times?

>>56353700
Say you have this address in the permanent table:

K F C, blah blah.

Tenant will have his customers type in "KFC blah blah" and that won't pick that address because KFC != K F C. So he'll want to change that address. Also there are address that doesn't exist in our database and tenants wants to be able to add custom addresses.

>>56353732
when you mean tenant do you mean cloud computing multi-tenancy or like landlord tenant? cuz how is it possible that a landlord would not know the addresses of all the properties they own?

and you can very easily make sure that duplication does not occur by creating multiple fields eg. house number, unit number, street name, street type, etc...

>>56353796
I am talking about SaaS tenants.

>>56353826
there are many databases available including addresses from countries
e.g. https://results.openaddresses.io/
import this data into your addresses table

....So why not just make two tables
one with the list of tenants
ID (int primary key)
firstName, lastName (varchar)

And another table with addresses, that's add/read only
addressID (int primary key)
tenantID (int, same as the one in prev table, foreign key)
data [...]

That's how I'd do it anyway.

>>56353572
> I don't think you got me

As I first replied, a status field solves this problem. An address is an address. Why have two tables representing the same data, adding redundancy and potential for anomalies? Further more, you can link them to a different table, but then you have two tables, representing same structure (and address) and linked together by FK. Why not just keep everything in one table, and use a simple "status" field (could be enum, string, etc.) that references the types of address: primary, secondary, not-in-service, etc.)

>>56353862
better yet create an api for your application to validate the input against the data and you can still keep the address table

>>56353732
%like% or 'in' should help with that, no?

>>56353905
Yes, this. You just need a field that distinguishes who added the address pretty much. If you have two tables with the same fields you're doing something wrong.

>>56354103
what?
this makes the most sense to me imho.
1. the user enters an address
2. api validates address as valid
3. sent :^)
4. if the address does not already exist, insert it, if it does exist see if that is ok with your business rules etc.
5. make the reference between the tenant fk and the address fk in the third table.

>>56354144
You're validating addresses against a table in your db? What's wrong with Google geocode/places?

>>56353907
>>56353905
>>56353866
>>56353862
Sorry guys I worded the whole thing wrong.

I already have a perfect SaaS in place. The tenant shit is already done.

The address table has every single address but as I said here >>56353732. I have no solution for that. I can't keep the changes in the original table because that cannot be touched. I don't want to edit an address just because of one tenant.

>>56354190
Google geocode and places are shit. You have no idea mate. I paid for my address database.

>>56354190
either a table but a better option is an api internal or public.
using google maps apis cost cash money