If you have a one-to-many relationship between Producers and Products (in other words, a product can only belong to one producer), than it would make sense to just put a foreign key reference directly in your Products table:
One-To-Many
create table Producer
(
id int identity(1, 1) not null primary key clustered,
Name varchar(100) not null
)
go
create table Product
(
id int identity(1, 1) not null,
Name varchar(100) not null,
ProducerId int not null foreign key references Producer(id)
)
go
Many-To-Many
create table Producer
(
id int identity(1, 1) not null primary key clustered,
Name varchar(100) not null
)
go
create table Product
(
id int identity(1, 1) not null primary key clustered,
Name varchar(100) not null
)
go
create table ProductProducer
(
ProductId int not null foreign key references Product(id),
ProducerId int not null foreign key references Producer(id)
)
go
-- adding the primary key also ensures uniqueness
alter table ProductProducer
add constraint PK_ProductProducer
primary key (ProductId, ProducerId)
go
If you decide to go with the Join table, you wouldn't need to have an additional key, as the combination of ProductId/ProducerId would ultimately be unique. You could use them as a composite key, so you wouldn't need that additional Id field in ProductProducer.
One question I have is how does the Company relate to a phone number? I mean, is there just a list of internal numbers, and will all of these phone numbers be associated to an employee. If they are you can simply just make your phone number table and have it relate to an employee. For example.
create table company
(
company_id int PRIMARY KEY IDENTITY, --(Primary Key)
company_name varchar(100)
)
create table employee
(
employee_id int PRIMARY KEY IDENTITY, --(Primary Key)
employee_name varchar(100),
company_id int not null --(Foreign Key)
)
create table phone
(
phone_id int PRIMARY KEY IDENTITY,
phone_number varchar(15), -- 555-555-5555
phone_type varchar(10), -- Home | Cell | Work
employee_id int not null --(Foreign Key)
)
employee_id being your foreign key to your employees table.
If each employee only works for one company, then to get all of the phone numbers available for that company, you could do something like this
select *
from phone as p
join employee as e on e.employee_id = p.employee_id
where e.company_id = 1
I hope this helps :)
Best Answer
SQL employs a concept known as domain name integrity which means that the names of objects have a scope given by their container.
Column names have to be unique, but only within the context of the table that contains the columns. Table names have to be unique, but only within the context of the schema that contains the tables, etc.
When you query columns you need to reference the schema, table and column that you are interested in, unless one or more of these can be inferred. When you write a query, you need to reference the tables in your query by name directly or by using an alias, e.g. Customer.ID or C.ID from Customer C, etc., unless your query is so simple that it only references one table.
There was a time when there was a technical requirement for uniqueness of all column names, which applied to old ISAM databases and to languages like COBOL in the 1960s and 70s. This got dragged along for no good reason into dBase in the 1980s and has stuck as a convention well into the relational and object DBMS eras. Resist this outdated convention.
When the shift from flat file and network databases to relational databases happened in the 1970s and 80s, the idea of joining tables was new. So some people chose the convention that a unique name could be repeated if one column was a reference to another (i.e. foreign keys). This concept is called a "natural join" and a lot of people still advocate for doing this.
I am not a fan of natural joins because it requires you, ultimately, to throw out the concept of domain name integrity and force the whole column reference into the column name.
The issue with natural joins is that you either have to be hypocritical or you have to make your column names long and unreadable. Let me illustrate: It may sound like a good idea that the primary key of the Customer table is CustomerID. Then in your Invoice table, your foreign key to Customer is also called CustomerID. This would be a natural join and it all sounds good so far. Here is the problem. What if your convention is to have a column on every table called LastUpdatedDate? So are you meant to join every table to every other table by LastUpdatedDate? Of course not. This is the absurdity of natural joins. In order to avoid this absurdity you would need to jam the table name into the column name as a prefix. However, if you have multiple schemas in your database, you can't stop there. You also need to add the schema to the column name, and so it goes.
Another place where natural joins break down is when you have multiple relationships between the same two tables. You if you need two references to Employee on your Invoice table (Sold By and Approved By, for example) you can't call them both EmployeeID.