February 1, 2023 at 7:47 pm
I have two tables: tblProfessors with ProfessorID as the primary key and tblStudents with StudentID as the primary key. I'm wondering if it's possible to create a single foreign key (ID) in a separate table that is a combined list of ID's from tblProfessors and tblStudents.
Here is the script for both tables:
CREATE TABLE [dbo].[tblProfessors](
[ProfessorID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
[StartDate] [date] NOT NULL,
[BeltRank] [varchar](50) NOT NULL,
CONSTRAINT [PK__tblProfessors] PRIMARY KEY CLUSTERED)
CREATE TABLE [dbo].[tblStudents](
[StudentID] [int] IDENTITY(10,1) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
[StartDate] [date] NOT NULL,
[BeltRank] [varchar](50) NOT NULL,
CONSTRAINT [PK__tblStudents] PRIMARY KEY CLUSTERED)
I want to create a Contact table where I have an ID column with both the student and professor ID's. Is this possible?
February 1, 2023 at 11:16 pm
>>I have two tables: tblProfessors with professor_id as the primary key and tblStudents with StudentID as the primary key. <<
The use of the prefixed "TBL_" is a design error so bad it has a name; it's called the tibble. In fact, Phil Factor even wrote a little poem about it. The basic error is that you do not mix data and metadata in a table nor do you mix relationships and entities in the table.
>> wondering if it's possible to create a single foreign key (ID stupid [) in a separate table that is a combined list of ID's from Professors and Students. <<
Yes, but we need fix those non-tables that you posted
CREATE TABLE Professors
(professor_id CHAR(10) NOT NULL PRIMARY KEY, -- not an option!!
first_name NVARCHAR(35) NOT NULL,
last_name NVARCHAR(35) NOT NULL,
start_date DATE NOT NULL, --- no end date?
belt_rank CHAR(10) NOT NULL
CHECK (belt_rank IN (....)) );
Take a quick look at everything I changed. I used a length of the columns for name parts that conforms to the international postal standards instead of just grabbing 50. The belt rank is going to be picked from a list not free text; you allowed stuff like "sauerkraut" to be a rank! Trust me, if you allow it, you will get it. People who use the identity table property (it is not a column) are called ID-iots in SQL slang. By definition, a column has to be a data type, not a table property. Since an identifier has to be on a nominal scale, it needs to be a character data type. This is usually covered in the first week of any good course undated design
CREATE TABLE Students
(student_id CHAR(10) NOT NULL PRIMARY KEY, -- not an option!!
first_name NVARCHAR(35) NOT NULL,
last_name NVARCHAR(35) NOT NULL
);
CREATE TABLE Contacts
(professor_id CHAR(10) NOT NULL
REFERENCES Professors
ON UPDATE CASCADE
ON DELETE CASCADE,
student_id CHAR(10) NOT NULL
REFERENCES Students
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (professor_id , student_id ));
Please pay careful attention to the use of references to establish foreign key relationships. Also learn what the DRI actions are and how they work. SQL does most of the important work in the DDL, not the DML.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 2, 2023 at 10:58 am
Celko, why did you choose CHAR(10) whilst an int works perfectly? Certainly in the age of anomized data
I didn't get the course "Since an identifier has to be on a nominal scale'
February 2, 2023 at 3:15 pm
You could create a combined table. I assume (perhaps wrongly, if so, correct me) that you will have fewer than 10 professors in the table and therefore you intend that the "IDENTITY(10,1)" in the Students table will prevent overlap of IDs, i.e., keep the IDs unique even in a combined table.
If that is true, then you can do something like this:
CREATE TABLE dbo.Contacts (
ContactID int NOT NULL CONSTRAINT PK_Contacts PRIMARY KEY,
Type char(1) NOT NULL CONSTRAINT Check_Type CHECK(Type IN ('P', 'S'))
)
INSERT INTO dbo.Contacts SELECT ProfessorID, 'P' FROM dbo.TblProfessors;
INSERT INTO dbo.Contacts SELECT StudentID, 'S' FROM dbo.TblStudents;
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 2, 2023 at 7:58 pm
The identifiers for an entity are always on a nominal scale. You don't do any math with them, they don't represent a category, a magnitude or anything else. Here's a link to a talk I did years ago at a PosGres users meeting in Prague. The stuff about nominal scales appears about five minutes into the video.
https://www.youtube.com/watch?v=95mS0v4d1M8
Using integers for nominal scale is likely a character from the early Peanuts comic strips. He didn't have a name just a number. When Charlie Brown asked him if this was his father's way of rebelling against the computerization of the human race, he said, "no, it was his way of giving in!"
Please post DDL and follow ANSI/ISO standards when asking for help.
February 2, 2023 at 8:03 pm
I disagree with this model. A table should represent a set of entities. What you are calling the generic type should be an attribute. But I would assume that a professor and a student are totally different kinds of entities, and therefore need separate tables. What would you name the generic type? It needs a specific attribute name to be a valid data element and not "student_or_professor_role" as you have it.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 2, 2023 at 8:28 pm
For many practical reasons, just use an int like the rest of the world does. Besides which, the SQL DDL Celko gave above: "allow stuff like "sauerkraut" to be a[n ID]! Trust me, if you allow it, you will get it," according to his own words!
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 2, 2023 at 8:29 pm
I agree that the model is not technically valid. But I take it this is more a class / fictional "design" than a real-world implementation.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 3, 2023 at 12:10 am
Interesting thought on the primary key using char. I agree that you should only store attributes as numeric types if you plan on doing math on them, but if you are using only surrogate keys as you should be, no one should ever see the primary key.
I guess the question is whether the professorID is the ID number of the professor or if it is only the identity of the record in the table.
If it is the natural ID of the professor, use a string.
If it is only a surrogate key identifying the record, there is a reason to use integer, it is 8 bits smaller than char(10), will query faster and consume less memory. Granted char(10) has more possible values, but as a DBA do you really want to have to determine whether an index value is a tab or a space, or figure out if your key is ` or ' ?
February 3, 2023 at 7:09 pm
I agree that you should only store attributes as numeric types if you plan on doing math on them, but if you are using only surrogate keys as you should be, no one should ever see the primary key.
I guess the question is whether the professorID is the ID number of the professor or if it is only the identity of the record in the table.
If it is the natural ID of the professor, use a string.
If it is only a surrogate key identifying the record, there is a reason to use integer, it is 8 bits smaller than char(10), will query faster and consume less memory. Granted char(10) has more possible values, but as a DBA do you really want to have to determine whether an index value is a tab or a space, or figure out if your key is ` or ' ?
Again, it's just impractical to use a string rather than a numeric, whether you do math on the value or not.
The professorID is likely both the natural ID for the professor and the identity of the row. Suppose you do use a string for the ID: how, then, do you determine the next value to be used?
Also, the overall performance of an int is vastly better. An int is at least 6 bytes smaller than a char(10) (with no compression, even less with compression).
Moreover, to prevent non-numeric chars, you'll have to add CHECK conditions for a string ID which will even more complexity and overhead.
Sometimes a generic rule make sense in and of itself -- "use char for numeric values which you don't do math on", but not in practical usage.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 3, 2023 at 8:25 pm
This is great! Thank you for your help.
February 3, 2023 at 8:28 pm
Thank you for your first response and for sharing the video link. I'm completely new to all of this and appreciate any and all resources.
February 4, 2023 at 6:19 am
This was removed by the editor as SPAM
February 4, 2023 at 6:20 am
This was removed by the editor as SPAM
February 6, 2023 at 12:02 am
Can you give an example of when would identity be appropriate? This would help me understand.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply