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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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 6, 2023 at 12:02 am
Can you give an example of when would identity be appropriate? This would help me understand.
February 6, 2023 at 4:17 pm
CreateIndexNonclustered wrote: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: 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).
And, to prevent non-numeric chars, you'll have to add CHECK conditions for a string ID that will even more complexity and overhead.
Sometimes a generic rules make sense in and of itself, but not in practical usage.
The practicality you speak of is based on bad premise.
IF you were to use a natural key, SQL server should not be generating the key, it would be getting inserted from somewhere else. The value of the key is important and significant in a way that SQL server could not understand.
Using an integer for an employee ID that is text because it is more efficient for joins, harms query ability in several scenarios.
(I did my math wrong earlier, an integer is 32 bits, char(10) is 80 bits)
If you use an integer because it is easier to increment and more efficient to join, two things are sacrificed for the convenience of the developer and database engine at the cost of non-optional business requirements of creating employee/professor Ids that are non-arbitrary, arbitrarily, and making all necessary string functions impossible without type conversions.
The real take away is, never use natural keys for table relations.
February 6, 2023 at 7:31 pm
>> The professorID is likely both the natural ID for the professor and the identity of the row. <<
We used to call people who used the identity table property as an identifier "ID-iots" because it missed some of the basic parts of RDBMS. The rows in a table have no ordering. They are not located by position inside the database schema. They are located by key. The key was defined as a subset of columns in each row that is unique and has no NULLs. This is straight Dr. Codd RDBMS and is usually covered in the first week of a decent class.
The professor_id is probably a tax identification number of some kind. This would give it the two things we want an identifier; validation and verification. We will know the identifier is valid if it is string by using a regular expression. This is why every standardized ISO and ANSI encoding I've see over the last 30+ years follows the Unicode convention of using Latin alphabet, digits and a limited range of punctuation. Thanks to punchcards and fixed length data fields in COBOL filesystems, the vast majority of these standards are fixed length.
Validation is done either by a trusted authority, or a process. An example of a trusted authority would be the taxing authority, to whom we are paying part of this professor's salary. His tax identification would be done by table lookup or a combination of table lookup and a process to see that his tax id is correctly assigned to this guy.
The use of an identity table property is wrong because it's not an attribute of the entity being modeled. Again, this is straight Dr. Codd.
Did you ever get into Jewish mysticism? A Kabbalah number is that which is assigned by God to everything in his creation and it's written out in Hebrew characters (which are also numbers). If you know the true name of God, then you can invoke him with his Kabbalah number and a proper ceremony and since God is one thing, not divided into many roles like his creations, He will come!
Unfortunately, in the rule of RDBMS, if you try to represent the same entity in different SQL servers, you get a different value for the identity property on each machine. The analogy I prefer is that of a parking garage. Each car has a unique identifier or key in the form of its VIN. But if you really want to save space, just use the parking space number in the garage. It works great locally! You've got a ticket with that number on it. You verified that it goes to the particular space that you left your car in. But did you notice if you try to use that parking space number on your insurance forms, a bill of sale, a traffic ticket, or anything, meaningful, it is useless.
And after all of this messing around, did you notice that you still have to have the real key in the database? TheID-iots are usually using identity or GUID to mimic a pointer chain in a non-RDBMS data model.
>> Suppose you do use a string for the ID: how, then, do you determine the next value to be used?<<
You said something that makes no sense in a relational model. Remember that this is a set oriented language, not one based on sequential file structures. The concept of "next" doesn't exist. If you want to do a sequence of some sort, you should be using a CREAT SEQUENCE statement and have designed that particular data element properly, instead of depending on the current hardware to do it for you.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply