I'm still a beginner in sql and I have to transform this relation between clients and addresses and vendors and adresses. This uses 2 primary keys(1 for the vendors and 1 for the clients). My question is, is it better practice to use two foreign keys(one per ID)(second picture) or one foreign key that is being referred by the two primary keys(last picture)? Thank you very much

When I searched around, the recommended way is to use the invoice address and delivery address in the same table and use a field "type" to show what it is. For example Addresses(Company, address, city, type) Type = 1: The address is a delivery address Type = 2: The address is a invoice address Type = 3: The address both delivery address and invoce address (same address) If someone does not look at my code that uses the database, they can not know what Type 2 is. Is it delivery, invoice or both? Do you usually clarify this and how? For example, if someone wants to search the database (outside my code that calls the database) for all billing addresses, they do not know if they should use type 1, 2 or 3 to get all invoice addresses from a database client. Compared to if you had two tables, then the name of the table says what it is and whoever can easily pick out all the invoice addresses. Delivery(Company, address, city) Invoice(Company, address, city)

We have a team who designs the tables and relations for software developers. In our organization, they are pretty strict about enforcing 3NF normalization - which to be honest, I agree with given the size of our organization and how the needs or our clients change over time. There is only one area I'm not clear about the reasons behind their design decision: addresses. While this mostly focuses on addresses in the United States, I think this could apply to any country that does this. Each piece of an address gets its own column in the addresses table. For instance, take this gnarly U.S. address: Attn: Jane Doe 485 1/2 N Smith St SW, APT 300B Chicago, IL 11111-2222 It would get split up in the database like this: - Street number: 485 - Street fraction: 1/2 - Street pre-directional: N (North) - Street name: Smith - Street type: ST (Street) - Street post-directional: SW (Southwest) - City: Chicago - State: IL (Illinois) - Zip code: 11111 - Zip4 Code: 2222 - Country (assumed to be U.S.A.) - Attention: Jane Doe - P.O. Box: NULL - Dwelling type: APT (Apartment) - Dwelling number: 300B And there would be a few other columns related to rural routes and contract routes. Furthermore, our specific application will likely have a few international addresses in it. The data modelers said they would add columns specific for international addresses, which would be the normal line 1, line 2 fields. At first I thought this was WAY overboard. Researching online repeatedly refers to using address line 1, 2, 3 and possibly 4, then splitting out city, region and postal code. We do have one use case for our new application where this granularity is beneficial. We have to validate that the user is not creating a duplicate business, and checking the address is one of the validations. We _can_ get it to work with address line 1 and 2, but it would be more difficult. As for our specific application, we need to store multiple kinds of addresses for businesses and people (physical, mailing, shipping, etc). We _might_ need to generate printable form letters, but that requirement hasn't been discussed so far. Some other things applications in our organization need to support: * Auditing (with full history tables) * Printing mailing labels * Generating printed forms * Reporting (for national and regional governments) While our application might not be doing everything that every other application is doing, splitting addresses into multiple components is an _enterprise standard_ where I work. Regardless of whether our application would benefit from it, we are forced to do this. Semi related StackOverflow question: [Where is a good Address Parser](https://stackoverflow.com/questions/518210/where-is-a-good-address-parser) which was closed, but illustrates how difficult parsing addresses can be. In order for me to better understand their design decision, and to sell our client on the idea... **What problems are solved by splitting the street address into individual columns?** Bonus points for anyone who has implemented a system like this, because they ran into problems.

I just created a PostgreSQL database on my laptop (following these instructions ), which is running Linux CentOS 7. Now I would like to understand what is the hostname address of my default database. I thought it was localhost but it's not. I want to get to know the name or the IP address to use with the psql -h command. Basically, running this psql -h HOSTNAME command should let me have the same result of the psql command alone, that is letting me connect to my database. What is the **hostname address** of my default database? > > service postgresql status states: > ● postgresql.service - PostgreSQL database server Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled) Active: active (running) since Thu 2017-03-30 14:40:30 EDT; 1 day 1h ago Main PID: 2256 (postgres while both psql -h 127.0.0.1 and psql -h 127.0.0.1 -U davide produce this message: > psql: FATAL: Ident authentication failed for user "davide"`

How do I parse Address into Street Number, Name, Type, Unit Number? Is there a function which will conduct this? 2280 MEYERS AVE ALDERGROVE AVE & S VINEWOOD ST SIMPSON WAY & N HALE AVE 412 E WASHINGTON AVE #3 AVOCADO AVE & LEMON ST 572 N TULIP ST 1030 HAWAII PL 500 N MIDWAY DR A-E, ADMIN

Requirement: user can add single ips or ip address ranges to a database table(for example permissions table having ipAddresses of inet[] type). I need to query that a single ip is contained in the array of ipAddresses field which can be array of ip addresses or ip address ranges or both. I have tried to add where query like this: WHERE "ipAddresses" @> '192.168.1.5'::inet and also like this WHERE "ipAddresses" >> '192.168.1.5'::inet but no luck. Thanks in advance. id uuid NOT NULL gen_random_uuid() permissions uuid[] ipAddresses inet[] createdAt timestamp with time zone now() updatedAt timestamp with time zone now() source: pg array functions and pg network address functions

Let's say for instance that I want to parse these addresses for the Chicken Ranch Chicken Ranch 10511 Homestead Rd Pahrump, NV 89061 Chicken Ranch 1600 Pennsylvania Avenue NW Washington, D.C. 20500 In both of these cases, I'd like to get rid of Rd and Avenue. For instance in the first case, I'd like to get "Homestead", and in the second "Pennsylvania". Not every address has a designation like this though.

I'm using Django with Postgres, and I'm looking for a field/datatype to store address components that come from Google Places API in this format: "address_components": [ {"long_name": "27", "short_name": "27", "types": ["street_number"]}, {"long_name": "Dromore Crescent", "short_name": "Dromore Crescent", "types": ["route"]}, {"long_name": "Westdale South", "short_name": "Westdale South", "types": ["neighborhood", "political"]}, {"long_name": "Hamilton", "short_name": "Hamilton", "types": ["locality", "political"]}, {"long_name": "Hamilton Division", "short_name": "Hamilton Division", "types": ["administrative_area_level_2", "political"]}, {"long_name": "Ontario", "short_name": "ON", "types": ["administrative_area_level_1", "political"]}, {"long_name": "Canada", "short_name": "CA", "types": ["country", "political"]}, {"long_name": "L8S 4A8", "short_name": "L8S 4A8", "types": ["postal_code"]} ], "formatted_address": "27 Dromore Crescent, Hamilton, ON L8S 4A8, Canada", "place_id": "ChIJtZiMplGbLIgRcGQ4Anc337s", The goal is to allow users to see this row in their results if they search for westdale, L8S 4A8, l8s4a8, 27 dromore, hamilton, etc. How should this data be saved? 1. Save it as-is in jsonb? There's a lot of duplicate text, for instance 'long_name' and 'short_name' keep appearing over and over, wasting space. 2. Strip all the keys and only keep the actual meat of the components in a varchar[], ex. ['27', 'Dromore Crescent', 'Westdale South', 'Hamilton', 'Hamilton Division', 'Ontario', 'Canada', 'L8S 4A8']? 3. Normalize this data in layers of foreign-key related tables? Four tables are required to normalize this: address_to_component, component, component_type, component_to_component_type. (I actually started with this, but couldn't wrap my head around how to go about querying it.) 4. Something else?

We are designing a DB for a limited functionality social networking application. Users (Registered Users), Retail Outlets, etc are several entities we need to store. We need to store Address of each of these entities. Also we need to store latitude and longitude of Registered Users, Retail Outlets, etc. Initially we designed the tables as shown below. We created a separate Address tables as the format of the address will be same for Users, Retail Outlets and other entities. User(UserID, UserName, AddressID) RetailOutlet(RetailOutletID, OutletName, AddressID) Address(AddressID, CityID, AddressLine1, AddressLine2, Latitude, Longitude) Now we realized that we need to search for Retail Outlets near to User's current location(CurrentLatitude, CurrentLongitude) So we would need to run a query like SELECT OutletName from RetaileOutlet INNER JOIN Address ON RetailOutlet.AddressID = Address.AddressId WHERE Address.Latitude BETWEEN CurrentLatitude - 10 and CurrentLatitude + 10 and Address.Longitude BETWEEN CurrentLongitude - 10 and CurrentLongitude + 10 Now we think that above query would run slow because the number of users will be much more than the number of retail outlets. From DB Design/Performance perspective which of the following should be done. 1. Create index on Latitude, Longitude in Address table and on AddressID in RetailOutlet table. So there would be no change in table structure. 2. Move Latitude, Longitude from Address table to RetailOutlet and User tables. Also create index on Latitude, Longitude in RetailOutlet table. So the table structure would be as shown below. User(UserID, UserName, AddressID, Latitude, Longitude) RetailOutlet(RetailOutletID, OutletName, AddressID, Latitude, Longitude) Address(AddressID, CityID, AddressLine1, AddressLine2) 3. Change table structure as shown below and create index on Lat, Lng in UserAddress table and on UserAddressID in User table. User(UserID, UserName, UserAddressID) UserAddress(AddressID, CityID, AddressLine1, AddressLine2, Latitude, Longitude) RetailOutlet(RetailOutletID, OutletName, AddressID) RetailOutletAddress(RetailOutletAddressID, CityID, AddressLine1, AddressLine2, Latitude, Longitude) 4. Introduce DB redundancy by copying Lat, Lng from Address table in the RetailOutlet table and create index on Lat, Lng in RetailOutlet table. With this option there will be no change in Address table and we will be able to use it for storing User address without change. Also we may be able to use it for storing Address of other entities. User(UserID, UserName, AddressID) RetailOutlet(RetailOutletID, OutletName, AddressID, Latitude, Longitude) Address(AddressID, CityID, AddressLine1, AddressLine2, Latitude, Longitude)

