SQL Database Table Creation - A TimCo Retail Manager Video 

I'm glad you appreciate it. I've debated leaving it in or not but I've come to the conclusion that people relate better when they realize I'm not perfect. And you are right, hearing how someone works through an issue can help you do the same. As for debugging, I'll be shocked if we don't have an intense debugging session or two in our future. I'm doing this project for the first time on video. I don't pre-create the content and then do it on video so, just like with any real development process, I'll make mistakes and have to fix them. That's part of coding. Besides, that is the best time possible to teach people how to debug.

Awesome!

I'm glad you enjoyed it and it sounds like the feedback was correct and the new way will better handle various situations.

I will add it to the list. Thanks for the suggestion.

Thanks!

I appreciate the kind words.

It is pretty awesome.

You can, but that can be a lie eventually, which is why I'm not a huge fan of doing it that way. Imagine you use a system where an order is captured offline. When you get back online, the data syncs. However, if the field updated automatically on insert, you might see multiple orders with identical times when in reality, they were ordered well before that modified date.

I am not a fan of using triggers as a software developer. We leave Triggers for client customization that way version upgrades do not overwrite their business logic...

Awesome!

This is exactly what I was going to ask.

Yep, I prefer Dapper (especially for full .NET Framework projects since EF6 is slow on top of the other issues). My biggest issue is all of the auto-generated code that EF creates that you then have to maintain (I've seen that go very bad a couple times). I've considered using EF Core for part of this app once we convert to .NET Core and I'm still kicking that around. Either way, I'll probably do a video or two on EF Core at some point.

Interesting. I hadn't considered partial quantities to handle goods that are sold by weight. It would seem like we would then need to add in a field that identified what the number represented (items, pounds, kilograms, ounces, liters, etc.) I'll have to think on that, since it makes things more complex. I'd want to include it in the easiest way possible. I'll add splitting the payments to the list. It is something we can add in down the road without difficulty. Thanks for the suggestions!

@@IAmTimCorey as tinumurymury38 said all you need to do just change the type from int to decimal with 3 decimal points, this way to calculate the sub-total it's just the purchaseprice * qty

Don't you love how complicated it gets to do something "simple"? Thanks for the suggestions. We will discuss taxes and what to do with them in an upcoming video. I'll add this feedback to the list to discuss.

@@IAmTimCorey Agree with nas ben, I think you need a tax code on each product, as some products are not "VAT" rated (e.g. food) in the UK. - e.g. the issue with Jaffa cakes being luxury or food item.

The Projects folder you are referring to is just a visualization of the SQL database you are building. When you publish your database, you will publish it under SQL Server.

You are welcome.

Thanks!

Good question. It is actually one I flip flop on. There is a case to be made either way. The key is to just be consistent in your database. Beyond that, I don't think there is one clear winner.

Interesting.

This is one of those exceptions to the rule where you need to look at the bigger picture. For every API call, I will know the user's GUID. I will use that to get their info, identify who they are, and more across a number of tables. If I need to first translate that GUID to an ID, I will need to make an additional database call every time the user calls the API. That is expensive. Compare that to how often we add new users to our system (not very often, even if we are including customers in that table) and the benefits of an int ID are outweighed by the drawbacks in this particular system. This is why I try hard to not say things like "never". There is an exception to every rule. SQL can handle this one exception and the slowdown on insert will not cause a big issue, especially since it will make every subsequent call the user makes faster. We will be adding Foreign Keys to the database in an upcoming video. Index tuning doesn't happen until the end since you really should not optimize until you can observe.

I'm not a huge fan of EF, although EF Core is trying to win me over. I'll be doing a video soon on it.

Update your version of Visual Studio. They fixed this in one of the most recent patches (I put in a support ticket on it).

I guess, I found my answer which it has to do with AzureStorageEmulator which mimics what can be done when using Azure services. In this case tables, blobs and Queues. Main purpose is for testing purposes before deploying to azure services. In my case I will be using azure services. Thank you again for such great tutorial.

Yeah, those auto-names can be rough.

When we publish this application, we will be connecting to a remote SQL Server. There isn't much of a difference compared to a local copy. Just change your connection string and you are done.

@@IAmTimCorey Thanks for the fast reply. I thought there was more to it than the connection string, appreciate the education tim

I mentioned it briefly in an earlier video but we will actually implement it when we wire up the API. Basically, when a user logs in, we know who they are on every call. We will use that to associate their EF-created ID to a table in our SQL database. For now, we will populate the record manually. One of our sprints will include adding a system for creating user accounts.

Did I make you hungry?

I prefer chocolate digestives :)

The EF database gets created automatically under certain circumstances. Later on in the course, I do recreate it manually. If you want to see how that is done, go here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-sMPbsA7OTtM.html

Here is my opinion on this: this is a mindset that is slowly changing. Here's why: you are optimizing for size. I am optimizing for speed. It used to be that we needed to keep database size down. Now storage space is cheap. What isn't cheap is our time. The longer something takes, the less likely we are to do it. By calculating and storing the tax, sub-total, total, etc. in SQL, we are optimizing our system to retrieve that data faster. The danger is, like you said, we get inconsistent. In that case, we will need to recalculate. However, SQL is not really the place to do monetary multiplication (tax calculation). Doing so means you need to use the decimal type and you need to be careful about rounding. Addition/Subtraction isn't a problem. So, I do my monetary calculation in C# once and store it. If I ever had a consistency issue, I would need to address it in a larger manner. You REALLY don't want to have your calculation change after the fact for the amount of tax you charged. If you say you charged the user $1.50 in tax today (and that is what you presented to them), you don't want to "recalculate" it tomorrow and say you charged them $1.25. Otherwise you would have an "extra" $0.25 floating around unaccounted for. Right now, we are doing the calculations and presenting them to the user. They should never change again.

We will add foreign keys in. I believe it is coming up soon in the series.

The reason to stage things before committing is because sometimes you might want to make two commits out of one set of changes (two different things were done). In that case, you wouldn't stage everything. Also, there are times when you don't want to commit a changed file because you aren't pushing those changes centrally. For example, you might change a connection string locally to test things out, but you don't want those changes being committed.

Ok, yes, I see. In TFS you have the option to exclude files from check in, this is not possible in GIT, so then the stage makes sense. You probably has the habit of doing that, but so far in this series, it hasn't been necessary, I guess.

😂 Yeah, my brain skipped a beat there and my mouth kept talking.

Also, using nvarchar(max) is definitely an antipattern

Having a LastModified date is exteremly beneficial if the data is ever going to be used in a reporting system / data warehouse

The Id for the user is a string because that is the ID we get from the authentication system. Just because it is a string does not mean it isn't a primary key. Not having a primary key is what causes your table a heap. The Id field is marked as the primary key for the table. Yes, not having the benefit of inserting in order can be a perf hit, but it is a small one and one that is worth it for certain scenarios like this one. As for nvarchar(max) being an antipattern, that is not true. There is a purpose and a reason behind nvarchar(max). The key is to use it only when appropriate. That's what I did. Note that the only time I used it was for the product description, which can be a significant amount of text (more than 4,000 characters). As for the LastModified, yes, that can be beneficial and I considered it. The balance I'm trying to hit with these videos, though, is time vs benefit. Adding additional columns and maintaining them takes additional time. It doesn't give a lot of additional benefit. I did add it to the Product table but not every table in order to demonstrate the action but not have lots of repetition.

It comes down to personal preference. I don't have a strong feeling on it. Some say that User.FirstName more clearly demonstrates a specific user while others say that the Users table more clearly indicate it holds multiple users. In the end, just be consistent.

I've worked at a place where the official convention was singular but the reality was a complete mixed bag. My personal preference is singular as plural doesn't add any useful information. It should be self evident that a table called User doesn't contain a single user.

I am not a fan of using Entity Framework: www.iamtimcorey.com/blog/137806/entity-framework

I'm on the other side of the fence on this issue. I much prefer surrogate keys (auto-incrementing numbers that have no other meaning) for a couple reasons. First, natural keys can change. Email addresses can change, phone numbers can change, people can get other people's phone numbers, addresses can change, and even sensitive information can change. Also, laws are starting to prevent the use of sensitive data as natural keys because of security concerns. Now you are left without good solutions for a LOT of tables. To add to that, you have a performance hit every time you store a record (records are stored in order by primary key so inserting a new record means shifting all of the existing files on disk "down" for any records after the new record in order). You also have a performance hit when you try to link to the table (linking on multiple rows, text, etc.) All in all, I don't see the benefit (other than saving yourself one int column of "extra" data).

@@IAmTimCorey that's exactly what I was expecting to hear. Thanks for taking me from the dark side of the force.

@@vladimirmilatovic You can always create "unique" index on data columns, e.g. email address. I personally prefer to have [Id] PK columns on tables, and disagree with the user table Tim created.

I am not a big fan of Entity Framework. I'll be doing a video on it sometime soon but I prefer the control and speed of Dapper.

It all depends on what works best for you (and your team).

That isn't my preferred naming method. It doesn't read well and it doesn't describe what that field actually holds. Is that refUserId a cashier or the customer? What if you added another column for floor manager (who was in charge of the cashier at the time)? What would you call that column? refUserId2? I like the idea of trying to communicate that it is a foreign key but that is what Id does. It indicates that the column is an identifier column (a moniker I reserve for primary and foreign key columns). The only thing it doesn't do well is tell you which table it refers to (although the column details will communicate that).

I think from a tutorial point of view, it's sufficient.. but yes, if you where going to create this as a Full Production Application, I think you would need to look at the required data and do a full Database normalisation process and a Full ERD (Entity Relationship Diagram). I see this initial stage of design, more of a prototype.

I can add it to the suggestion list.

I keep it separate for separation of concerns. Just best practice.

I think we're going to store the AccessToken in a variable while the program runs as it's only valid for x amount of time. + if anyone were able to recover information from this database (in any way) they'd have access to all those tokens, which could pose a security risk. With just the UserId there's nothing anybody can do.

Using Visual Studio to create your database allows you to use source control and to deploy it with your code. Using SSMS is the traditional way and does not offer either of those benefits natively. SSMS is better for database management. Visual Studio is better for database development.

@@IAmTimCorey Thanks a lot Tim that is very helpful for me

my bad i had jumped to the comment before the last few seconds of the vid :)

Good luck on your WPF project. I do recommend you build a few test applications in WPF before jumping right into a "real" application. It will make your life easier. I cover that in this video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-h7aIzCkmbl8.html

I think the solution is that the user would still see the total number in stock, and then just use logic to see that the oldest stock of shirts was bought for $2 but there was only 7 left, and then make a new db entry for the other 3 at the new price. The ItemId should be the same if it's the same product. It would just exist multiple times in the Inventory table, one row for each batch that the store has purchased.

The price that the user will see is RetailPrice in the Product table which is independent of the stock purchase price which is used internally to calculate markup. The stock level would just be product line summed up. You could write business logic that would calculate the total PurchasePrice of the purchased items from different stock entries in order to get the aggregate markup.

I'm having the same problem with the PublishLocations folder. In the Solution Explorer I click the Icon to "Show All Files" and several files appear, including the publish.xml. However, when I go to SQL Server Object Explorer my tables are not showing up in the SQL Server DB, but in a folder labeled "Projects".... No idea where that folder came from... I'm trying to follow the tutorial step-by-step, but I'm not getting the same results. @alireda - Did you ever figure out what the issue with your PublishLocations folder was?? Thanks

@@avav3457 Hi, Sorry for the delay, I just saw it. Nope, I restarted the series from scratch on a new system. and it seems like an issue with the older visual studio version.

I don't do this because I don't reserve specific numbers for specific people/roles. My rule is to not apply meaning to an auto-generated number. It is just an identifier. Trying to force it into a system as well makes the system fragile.

What happens when your organization exceeds 100 system users?

@@adamschneider868 exactly

Speed of querying. Instead of doing a calculation for each of them when I need those values, I just ask for them. This is especially important when you have hundreds of sales and want to do a sales report. Instead of calculating values for each sale and then totaling them up, you just do the total. Also, by providing these values, we know what the actual sale value was at that point. If the tax rate changes or there is a slightly different rounding value, the numbers could look different then they actually were.

@@IAmTimCorey Tim, but normalisation eliminates these fields, which in effect reduces bandwidth and load on the DB server. Why don't we just have the client app do the calcs.

Normalization isn't done in a vacuum. You need to take into account how you will use a database, not just blindly normalize a database. In this case, it is more efficient to have these two fields rather than re-calculating them each time you need them. As for having the client app do it, there are a couple problems with that. First, that means you now need to load all of the transaction child records when doing a report, not just the summary record. That is a LOT more transmission across the wire (greatly increasing your bandwidth usage and your load on the database). Second, we are trying to avoid doing the calculations repeatedly in the first place. Also, since the calculation is what they paid, it is also a protection against future changes such as tax rates, product rates, etc. that might change what the total would become.

@@IAmTimCorey Ah yes, so where ever its practical, I get it. Thanks Tim.

@@parko1965 Ok, now you have one million saleslips which each have multiple items, what do you think would be faster? having to recalculate ALL totals, or just getting the totals itself. Also it has another benefit (not a situation you hope would happen), if a record in the item goes missing, you still have the original price (and you can have a check on that). I agree with you that there are more than enough times you don't record a total in a separate field.. But in this case I would also do it, so I only have to get the data from 1 table when I want to have a report of all the saleslips..

Your SQL project is expecting to publish to SQL Server 2016 but when you publish it, you are pointing it at a SQL Server 2014 server. To fix this, you can either point it to a different SQL Server to publish or you can go into the properties of your SQL project and change the target version to SQL Server 2014.

You are welcome.

@@IAmTimCorey Sir, I am waiting for the next video. Please upload soon.

There are foreign key relationships set up in our database.

I thought that even with Windows authentication that the record got an ID.

In the past, I got this to work using OPENQUERY in SQL, which uses LDAP to query Active Directory to get guids assigned to AD accounts. Below is the article I used to OPENQUERY in SQL. You could create a stored procedure, and then call it from the application: blog.sqlauthority.com/2016/03/30/sql-server-query-active-directory-data-using-adsi-ldap-linked-server/ Here is a Reddit post that will help if you have a large number of user accounts: www.reddit.com/r/SQLServer/comments/4dsck7/active_directory_query_for_users/ Being that it works with SQL, I figured there must be an assembly you could reference to do this. Sure enough I found an answer. Here is a StackOverflow response where they use the System.DirectoryServices.AccountManagement namespace to get the guid: stackoverflow.com/questions/27447823/get-guid-or-native-guid-from-active-directory You'll have to keep in mind that regardless of the direction you take, you'll have to be conscious of Active Directory permissions. And if you're using local user accounts (not Active Directory), the SQL solution won't work. But I believe the StackOverflow solution will, with some changes. You'll probably have to modify it since I don't think the domain context will work without a domain. Hope this answers your question

You might need to add a connection to the LocalDB. Normally it is mapped if you have it installed though. Check your installation of Visual Studio to be sure you have "Data Storage and Processing" installed. Go to the Tools menu and look under the first option "Get Tools and Features" to check. If it is installed, you will just need to open up SQL Server Object Explorer and use the Add Connection button at the top to add a connection to LocalDB (the same path as what you published to).

@@IAmTimCorey Thanks for the reply, that's basically what I did. But I needed to start localdb from the command prompt first. I followed the instructions listed here stackoverflow.com/questions/21563940/how-to-connect-to-localdb-in-visual-studio-server-explorer/30098918#30098918

I had an issue with this about a year ago but updating Visual Studio fixed it. Check to see if you have the latest update. If you do, you might have a bad installation.

@@IAmTimCorey It's the latest version. solved by reinstalling vs. Thanks!

We will be adding foreign keys, although there won't be cascading deletes, etc. attached since we won't be deleting records. As for the other columns you are wanting, these can be added later if necessary. Right now I don't think they are at this level. As we get bigger and want more features, maybe. As for the first part with who and when, that is better suited (in my opinion) to an archive table since last modified date/user only tells us the latest info, not a history. Not terribly useful. Instead, if I REALLY need it, I put an insert/update/delete trigger on the table that logs the new values, who did it, and when. That way you can get a history of the data all the way back to the beginning for any row. The concurrency issue isn't really an issue until we get into allowing offline access or until we start allowing heavy modifications of critical data. For instance, we won't be modifying purchases, inventory records, or pretty much anything except item quantities (and that will be in transactions and will be decrements, not overwriting the inventory amount). Therefore, we don't need heavy modification protection.

Great on the foreign keys. I do believe that cascading deletes have a place at the table even if the final product would rarely use them. I've seen too many instances where I need to delete test data where deleting a single row also deletes the dependent rows. It is a valuable time saver in that case and in future cases where you wish to move the data off. The other fields are included not so much for creating an audit history but for accountability. Are they necessary for every record type? No. But the process to provide the information simple. Having the information available from the start allows so many possibilities, e.g. closing the books, archiving, and reporting not to mention ETL. Concurrency, IMHO, is an issue as this application appears to be a SOA that could have multiple UI clients hitting it at some point. Might as well have the rowversion in place to handle the eventuality - consider Inventory where the same item needs to have the quantity reduced because of a sale. Thanks for considering. By the way, I learn something new from each of your lessons and I'm a Patreon.

I talked about this when we set up the guid to be the unique identifier. Yes, I would definitely do this in a large POS application because the way I did it is more performant. The trick with evaluating performance is to evaluate the whole system, not just one piece in a vacuum. In this instance, we lose a (very) little bit of performance by using a guid instead of an integer. Mainly on the insert of the record (which does not happen often - once per record). Lookups are just about as fast as integer lookups because the records are stored sequentially on disk and we only do individual record lookups by the ID. The difference, though, is in the lookups. We are going to have the guid of the logged-in user for every call. So, if we want to look up the user's details using my system, we just take that guid and look up the record (a seek operation, which is very fast). If we were to have used an auto-incrementing integer instead, we would need to look up the record by the guid to find the record still. That means doing a seek on a secondary index. Secondary indexes take additional resources on insert and you have to maintain additional records on disk. That means that the actual storage of the table resources will be more this way because of the additional covering index needed.

@@IAmTimCorey I'm sorry if my first comment was confusing. I completely agree with using the the guid as a primary key (in this case) instead of making this an int identity field for all the reasons you mentioned, on top of the fact that it has to be maintained. I'm a DBA, and at my last job, I had to clean up tons of data because we had 2 source systems that generated 2 completely different IDs; one generated guids, the other generated auto incrementing identity fields. So if our CRM needed to pull sales data from our POS system, it would throw an exception because the account in CRM had a null value in the field that stored the primary key (guid) of the account in the POS system. It didn't help that we weren't allowed to put a constraint to prevent the record from being created if that guid was missing. DBAs and developers are always at war because of stuff like this, lol. Anyway, my question was in regards to using a "uniqueidentifier" SQL data type, rather than using an "nvarchar" SQL data type to store the guid in the [Id] field in our User table. I've always worked with uniqueidentifiers as my data type for any guids, but I've never stored them as nvarchar because I was always taught that storing guids in varchar/nvarchar was bad. Compared to nvarchar/varchar, the performance is supposed to be better when doing lookups on fields that are uniqueidentifiers and uniqueidentifiers are supposed to take up less storage as well. So couldn't we just cast that nvarchar field to a uniqueidentifier data type?

​@@nogo1880 I haven't watched the rest of the series to know the answer for certain. However, nvarchar(128) is what is being stored in the auth system. Tim has just used the same data type so that they match. Assuming that we would be querying the auth table for our users, we wouldn't need the overhead of casting from uniqeuidentifier to nvarchar(128) in that direction.

This isn't database first. I am not using Entity Framework in this project. I prefer Dapper. It is typically faster and it is much less dangerous to use, in my opinion.

Interesting. Not sure why that it. I'll look into it.

I love that word!

Money is a byte cheaper than decimal on storage usage. Personally I'd use decimal (19,4) though

You would probably create a table that has sizes in it and then another that linked your items to what sizes they are carried in. So Items table (let's say it holds shirts), a Sizes table (Small, Medium, Large, etc. in rows), and ItemSizes table (that holds Shirt1 has a small, Shirt1 has a medium, etc. in rows).

Yep

That table is in a different database. I have a completely separate database for my authentication. If I were going to mix my authentication tables with the rest of my SQL code then I could do that but for a number of reasons, I prefer to keep the databases separate (better security, portability, etc.)

EF auto-creates that the first time you use it. Did you try logging in?

@@IAmTimCorey I must have done something wrong somewhere. I'm revisiting the "Creating a WebAPI with Authentication - A TimCo Retail Manager Video" video and the database has been created there. Thank Tim.

Completely up to you. Look at your time available to do the work/study, the deadlines you have and the amount of effort you plan to put in. If you can afford it, and are in a hurry, go for the paid version

That’s an interesting suggestion.

I don't add it in to the Claims or to the Identity because then it bloats the table and it is trying to do two things. I keep them separate and keep the lookups lean.

It is purely a preference thing. I prefer Product.LastModified to indicate that this is the date/time when it was modified last (indicating there may be modifications before this). I prefer not to do the abbreviations wherever possible (they don't read well). My preference is on clear, readable names that indicate what the data represents. ModifiedDate, CreatedOn, and ModifiedOn all do this well. I would have no problem using those.

To answer your questions, we store the email in the User table because that is where we will access it from. Essentially, we will never directly access the AspNetUsers table (we won't do queries against it). Since we will want to see the email address of the user commonly, it makes it easier to include it in both places. This does create a duplication of email but that isn't a problem. We know the source of truth (the authentication system) and we don't change the email frequently. As for including the person who last made a change to the record, that is possible and sometimes beneficial but it is of limited use (really, so is the LastModified but that is a bit different - we get to know if a record was just changed or not, which can be helpful). If you really want to know who did what, it would probably be good to create a history table that stores the record after each change so that you can see who made a change, what it was, and what the history of changes have been. You can create a trigger to do this for you and on REALLY important tables, it is valuable. Just remember that you are relying on the sender giving you a valid UserID and not lying about who it was (I could provide your GUID for the change and the system would accept it).

@@IAmTimCorey That makes sense. I had forgotten that we were accessing the UserID via RequestContext.Principal.Identity.GetUserId() instead of through a database query.

The bearer token isn't the ID. The GUID of the user is (which never gets reset).

I mirrored what Microsoft used for their table.

Int is more than enough. If you have to upgrade to bigint, you will have to do a LOT more updates to make your app performant before then. 2 billion sales would be beyond even Amazon.

@@IAmTimCorey So amazing! Thank you so much.

ULong is a mess. In SQL, we have BigInt, but it is signed (has the plus and minus capability). That is the equivalent of Long/Int64. The problem is that ULong is an unsigned long, meaning it starts at zero (no negative) and can be twice as large. The closest thing SQL has to store that is Numeric(20,0). The problem with that is that it isn't as efficient as an integer type and it is actually larger than you need. That's only a problem if you are creating data on the SQL side, not the C# side. If you create data on the SQL side, you need to limit the column to be between 0 and 18,446,744,073,709,551,615.

It should be that you just need to run Update-database from the package manager console but if that gives you an error, check out this post: stackoverflow.com/questions/20304058/how-to-re-create-database-for-entity-framework

We could, although I avoid triggers whenever possible because they can slow things down and cause unexpected results. In our case, I'll probably just do it manually.

@@IAmTimCorey true. And because we tend to forget it exists down the road when we haven't really messed with the app for so long. I work with sql server as my primary tool and I once had a trigger for a set of opperations, and that created a whole lot of problems and was harder to debug. It's a lot easier a simples stored procedure.

One item per row. We will have multiple rows per Sale.

@@IAmTimCorey thanks!

Okay, I don't know if this is the right way to do but I have found a workaround: 1. I added another ASP.Net WebAPI Project to the solution and called it TRMDataManager2 2. Done the updates, added Swagger. 3. On the newly created TRMDataManager2 project, I updated the ValuesController and SwaggerConfig classes to match the ones on the original TRMDataManager. And run in debug mode. Registered a random user. 4. Opened the Web.Config on the original TRMDataManager, and copied the server name at the connectionString line (for me it was aspnet-TRMDataManager-20201111085247.mdf) 5. Opened the {Project Directory}\TRMDataManager2\App_Data folder and copied those 2 files and pasted to {Project Directory}\TRMDataManager\App_Data folder and renamed it to aspnet-TRMDataManager-20201111085247 (it would be different for you) 6. Voila!

Did you save your publish profile to that folder?

@@IAmTimCorey anyway to direct me to when you created the publish profile? ive followed the series up until now and dont recall that... anyway thank you so much for this series, im learning a lot!

@@matan7618 I believe it was at the end of the last video.

Potentially dumb question, but are you saving your *Solution/Project* file after re-adding the files (i.e. "Save All")?

@@benezard1454 Not to silly, but yes I do a save all. When I add tables, etc. VS recognises that the sqlproj has been modified but there are no changes detected. The only way I have got it to behave is to publish the database, but before actually hitting the publish the button, to save the profile. Otherwise the sqlproj never gets updated.

Not sure why that is, sorry.

Click on the Show All Files button of your solution and then find your tables, right click on them and select Include In Project.

lol, yeah, I do.

Man, your A/C must be working great to cause that.