How to relate entities in SQL through SQL tables

Question

I'm a very beginner on DB designing and I need to create a DB for a project. I can explain what I wanna do in Object Oriented terms and thankfully a DB expert would be kind enough to explain me how I can deal with this in a DB aspect .

I wanna create a User (Id, Name) entity that would have a relation with a Location entity (state, city). So in programming language i would like to have the following

class User {
String Name;
Int Id;
Location location; }

class Location {
String State;
String City; }

Could someone explain me how I can deal with this?

Answer 1

Based on the comments it looks like what you want is a Many-To-One relationship between the Location table and the User table. This means that a User will have one and only one Location, but a single Location can be assigned to multiple users. So you can see how this should look, I have included the following DDL script, or "Data Definition Language", which is the language all Database Administrators use:

Create the User table:

CREATE TABLE [dbo].[User](
    [UserId] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [LocationId] [int] NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [UserId] ASC
) ON [PRIMARY]
) ON [PRIMARY]

Create the Location table:

CREATE TABLE [dbo].[Location](
    [LocationId] [int] NOT NULL,
    [City] [varbinary](50) NOT NULL,
    [State] [varchar](2) NOT NULL,
 CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED 
(
    [LocationId] ASC
) ON [PRIMARY]
) ON [PRIMARY]

Now create the Foreign Key (FK) between the 2. A FK tells the database that you want to link data between 2 tables. That is, you cannot assign a User to a Location that does not exist in the Location table. This is done through the Id fields.

ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Location] FOREIGN KEY([LocationId])
REFERENCES [dbo].[Location] ([LocationId])
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Location]
GO

There are many good resources on the web for learning database design. The one question you will want to answer early is "How normalized do I want to make my database?" Database normalization will heavily influence your design.

One more thing: do not let your application object model dictate what your database model should be. In other words, you do not need to have a one-to-one relationship between your application objects and your database tables. It might be that way for very small databases, but using best practices for db design you will quickly see that it is a practice that is not sustainable.

Answer 2

It depends on your project requirements (Designing)

The relationship might be the following :

*User has one location and Location related to one User (One To One Relationship)

*User has one location or more and Location is related to one specific user (One To Many Relationship)

*User has one location or more and Location is related to more than user (Many To Many Relationship)

Answer 3

A table represents a relationship aka relation. Hence the relational model. Ie a table contains the rows that satisfy some parameterized statement. The parameters of the statement are the columns of the table.

table User on Name, Id, LocId
    // (longhand) "user identified by [Id] has name [Name] and is located in location [LocId]"
    // (shorthand) User(Name,Id,LocId)
table Location on LocId, State and City
    // (longhand) "location [LocId] is city [City] in state [State]"
    // (shorthand) "Location(LocId,State,City)

One gets the rows satisfying other statements by combining statements using AND, OR, AND NOT, EXISTS, IMPLIES etc. You get the corresponding tables by combining tables using JOIN, UNION, MINUS, PROJECT-OUT, <= (respectively). The DBMS can do this conversion for us.

User
    // rows satisfying User(Name,Id,LocId)
User JOIN Location
    // rows satisfying User(Name,Id,LocId) AND Location(LocId,State,City)
User PROJECT-OUT LocId
    // ie User PROJECT Name,Id
    // rows satisfying EXISTS LocId User(Name,Id)
User WHERE Name='Fred'
    // rows satisfying User(Name,Id,LocId) AND Name='Fred'

That is how tables/statements are "connected": by parameter/column names and logical/relational operators.

Only you can decide what rows you want in your tables, ie what statements you want your queries combined from.

You responded to another answer (itself confused about relationships) with some properties of the relationships, but you didn't say what the relationships actually were, & that's not enough to know what goes in the tables or what statements the rows in a query result satisfy.

Notice that this is all you need to update and query a database.

Given some statements and the situations that can arise it follows that the database will only ever be in certain states. We tell the DBMS about that via "constraints" so it can prevent other states and also optimize execution. Eg if it is always the case that EXISTS Name,Id User(Name,Id,LocId) IMPLIES EXISTS State,City Location(LocId,State,City) ie that USER PROJECT LocId <= Location PROJECT LocId then we say there is an "inclusion constraint" from User's LocId to Location's. If also {LocId} is a key of Location then there is also a FK from User's LocId to Location's. I repeat, one doesn't need this to query; states violating this will never arise from using the statements except in error.

You and other commenters here suffer from common confusions which are taught as misprepresentations of methods or unfortunately are actual parts of methods. Eg "relationship" instead gets used meaning certain kinds of constraints on the tables/relationships in a database. One confusedly says there is "a" "many:1" "relationship" between users and locations. What this actually means is that the table/relationship User PROJECT Id,LocId ie EXISTS Name User(Name,Id,LocId) between users (which are 1:1 with Ids) and locations (which are 1:1 with LocIds) has the property of being many:1, ie that many users can be located in the the same location in some database states. Then this is further confused with there being an inclusion constraint or FK from Users to Locations. Then people think vaguely that such things "connect" tables: but they constraint tables (ie the database) and are irrelevant to querying.

Read about NIAM or FCO-IM or Object Role Modeling which will tell you how to think clearly about design. (Halpin's books tell you how to map from ORM2 to ER & others without warping by common misconceptions.)