SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

My Rhythmic Crotch posted:

Not reading any of that

I skimmed it. Well, parts of it. The main thing is that your system is doing this stupid thing people do where they store comma-separated lists of items in a single column and then write complicated queries to get the actual values out. Do your best to persuade the people you work with that this is a bad idea.

# ? Feb 10, 2017 11:41

Adbot: ADBOT LOVES YOU

# ? Jun 4, 2024 19:42

Vargatron: Apr 19, 2008; MRAZZLE DAZZLE

Any of you goons familiar with the SAP R/3 data schema? I'm trying to find a schema overview so that I can figure out what tables I need to use to port over about 50 SSRS reports during a systems migration later in the year.

# ? Feb 10, 2017 14:59

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

My Rhythmic Crotch posted:

Not reading any of that

Nevertheless I am still curious why Bucardo requires two-hours for DDL changes, and strongly recommend releasing a change in phases to avoid that downtime.

# ? Feb 10, 2017 15:00

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

Has anybody used the MSSQL for Linux preview, ideally in a docker environment? Have you found any particular pain points?

# ? Feb 11, 2017 14:41

Kilson: Jan 16, 2003; I EAT LITTLE CHILDREN FOR BREAKFAST !!11!!1!!!!111!

PhantomOfTheCopier posted:

Nevertheless I am still curious why Bucardo requires two-hours for DDL changes, and strongly recommend releasing a change in phases to avoid that downtime.

We did something similar, and we didn't have any trouble with Bucardo replicating any new tables or existing table structure changes. It shouldn't take any longer than any regular inserts/updates of data in the tables. I guess it depends on how you configure Bucardo's replication.

# ? Feb 14, 2017 21:39

Boz0r: Sep 7, 2006; The Rocketship in action.

I'm trying to insert the result of a select statement into a another table, but I'm not sure how.

This is my select statement:

code:

SELECT tky.ID as 'COLUMN_1', hky.ID as 'COLUMN_2'
FROM [dbo].[Imported CSV] as sk
join THING as hky on hky.KODE = sk.HY
join THING as tky on tky.KODE = sk.TY

I have another table with two columns with the same names. I've tried something like this, but it doesn't work:

code:

INSERT INTO MANY_TO_MANY 
values (
	SELECT tky.ID as 'COLUMN_1', hky.ID as 'COLUMN_2'
	FROM [dbo].[Imported CSV] as sk
	join THING as hky on hky.KODE = sk.HY
	join THING as tky on tky.KODE = sk.TY
)

select * from MANY_TO_MANY

# ? Feb 23, 2017 09:15

DICTATOR OF FUNK: Nov 6, 2007; aaaaaw yeeeeeah

Assuming you're using MySQL (not sure about other engines):

code:

INSERT INTO MANY_TO_MANY 
SELECT tky.ID as 'COLUMN_1', hky.ID as 'COLUMN_2'
FROM [dbo].[Imported CSV] as sk
join THING as hky on hky.KODE = sk.HY
join THING as tky on tky.KODE = sk.TY;

select * from MANY_TO_MANY

INSERT INTO SELECT

# ? Feb 23, 2017 09:38

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

The "FROM [dbo].[Imported CSV]" bit suggests MS SQL Server.

https://technet.microsoft.com/en-us/library/ms189872(v=sql.105).aspx

It looks like you just need to get rid of the VALUES(...) and it should work?

# ? Feb 23, 2017 10:18

Boz0r: Sep 7, 2006; The Rocketship in action.

Thanks, that works perfectly.

# ? Feb 23, 2017 10:29

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

It happens to match SQLite in this case. It sounds like you just need a nice railroad diagram to follow. :eng101:

# ? Feb 23, 2017 13:53

Boz0r: Sep 7, 2006; The Rocketship in action.

I like trains, so thanks.

# ? Feb 23, 2017 15:00

ModeSix: Mar 14, 2009

Crossposting from .NET thread because it is in fact a database access question.

I have a question for people who work with Entity Framework Core, it's related to databases. I'm using MS SQL Server Express 2016.

I don't have a lot of experience designing databases but I'm trying to create one for a project I am working on.

I've visually created the schema using a visual db tool and I have a couple questions about if I've done it right.

Here's the visual representation of my database. It's fairly basic, because I am just creating a prototype right now, and this will expand later.

Basically I want to make sure that the way I've linked the foreign keys will allow access to all data from different views.

Basic scenarios I want to ensure will work:
- If I am in a Teacher view, will I be able to view the entries in the Students table that belong to Groups that are assigned to the Teacher? (there can be multiple groups for a single teacher)
- If I am in a Student view, will I be able to see who their Teacher is?
- If I am in a Client view will I be able to see the Students that belong to them? (obviously each client can have multiple groups and students)

These are three possible scenarios I will need.

Have I done this right, or do I need to store the FK's for all the entities in each view, or will a single linkage through the Groups table suffice for the other views by eager loading relevant data?

# ? Feb 25, 2017 14:22

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

ModeSix posted:

Crossposting from .NET thread because it is in fact a database access question.

I have a question for people who work with Entity Framework Core, it's related to databases. I'm using MS SQL Server Express 2016.

I don't have a lot of experience designing databases but I'm trying to create one for a project I am working on.

I've visually created the schema using a visual db tool and I have a couple questions about if I've done it right.

Here's the visual representation of my database. It's fairly basic, because I am just creating a prototype right now, and this will expand later.

Basically I want to make sure that the way I've linked the foreign keys will allow access to all data from different views.

Basic scenarios I want to ensure will work:
- If I am in a Teacher view, will I be able to view the entries in the Students table that belong to Groups that are assigned to the Teacher? (there can be multiple groups for a single teacher)
- If I am in a Student view, will I be able to see who their Teacher is?
- If I am in a Client view will I be able to see the Students that belong to them? (obviously each client can have multiple groups and students)

These are three possible scenarios I will need.

Have I done this right, or do I need to store the FK's for all the entities in each view, or will a single linkage through the Groups table suffice for the other views by eager loading relevant data?

You're on the right track, more or less. To see students assigned to a teacher's group for example you could do:

code:

SELECT *
FROM Teachers AS T
JON Group AS G
ON G.TeacherID = T.TeacherID
JOIN Students AS S
ON S.StudentID = G.StudentID
WHERE T.LastName = 'Butts'

Column names may be wrong, phone posting without benefit of your image.
Your separation into the individual tables is petty good as is. Good job

# ? Feb 25, 2017 14:45

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

It's pretty good. Probably more natural to call it groups.student, because each entry is just a single id.

What are the student.levels, or more specifically how are they related to the group level? Group only has a single level identified, but student levels are broken down into written and oral. If there are more, having a separate table of studentlevel might be useful (stid,lvlid,low,high) and could aide searches by level.

Final thought, groups have a single teacher and client, but multiple students? If you have twenty students in a group, you're going to have 20 rows in the table with the same (groupid,clientid,teacherid), so you may want a different table for group assignments for the students.

These are not necessary changes, but might help depending on your goals.

Question: Do you need to report current status only, or are you interested in historical information (John Doe moved groups)? (See also, Zawinski's Law)

PhantomOfTheCopier fucked around with this message at 16:17 on Feb 25, 2017

# ? Feb 25, 2017 16:15

ModeSix: Mar 14, 2009

Thanks a lot guys, very helpful.

Nth Doctor posted:

You're on the right track, more or less. To see students assigned to a teacher's group for example you could do:
code:
SELECT *
FROM Teachers AS T
JON Group AS G
ON G.TeacherID = T.TeacherID
JOIN Students AS S
ON S.StudentID = G.StudentID
WHERE T.LastName = 'Butts'
Column names may be wrong, phone posting without benefit of your image.
Your separation into the individual tables is petty good as is. Good job

Great. This is very helpful, because it will help me putting together the queries. I won't be writing raw SQL statements, as I am using ASP.NET Core with Entity Framework, but it will help me when actually looking at generated code to make sure it's correct.

PhantomOfTheCopier posted:

It's pretty good. Probably more natural to call it groups.student, because each entry is just a single id.

What are the student.levels, or more specifically how are they related to the group level? Group only has a single level identified, but student levels are broken down into written and oral. If there are more, having a separate table of studentlevel might be useful (stid,lvlid,low,high) and could aide searches by level.

The high/low designations are for the evaluation prior to being assigned to a group. It's internal information only for the administrative side of things, whereas the group will be given a specific level in which students are assigned. For example a student may have a high/low of 4/6 and be assigned to a level 5 group.

This is for an ESL school where they are tracking all their information in Google sheets right now and they have about 50 different sheets they use and things get messed up quite often. I'm just throwing together a demo for them right now so they can see what I am proposing, so it's going to be missing a lot of rows as well as tables.

quote:

Final thought, groups have a single teacher and client, but multiple students? If you have twenty students in a group, you're going to have 20 rows in the table with the same (groupid,clientid,teacherid), so you may want a different table for group assignments for the students.

These are not necessary changes, but might help depending on your goals.

Question: Do you need to report current status only, or are you interested in historical information (John Doe moved groups)? (See also, Zawinski's Law)

Hm, so basically what you're saying is store a separate table of students that links to the group table? If I've understood correctly.

Edit: Is this more like what you're talking about?

Regarding historical information, yes I will eventually add tracking for that information, but for the prototype what I have is sufficient.

ModeSix fucked around with this message at 20:35 on Feb 25, 2017

# ? Feb 25, 2017 16:49

Boz0r: Sep 7, 2006; The Rocketship in action.

How can I find and remove duplicate entries in a table, when they have no id?

# ? Feb 27, 2017 14:32

Gatac: Apr 22, 2008; Fifty Cent's next biopic.

Define what you actually mean by duplicate and you'll be halfway there, I think.

# ? Feb 27, 2017 15:06

Cadiem: Oct 9, 2007

Boz0r posted:

How can I find and remove duplicate entries in a table, when they have no id?

I use a CTE/row_number when I come across something like that, so like

With CTE as(
Select row_number() over (partition by [some fields] order by [another field]) RN, * from some_table)
Select * from CTE where RN>1 -- change to Delete from CTE where RN>1 when you want to delete

Cadiem fucked around with this message at 15:18 on Feb 27, 2017

# ? Feb 27, 2017 15:14

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

If you have a trillion duplicates and only a few legitimate entries it may be faster to do:

SELECT DISTINCT * INTO newTable FROM oldTable
// add pkey, fkey, indexes, whatever to newTable
DROP TABLE oldTable
ALTER TABLE newTable RENAME TO oldTable

which is, I believe, one of the few legitimate uses of DISTINCT *

Of course, only a total moron would let a table grow to xillions of rows before noticing the missing pkey *cough*

# ? Feb 27, 2017 23:26

Boz0r: Sep 7, 2006; The Rocketship in action.

NihilCredo posted:

If you have a trillion duplicates and only a few legitimate entries it may be faster to do:

SELECT DISTINCT * INTO newTable FROM oldTable
// add pkey, fkey, indexes, whatever to newTable
DROP TABLE oldTable
ALTER TABLE newTable RENAME TO oldTable

which is, I believe, one of the few legitimate uses of DISTINCT *

Of course, only a total moron would let a table grow to xillions of rows before noticing the missing pkey *cough*

I think this works.

I didn't design the model, and the rest of it is exactly as dumb. I think all our ManyToMany tables are missing primary keys.

# ? Feb 28, 2017 09:14

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

Boz0r posted:

I didn't design the model, and the rest of it is exactly as dumb. I think all our ManyToMany tables are missing primary keys.

I can think of exactly two databases in existence that induce "users" into such a mindset. The suspense is killing us.

# ? Feb 28, 2017 14:08

Knyteguy: Jul 6, 2005; YES to love
NO to shirts; Toilet Rascal

I'm designing the schema on a new project from scratch, and schema design isn't really my forte. Anyone mind helping me with a few questions?

Are you supposed to use a join table for many-to-one relations, or is it mostly many-to-many that needs them?

Also for MSSQL what primary key do you use for a join table? IE EntityAddress with two ID fields.

Can I used a clustered index on an nvarchar primary key? I.e.: U000001 being a primary key user id. Sequential, but prefixed IDs.

Ty.

Knyteguy fucked around with this message at 20:18 on Mar 2, 2017

# ? Mar 2, 2017 20:14

Data Graham: Dec 28, 2009; 📈📊🍪😋

Many-to-many is what you want a relation table for.

For many-to-one relations, just put a column on the child table that points to the parent.

# ? Mar 2, 2017 20:19

Knyteguy: Jul 6, 2005; YES to love
NO to shirts; Toilet Rascal

Data Graham posted:

Many-to-many is what you want a relation table for.

For many-to-one relations, just put a column on the child table that points to the parent.

Thanks that's what I thought. A combination of StackOverflow and some db design articles screwed me up.

# ? Mar 2, 2017 20:25

LargeHadron: May 19, 2009; They say, "you mean it's just sounds?" thinking that for something to just be a sound is to be useless, whereas I love sounds just as they are, and I have no need for them to be anything more than what they are.

Boss wants to know if we should simplify one of our data retrieval algorithms by accumulating said data into a single specialized table when storing it, so that we can just query the one table when we need that data. Currently, the data is spread out among a variety of tables and retrieval is a little slow. We would need to keep the data as-is but *also* put it in that new specialized table (if that wasn't clear). I'm inclined to think that having that sort of hamfisted redundancy in a database is bad form, but I don't know if my argument for why is convincing. I'm thinking: 1) bigger DB, 2) more processing to be done when writing (though this by itself doesn't sound that bad - do extra work up front so we can do less work later), and 3) if we gently caress it up and introduce a bug that puts the redundant data out of sync it might be difficult to deal with in the wild (though a synchronization step on startup could mitigate this if it's performant enough). Any other reasons why this approach is bad? Or, maybe this approach is good and I'm the rear end in a top hat

# ? Mar 4, 2017 02:32

McGlockenshire: Dec 16, 2005; GOLLOCKS!

The general approach of duplicating and denormalizing or otherwise transforming data to be able to make specific queries easier is perfectly normal and fine. The there are segments of the report tool industry that are built around this idea.

How much can you push of the duplication effort into the database instead of the application? Perhaps this is a case for a few stored procs to coordinate the writes?

Can the duplicated data be allowed to grow stale, or does it have to be available instantly (within the same transaction)? Perhaps you can have the data duplicated by a background worker once the master data has been written and committed.

# ? Mar 4, 2017 04:01

pangstrom: Jan 25, 2003; Wedge Regret

Yeah, my amateur take is that if queries are taking perceptible/annoying amounts of time for users and you don't need completely-current data for them go ahead and make denormalized tables in stored procedures or scripts or wherever.

# ? Mar 4, 2017 04:09

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

LargeHadron posted:

Boss wants to know if we should simplify one of our data retrieval algorithms by accumulating said data into a single specialized table when storing it, so that we can just query the one table when we need that data. Currently, the data is spread out among a variety of tables and retrieval is a little slow. We would need to keep the data as-is but *also* put it in that new specialized table (if that wasn't clear). I'm inclined to think that having that sort of hamfisted redundancy in a database is bad form, but I don't know if my argument for why is convincing. I'm thinking: 1) bigger DB, 2) more processing to be done when writing (though this by itself doesn't sound that bad - do extra work up front so we can do less work later), and 3) if we gently caress it up and introduce a bug that puts the redundant data out of sync it might be difficult to deal with in the wild (though a synchronization step on startup could mitigate this if it's performant enough). Any other reasons why this approach is bad? Or, maybe this approach is good and I'm the rear end in a top hat

Before doing denormalization, can you first investigate the indexing situation? Do the FKs you're joining on have supporting indices that are regularly maintained/rebuilt/reorged?

Nth Doctor fucked around with this message at 16:29 on Mar 4, 2017

# ? Mar 4, 2017 16:18

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

Quote is not edit

# ? Mar 4, 2017 16:28

Knyteguy: Jul 6, 2005; YES to love
NO to shirts; Toilet Rascal

(work in progress) Anyone see any basic mistakes I'm making here? I'm not sure if I'm missing anything really important from a general design standpoint (less the specifics for this particular site). I'm hoping this doesn't end up in coding horrors or something, but if that's necessary to get it right so be it.

http://i.imgur.com/DfRnHxi.png

It's cool if there's no opinion on data since you don't have any examples, so I'm mostly hoping for some input on whether it's laid out in a really terrible way, and if I'm maybe screwing up PK/FKs somehow. Some tables will expand, such as InvoiceDetails. I'll also hand replace all the data types to work for SQL Server in the SQL itself, so those can probably be ignored.

Thanks.

E: the not nulls next to primary keys in some places as opposed to others is just the dbdesigner.net software being weird on the export. The image export isn't exact either. Tables line up a little better in the actual design.

Knyteguy fucked around with this message at 20:02 on Mar 4, 2017

# ? Mar 4, 2017 19:25

Nth Doctor: Sep 7, 2010; Darkrai used Dream Eater!
It's super effective!

Modest Mouse cover band posted:

(work in progress) Anyone see any basic mistakes I'm making here? I'm not sure if I'm missing anything really important from a general design standpoint (less the specifics for this particular site). I'm hoping this doesn't end up in coding horrors or something, but if that's necessary to get it right so be it.

http://i.imgur.com/DfRnHxi.png

It's cool if there's no opinion on data since you don't have any examples, so I'm mostly hoping for some input on whether it's laid out in a really terrible way, and if I'm maybe screwing up PK/FKs somehow. Some tables will expand, such as InvoiceDetails. I'll also hand replace all the data types to work for SQL Server in the SQL itself, so those can probably be ignored.

Thanks.

E: the not nulls next to primary keys in some places as opposed to others is just the dbdesigner.net software being weird on the export. The image export isn't exact either. Tables line up a little better in the actual design.

Two things that jump out at me from a quick noon-comprehensive glance at the schema: unless there is an extremely compelling reason, I wouldn't use VARCHARs as PKs. If you need pseudo PKs, try a unique index, and let your identities remain INTs.
Also, it may save on sanity to name your identity columns after the table they are the PK of in all circumstances, not just when they're FK.

# ? Mar 5, 2017 07:13

ConanThe3rd: Mar 27, 2009

Someone starting SQL (like only had to peak at PHPmyadmin to futz with things in wordpress and drupal) and using PHPMyAdmin

I have a client who has a list of their own clients (learners) who attend courses sorted in Excell and they are wanting to move to a database.

What would be the best way to link learners to a specific course?

For example; I want one course that has three learners on it, how could I get their LearnerID into the one collum as creating the same course over and over on a per learner basis strikes me, as a complete outsider, as not intuitive.

ConanThe3rd fucked around with this message at 09:38 on Mar 6, 2017

# ? Mar 6, 2017 09:30

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

ConanThe3rd posted:

Someone starting SQL (like only had to peak at PHPmyadmin to futz with things in wordpress and drupal) and using PHPMyAdmin

I have a client who has a list of their own clients (learners) who attend courses sorted in Excell and they are wanting to move to a database.

What would be the best way to link learners to a specific course?

For example; I want one course that has three learners on it, how could I get their LearnerID into the one collum as creating the same course over and over on a per learner basis strikes me, as a complete outsider, as not intuitive.

You have a many-to-many relationship: a given student might be on zero, one, or many courses and a given course might have zero, one, or many students. You are correct that recreating the same course multiple times would be a mistake; more technically it would be creating an unnormalized database. Google "database normalization". What you need is a junction table, in other words a third table where any given row represents the fact that student x is registered for course y. Don't make the mistake of storing a comma-separated list of student IDs in a column - this is a common antipattern that will bog you down horribly.

Tables:

Student (StudentID, ..., PRIMARY KEY(StudentID))
Course (CourseID, ..., PRIMARY KEY(CourseID))
StudentCourse(StudentID, CourseID, PRIMARY KEY(StudentID, CourseID))

StudentCourse should also have two foreign keys, one to Student and one to Course.
Some people will advocate adding a third column to StudentCourse to serve as a single-column primary key. This is fine, but (1) be consistent with your decision if you later add more junction tables and (2) if you do this, make sure (StudentID, CourseID) is a UNIQUE KEY.
The naming conventions shown here are not universal in database design. As with many things in software, the most important thing is to be consistent across your entire database.
It is OK for StudentCourse to have more columns if they really do relate to that student's relationship with that course, e.g. date registered, grade.

# ? Mar 6, 2017 11:09

ConanThe3rd: Mar 27, 2009

Hammerite posted:

You have a many-to-many relationship: a given student might be on zero, one, or many courses and a given course might have zero, one, or many students. You are correct that recreating the same course multiple times would be a mistake; more technically it would be creating an unnormalized database. Google "database normalization". What you need is a junction table, in other words a third table where any given row represents the fact that student x is registered for course y. Don't make the mistake of storing a comma-separated list of student IDs in a column - this is a common antipattern that will bog you down horribly.

Tables:

Student (StudentID, ..., PRIMARY KEY(StudentID))
Course (CourseID, ..., PRIMARY KEY(CourseID))
StudentCourse(StudentID, CourseID, PRIMARY KEY(StudentID, CourseID))

StudentCourse should also have two foreign keys, one to Student and one to Course.
Some people will advocate adding a third column to StudentCourse to serve as a single-column primary key. This is fine, but (1) be consistent with your decision if you later add more junction tables and (2) if you do this, make sure (StudentID, CourseID) is a UNIQUE KEY.
The naming conventions shown here are not universal in database design. As with many things in software, the most important thing is to be consistent across your entire database.
It is OK for StudentCourse to have more columns if they really do relate to that student's relationship with that course, e.g. date registered, grade.

OK I think I got it. Basically when linking the two I'd be inserting the two table's Primary IDs into a record in StudentCourse?

# ? Mar 6, 2017 16:07

30 TO 50 FERAL HOG: Mar 2, 2005

code:

CREATE TABLE [dbo].[Product]
(
	[ID] INT NOT NULL PRIMARY KEY IDENTITY,
	[Category_ID] INT NOT NULL,
    	[Manufacturer_ID] INT NOT NULL,
	[Model] NVARCHAR(50) NOT NULL UNIQUE,
	[ModelStripped] AS [dbo].GetStrippedModel(Model) PERSISTED UNIQUE,
	[Description] NVARCHAR(MAX) NOT NULL,
	[CommentsNotes] NVARCHAR(MAX) NULL,
	[RU] INT NULL,
	[Width] DECIMAL(10, 4) NULL,
	[Depth] DECIMAL(10, 4) NULL,
	[Height] DECIMAL(10, 4) NULL,
	[Weight] DECIMAL(10, 4) NULL,
	[BTU] DECIMAL(10, 4) NULL,
	[Watts] DECIMAL(10, 4) NULL,
	[ProductURL] NVARCHAR(MAX) NULL,
	[MostRecentPriceDate] AS [dbo].GetMostRecentPriceDate(ID),
	[MostRecentPrice] AS [dbo].GetMostRecentPrice(ID),
    	CONSTRAINT [FK_Product_Manufacturer] FOREIGN KEY (Manufacturer_ID) REFERENCES [Manufacturer](ID),
	CONSTRAINT [FK_Product_Category] FOREIGN KEY (Category_ID) REFERENCES [Category](ID)
)

code:

CREATE FUNCTION [dbo].[GetStrippedModel]
(
	@Model NVARCHAR(50)
)
RETURNS NVARCHAR(50)
AS
BEGIN
	DECLARE @regex NVARCHAR(50)
	SET @regex = '%[^a-z]%'

	WHILE PATINDEX(@regex, @Model) > 0
		SET @Model = STUFF(@Model, PATINDEX(@regex, @Model), 1, '')

	RETURN @Model
END

Deployment is failing because 'ModelStripped' is allegedly non-deterministic. Basically I'm taking "Model" and removing spaces, special characters, etc because users will inevitably put in model numbers hyphenated differently, or with spaces, etc.

Of course, I could remove "PERSISTED UNIQUE" from the column and have the duplicate checking done in the front end logic, but I'm really trying to have the DB do most of the heavy lifting and use the front end for CRUD only.

What should I be doing here?

# ? Mar 6, 2017 16:17

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

ConanThe3rd posted:

OK I think I got it. Basically when linking the two I'd be inserting the two table's Primary IDs into a record in StudentCourse?

Yes

# ? Mar 6, 2017 16:35

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

It's been a while since I did anytyhing with MSSQL, but I think if you do WITH SCHEMABINDING in the function declaration, SQL Server will be smart enough to know your function is deterministic.

# ? Mar 6, 2017 16:42

30 TO 50 FERAL HOG: Mar 2, 2005

Jethro posted:

It's been a while since I did anytyhing with MSSQL, but I think if you do WITH SCHEMABINDING in the function declaration, SQL Server will be smart enough to know your function is deterministic.

Yeah, that's what I ended up doing. Thanks.

# ? Mar 6, 2017 16:49

Knyteguy: Jul 6, 2005; YES to love
NO to shirts; Toilet Rascal

Nth Doctor posted:

Two things that jump out at me from a quick noon-comprehensive glance at the schema: unless there is an extremely compelling reason, I wouldn't use VARCHARs as PKs. If you need pseudo PKs, try a unique index, and let your identities remain INTs.
Also, it may save on sanity to name your identity columns after the table they are the PK of in all circumstances, not just when they're FK.

Thank you, I ended up doing this. Sent to the client! 47 tables after it's all said and done yeesh.

# ? Mar 7, 2017 00:47

Adbot: ADBOT LOVES YOU

# ? Jun 4, 2024 19:42

lol internet.: Sep 4, 2007; the internet makes you stupid

I have this deadlock happening on the master DB. Any way to find out what process this is happening on? (Have no idea how to use SQL really btw.)

# ? Mar 7, 2017 08:01

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »