February 8, 2012 at 8:15 am
Hi,
I have this four tables:
Name: Location_type
Columns: (Id;Type)
Records:
1 ; CAR
2; TruCK
3; WhareHouse
Name: Cars
Columns: (Id;Brand)
Records:
1;bmw
2 ; audi
Name: trucks
Columns: (Id;brand)
Records:
1;Mercedes
Name: WareHouses
Columns: (Id;local)
Records:
1;Palm
I what to create a New table that will relate the types of locations where stock can be in ,
with all the possible physical location (car 1, car 2, truck, warehouse)
I have created a new table named Physical_Location as follow:
Columns: (Id;Locationtype;Physical)
Records:
1;1;1
2 ;1;2
3;2;1
4;3;1
I whant that column physical can only have values from the cars table id or the trucks table id or the warehouse table id.
How can i do this?
Thankls
February 8, 2012 at 9:30 am
I don't think you want a table for this. This really should be a view instead of a permanent table. Otherwise you have to insert/update/delete from this table every time you make a change to either table. Just make your view something like
select columns
from Location
cross join cars
Just my opinion but I would have a vehicles table with a type instead of separate tables for each vehicle type.
create table Vehicles
(
VehicleID int identity,
VehicleType varchar(10), --or this could be a lookup to a VehicleType table
Brand varchar(20)
)
If you create a table for each vehicle type do you have a tables for Vans, RVs, SUVs, Motorcycles, Bicycles, Mopeds, Space shuttles?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 8, 2012 at 9:34 am
The table Type is location type, not vheicule type.
February 8, 2012 at 9:36 am
In the location_type you don't have just vheicules...
You have three different records:
1) Cars
2) Trucks
3) WareHouse
This is where the stocks can be
February 8, 2012 at 9:36 am
The ideia of the view seems nice
February 8, 2012 at 9:44 am
river1 (2/8/2012)
In the location_type you don't have just vheicules...You have three different records:
1) Cars
2) Trucks
3) WareHouse
This is where the stocks can be
I followed that. Maybe that should be Vehicle and Warehouse. i was really trying to point out that separate tables for different vehicles types is likely going to cause you some issues down the road.
Stock is in a location, that location has a type
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 8, 2012 at 9:49 am
"Stock is in a location, that location has a type "
Sorry, didn't understood you point...
This is a database about stocks (Car parts)
This parts (wheel, tires, etc...) can be located in one of this types :
In a car (in use...)
In the trucks
In the WareHouse
February 8, 2012 at 10:01 am
river1 (2/8/2012)
"Stock is in a location, that location has a type "Sorry, didn't understood you point...
This is a database about stocks (Car parts)
This parts (wheel, tires, etc...) can be located in one of this types :
In a car (in use...)
In the trucks
In the WareHouse
I was trying to say the your stock is not in a type, it is in a location and that location has a type. In other words, it isn't in a car it is in locationID 4, locationID 4 is a car
Here is a rough sketch of what I was thinking.
create table location_type
(
LocationTypeID int identity,
Description varchar(20)
)
insert location_type
select 'Warehouse'
union all
select 'Car'
union all
select 'Truck'
Create table location
(
LocationId int identity,
LocationTypeID int,
Description varchar(20)
)
insert location
select 1, 'Main WareHouse'
union all
select 2, 'My Car'
union all
select 2, 'Orange Subaru'
union all
select 3, 'Delivery Truck 1'
union all
select 3, 'Delivery Truck 2'
select * from location
select * from location_type
Then you would just have a locationID in your stock table so you know where it is. From that you can extrapolate the other details.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 10, 2012 at 9:18 am
Hi,
I was thinking... in a situation where I want that a database column can only have data that is already on one of other three tables (like a FK), can i create a CK that says that the values inserted on that field can only be values already present on table1 or table 2 or table3?
February 10, 2012 at 9:23 am
river1 (2/10/2012)
Hi,I was thinking... in a situation where I want that a database column can only have data that is already on one of other three tables (like a FK), can i create a CK that says that the values inserted on that field can only be values already present on table1 or table 2 or table3?
This is another reason I was suggesting that multiple tables for this is not the best approach. I think the only you could enforce this would be triggers. This is a decent sign that the design is not going to be easy to support. If this was one table instead of 3 it is painless with a foreign key. What happens when you add a 4th table or the 23rd?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 10, 2012 at 9:26 am
Another to consider with the multiple table approach, how will you find out where a stock item is at? The stock record will have a locationtypeid? This means then you have you build dynamic sql to figure out which table to pull your data from or build a whole series of if statements for each locationtypeid.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 10, 2012 at 9:28 am
That will not happen.
I have four tables:
Cars
Trucks
WareHouses
Type of location
But I have another table named Physical_Location
it as OwnID; type of location Id; ID Location
February 10, 2012 at 9:45 am
Well I have given my suggestions about the approach you are taking. I still think your approach is not the best but I guess we will have to agree to disagree on that. 😀
The only way you can achieve this type of conditional foreign key constraint will be with triggers.
If I understand what you are trying to do, On insert to your location_Type table you will have to verify that the quasi-foreign key exists in one of your other tables? Create an insert trigger on location_type
if not exists
(
select key from cars
union all
select key from trucks
union all
select key from warehouse
)
RAISERROR('Invalid Key Value', 18, 42)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply