Database Design Question

[telstar1]

Basic database design question... I'm designing some tables and many of them share all of the fields for "Address" information. One of these tables includes the user-profile table. Does it make sense to make a separate "Addresses" table, or would that potentially make things more confusing in that I'd have a mix of addresses and I'd have to add a column to indicate what "type" of address it is?

Does mixing the user-address info in with more public data compromise that data? I'd think it'd be better to keep it isolated.

Thanks,
Telstar

 

[Armitage]

Originally posted by: telstar1
Does it make sense to make a separate "Addresses" table

Yes

 

[telstar1]

Even if there will never be shared data between the two tables that will use the Address table?

 

[Armitage]

Originally posted by: telstar1
Even if there will never be shared data between the two tables that will use the Address table?

Why clutter up those tables with address information?
Why muck around with doing it more then once?
Build one good address table, and queries/suboutines to work with it.

 

[bunker]

Are they the same addresses?
What I mean is this, do you have one table with suppliers and their addresses and another table with customers and their addresses?

If that's the case, then keep them in their own tables.

 

[telstar1]

Good point. Thanks.

 

[telstar1]

Originally posted by: bunker
Are they the same addresses?
What I mean is this, do you have one table with suppliers and their addresses and another table with customers and their addresses?

If that's the case, then keep them in their own tables.

Nah, it's two completely different sets of data. No real crossover.
One group of addresses will be the user-information for users that register with the site.
The other group of addresses will be the location of stores that have info displayed on the site.
No crossover at all.

 

[Armitage]

Originally posted by: bunker
Are they the same addresses?
What I mean is this, do you have one table with suppliers and their addresses and another table with customers and their addresses?

If that's the case, then keep them in their own tables.

I don't see the advantage of this is.
Address information is address information. What seperates suppliers & customers is what other tables use the data.

 

[Mavrick007]

If you had users which are used as customers and stores, then it might be confusing, but otherwise, I would put all addresses in one table and then just use queries/sub-routines like ergeorge said.

You might want to add another column to the address table to state in a drop down box "user" or "supplier" and then when you do a query, just do one or the other. The addresses can be all in the same table and therefore used only once instead of typing them in multiple times. Save yourself some time

 

[bunker]

Originally posted by: Mavrick007
If you had users which are used as customers and stores, then it might be confusing, but otherwise, I would put all addresses in one table and then just use queries/sub-routines like ergeorge said.

You might want to add another column to the address table to state in a drop down box "user" or "supplier" and then when you do a query, just do one or the other. The addresses can be all in the same table and therefore used only once instead of typing them in multiple times. Save yourself some time

The data is not the same, there will be no multiple entry. He has one customer table with customer address and one store table with store addresses. Combining the addresses into one table makes no sense. In order to get the correct address out he would need to know if it's a customer or store address, then need to know which store or customer it went with.

Customer Table:
FName | LName | StreetAdd | City | State | Zip, etc.....

Store Table:
Name | StreetAdd | City | State | Zip, etc....

 

[Armitage]

Originally posted by: bunker

The data is not the same, there will be no multiple entry.

The data is the same. If it's not address data, it belongs in another table.
Whether the address data belongs to a customer or a business depends entirely one data i other tables. there is no reason to have it in the address table.

He has one customer table with customer address and one store table with store addresses. Combining the addresses into one table makes no sense.

It makes no sense to seperate them. You'll just have two tables with the same format. You can do that of course. Your choice.

In order to get the correct address out he would need to know if it's a customer or store address

No. They would have have a table listing their customers (and businesses). Then they join that with the address table to get the address.

then need to know which store or customer it went with.

Customer Table:
FName | LName | StreetAdd | City | State | Zip, etc.....

Store Table:
Name | StreetAdd | City | State | Zip, etc....[/quote]