I want to design an "Address" model for all types of entities like users, businesses, etc. I have two types of main models: one is User and the other is Business. Each one has different address types like below. ### User 1.Contact Address 2.Billing Address ### Business 1.Contact Address 2.something So I created an address model with an addresstype column like this ### Address id addresstype user addressline1 addressline2 Relationships: * User – One to many –> Business * User – One to many –> Address (User Column) Now using the above relations, addresstype and user columns will be in a relation, but Business address is not relatted with address. How can I design this one in an efficient way?

I have two tables: addresses and locations. Locations zipcode city county state Addresses line1 line2 city county state zipcode Posts Id Address_id Body All my users are required to provide at least a personal address and some need to provide the business address. My users can create two types of posts: first requires to have a full address and the second requires just the location (state, city, zipcode). Now, the issue comes because I need to allow searches for posts based on location. The search is always based on city state and zipcode (not street numbers). Does it make sense to place all the addresses inside the Addresses table and for the posts that need just the state, city and zipcode to leave the other fields blank? Would this slow down posts searches based on addresses? Would having in my DB 5.000-10.000 users and maybe 200.000 addresses really slow down the process of searching for first type of posts based on the address?

I have a list of addresses which need to be updated in SQL Server. How can I go about adding the Suite # to the addresses which are missing it? Address 1 Burton Hills Blvd, Suite 250 1 Burton Hills Blvd 1 Burton Hills Blvd 1 Burton Hills Blvd 1 Burton Hills Blvd 1 Burton Hills Blvd 1 Ford Pl 1 Ford Pl, Suite 5B 1 Kish Dr 1 Park Center Dr, Suite 11 1 Park Center Dr

I'm new here, so I'll keep my question straight and short. I am building a webapp that lets users select some product, submit their details and place an order. Currently, my ORDER table contains only one field for the address. I was planning to write code to assimilate all address lines to one, including the state, city, etc. I will then add functions to retrieve the address field and parse it for state, city, etc. Is this a good approach, or will the database be faster if there is seperate field for states, cities and the like?