Posts by zachattack05

If I have 2 or more tables and I need to have another table that is related to the data in those tables...how would I achieve that?

As an example:

Table A and B have totally different data and aren't directly related at all. I would like Table C to relate to a record in either Table A OR Table B but not both. Like, if there is an entry in Table C it could reference the PK in Table A or Table B...but since those are different tables, the PK can duplicate and I'm stuck figuring out (which key is which?) would it be better to have columns in Table C called something like "TableAKey" and "TableBKey" and have a relationship between those columns and the respective tables? If the relationship belongs to one table and not the other, one of them is null and the other isn't?

Any ideas?

Argh!

What a pain in the rear!

Thanks for the link though, its a little help, but I just can't bring myself to edit tables in the diagram view...it makes me uneasy.

Is it possible to move the "description" column to the right of the "Allow Nulls" column in the table designer so you can see the descriptions for each table column without clicking through them one at a time?

Like the attached picture? Please, someone, for the love of all that is holy, tell me this is possible! It's so annoying! Even MS Access has this feature!

When I need to serialize things to a byte array I usually write the array to a MemoryStream first then use the binary formatter to serialize it to whatever (file, another byte array whatever).

The question I had was, can I just serialize a byte array instead of converting it to a memory stream and then back again?

The binary formatter says it needs a "stream" to serialize...is there another method?

True...I'll do that. For some reason I thought there was something built-in to VS that allowed you to generate data.

Can VS generate "junk" data in a database for testing purposes?

I could swear somewhere I saw it in a menu, but I can't find it now?

skatamatic,

Excellent idea and thoughts!

Actually, the data being served up would only be accessible by two people at once. Myself, sitting at the server, and my clients. The neat thing is, the data that I would edit is data they cannot edit, but can only view, and the data they can edit is data I could edit, but the whole point of the software is so that I don't have to. The only time that would/should happen is if they send an email to me or call on the phone and say "hey, change my contact phone number" and then at the same time they open the client up and update it themselves.

I do see your point though and I really do like your idea! I never thought about that. It's flexible so it could work for any table and any update type...thanks a ton! I never looked at it that way before! :)

Good point!

I've seen many different answers to this...is it possible to have a constructor for a class that returns a value?

If Server A and Client B are chatting through sockets, and Client B requests a dataset of all accounts on the server it has access to, the server provides that data set to the client (through serialization and sending it over the socket) and the client modifys the data, say it changes a contact name or address or whatever on one or many different records, and sends the dataset back to the server so that the server can update it's records...is there a built-in method to compare the two datasets and perform those updates on the parent data source?

Check out this diagram for what I mean:

Server A		| Client B		|
-------------------------------------------------
MS Access DB File	<= Gimmie data!
DataSet with Data	=> Modify the data
MS Access DB File	<= Save my changes
Okay! All saved!	=> :) [Smiley Face]

That's the general idea, but I'm trying to get the 3rd step to be a little less complicated than comparing row by row each update.

Is it possible, and this is a stretch I think...
Have the client receive the dataset, and then use a table adapter and bind the dataset to the form and when the client clicks the "save" or whatever button, just extract the SQL methods from the table adapter for the insert, update, delete and transmit the SQL commands over the wire, and have the server perform them?

Also, the communication is encrypted, so I'm assuming sending data …

Momerath,

Were you talking about something like this:

/// <summary>
        /// Prepares a Data object for transmission over the wire.
        /// </summary>
        /// <param name="data">The Data object to prepare</param>
        /// <returns>A byte array to send over the wire</returns>
        public byte[] PrepareForWire(Data data)
        {
            using (MemoryStream DataArray = new MemoryStream())
            {
                BinaryFormatter DataSerializer = new BinaryFormatter();
                DataSerializer.Serialize(DataArray, data);

                return DataArray.ToArray();
            }
        }

Does that cause the size of the array to become bloated?

Momerath,

Actually I guess I don't really HAVE to hard code them. I could use the bit converter for them as well, but I figured it was an unnecessary call. I always figured that when it came to a communication protocol (of any kind, network or between threads etc...) it's better to be "tough" and have a tight leash on things. One extra byte or two could accidentally change the entire meaning of the communication because someone didn't follow the protocol correctly.

Course I guess the same could happen here, but I dunno.

One thing that occurs to me now though is that if I need to modify the protocol for some reason (add something or remove something) or maybe change the type of data like from bool to an integer, I would have to go back and change the lengths I hard-coded by hand. If I used the bit converter and found the byte length I wouldn't have to do that, I would just have to modify the methods that encode and decode the stream.

Hmmm...Actually I would have to do that anyway, regardless of the setup...I think.

Wow, anyway, so no glaring issues? Seems like it would encode in the correct order?

I must say I did actually consider serialization, but I was worried that if I serialized the entire class that the recipient, if it was running a slightly older or newer version might run into major issues and they might …

So it's early and I always have anxiety over things like this, so I was hoping someone could take a quick look and make sure this would actually work.

I've got a socket program that will transmit a Data object over the wire. I just want to make sure that my bytes are in order and nothing obviously wrong is going on. I haven't gotten far enough into it to test it, I just wanted to head off any errors at the pass.

This is the method that converts a object of type Data to a single byte array to be transmitted:

/// <summary>
        /// Prepares a Data object for transmission over the wire.
        /// </summary>
        /// <param name="data">The Data object to prepare</param>
        /// <returns>A byte array to send over the wire</returns>
        public byte[] PrepareForWire(Data data)
        {
            using (MemoryStream DataArray = new MemoryStream())
            {
                // convert the data to byte arrays
                byte[] dIsSecret = BitConverter.GetBytes(data.DIsSecret);
                byte[] dCommand = BitConverter.GetBytes((int)data.DCommand);

                // write the arrays to the memory stream
                //skip the first 4 bytes, this is where the message length goes
                DataArray.Seek(4, 0);
                DataArray.Write(dCommand, 0, 4);
                DataArray.Write(dIsSecret, 0, 1);
                DataArray.Write(data.DMessage, 0, data.DMessage.Length);
                
                // find the length of our message
                int TransmissionLength = DataArray.ToArray().Length;

                // write the length of the message minus 4 bytes to the start of the memory stream
                DataArray.Seek(0, 0);
                DataArray.Write(BitConverter.GetBytes(TransmissionLength - 4), 0, 4);

                return DataArray.ToArray();
            }
        }

And here is a Data object and it's constructors:

/// <summary>
    /// Data object
    /// </summary>
    public …

jbrock31,

Hmmm...that's also a great idea!

The situation I have (the real situation) is thousands of medical patients, hundreds of doctors, nurses, PAs, and laboratory test codes, the substances tested in each code, test results for each substance, account numbers, chain/requisition numbers etc...it's a mess of relationships, but the problem I have is, medical records must be kept for x amount of time (5 years for most here) and that's fine, but that also means if a laboratory removes a test code (because they stop offering it) or if a doctor, nurse etc... quits, gets fired whatever....I can't delete the doctor, the test code etc...because a result is attached to them.

I think I'll need to just do the "soft delete" and have a routine that runs every month or so and checks result dates, deletes (or archives them out of the database) the older ones and then tries to remove the doctors, etc...that are marked with that soft delete flag.

I dunno...it's the best solution I can come up with right now.

EDIT: Oh, and I love the avatar btw :)

Good point!

Thanks as usual! :)

Momerath,

I considered the first solution. But isn't that a bad design? I mean, over the course of many years the size of the table would be ridiculous with old and outdated information. Wouldn't the performance of the system drastically suffer?

On the second solution. Good idea. :)

I know this makes no sense at all, but hear me out and maybe someone can supply me with some info on how this can be accomplished correctly.

Using an invoicing type example.

You have a table of clients who can receive invoices, a table of invoices with invoice numbers and client ids, and a table of invoice details with invoice numbers and items on those invoices and then an items table where details about the items are stored that are shown on invoices.

Now, assuming the system is heavily used and thousands upon thousands of records could be in any of those tables at one time or another, what happens if an item is discontinued?

With a relationship setup like this, deleting the item from the items table won't work, it will delete the item from all the invoices as well. Even if it could be deleted from the items table and you left the info in the invoices tables you have a reference to a key in the items table that doesnt exist. The invoice would refer to item # whatever, but if the item is removed from the items table you have no idea what that item originally was!

So, how can you make sure that any items added to a new invoice contain only available items, and the invoices that have discontinued items on them can still display what the item was, the cost etc?

Is this a situation where …

arsalanghouri;

Mitja is right.

Primary keys that are integers and have the identity property cannot be reduced. One of the best database design documents I ever found was from a website called "The Access Web" (not sure if it's still around) and they had a 10 commandments of database design. One of them was (edited to apply to SQL): "Thou shalt not use identity primary keys if the field is meant to have meaning for thy users." The key (number) is only there to identify that specific record/row in the database.

If you wanted, you could use a natural key instead of a surrogate key. This would allow you to change the key value and "cascade" that change (if you set the relationship up that way), but in reality, primary keys shouldn't change and outside of the database's relationships, shouldn't mean much to anyone so there's no reason to change the key anyway.

Probably no responses because there's missing info.

Where are you storing the input? Is it just stored in memory, in a file, sql server? Is this a Winform or web app? (I'm assuming web because you mentioned session data).

Also, what exactly are you trying to accomplish?

Cool beans!

Thanks!

Wait...so if I set ColumnA to 10 and ColumnB to 4 and then later want to change ColumnB to 6...it will throw an exception? Or are you saying it throws the exception on updates if the check constraint is violated?

Hey that's a clever idea skatamatic! I like it!

I'm assuming the check constraint will throw an exception and not insert anything?

Hmmm...tree views can have an image list associated with them. You might want to check that out instead, then you don't have to worry about pictureboxes.

Also, another consideration. The Microsoft Office icons are copyrighted and probably trademarked. I would be careful about including the icons in your application, but instead draw up the default icon for that particular file type on the computer your application is running on. Some users (like me) don't have MS Office at home, but use Open Office instead and the icons are different. Unless someone is familiar with the MS Office icons, they really lose their meaning.

EDIT: While it is true that Open Office has different file extensions, Open Office CAN open .xls files and they do not have the same icon at my house as they do here at work where MS Office is installed. Just a FYI :)

If my application was designed to generate a "schedule" or list of dates, and I wanted to store those dates in a SQL server as an array, what SQL data type would I pick? ntext seems excessive, and I don't think date would work...any suggestions?

You guys are always so helpful, this is like my "go-to" forum for bizarre questions :P

Not sure if this should be enforced by the SQL server or the Application itself.

If we have a table with 2 columns of type int, ColumnA and ColumnB, is there a way to make a rule that says ColumnB must be >= (greater than or equal to) ColumnA?

Is that something that should be enforced SQL side (if even possible) or application side?

I think I found a solution, but it seems messy.

Could someone take a look at the attached diagram and tell me if you see any improvements/problems?

I'd really appreciate it :)

ParentCompany - main table
Branch - FK to ParentCompany
BankBranch - FK to Branch, holds info specific to banks
ConstructionBranch - FK to Branch, holds info specific to construction
Addresses - Branch would have 1 or more of these

You might need ParentBank and ParentConstruction to hold the info that is unique to those types.

This will allow you to add more types of buisnesses.

I hate to ask, but is there a way you could draw a diagram showing these relationships? I think I'm having problems visualizing this.

OK, I know I'm resurrecting a thread over a week old, but it's better than starting a new one...

This is making me crazy, I can't figure this out.

I'm still working on the same problem.

I have three tables (at least for now): Banks, BankBranches and Addresses.

The PKs for the tables are:

Banks: ParentCode
BankBranches: ParentCode, BranchCode (2 columns, 1 PK)

the addresses table doesn't have a PK yet, that's where my problem lies.

The addresses table will be a catch all for all of the addresses that the system uses, including addresses for Bank Branches and Construction Company Branches (like in the examples before).

The Construction Companies will also have two tables and PKs:

Construction: ParentCode
ConstructionBranches: ParentCode, BranchCode (2 columns, 1 PK)

The problem I'm having is getting a unique enough key into the addresses table.

Since the Parent Code for a Bank, and the Parent Code for a Construction Company are only unique to those particular tables and not unique overall, creating an address table that uses the ParentCode and BranchCode for the PK just won't work.

I thought of creating a column in the address table called something like "AddressGroup" or something so the system could figure out which ParentCode, BranchCode combination we are talking about.

Here's an example of what I mean:

Addresses
----------------------------------------
ParentCode | BranchCode | AddressGroup | (All 3 of these columns would make …

Wonderful!

Thanks!

Are there any performance considerations when using these keys in combination with FKs and cascading updates/deletes?

Still working to figure this stuff out :)

My question is, is it possible to set a combination of columns with a unique constraint?

For example:

ColumnA | ColumnB
-----------------
Value1  | Value2     <- OK
Value1  | Value1     <- OK
Value2  | Value1     <- OK
Value2  | Value2     <- OK
Value1  | Value2     <- Not OK, combo already exists in the table

Any ideas? Or is this something that needs to be enforced in the logic layer?

Momerath,

Thanks for clarifying that. I appreciate it :) I'm starting to catch on to this SQL stuff :)

Hmmm...

I must preface this and say that I truly believe XML is the spawn of Satan and I do everything I can to avoid it like the plague, but I'll offer what I can.

Is it possible to do something like this:

DateTime _SomeTime;

[XmlElement("some-time")]
public DateTime CancelledAt
{
    get { return _SomeTime; }
    set { _SomeTime = value ?? //insert some default value here; }
}

or

DateTime _SomeTime = DateTime.MinValue;

[XmlElement("some-time")]
public DateTime CancelledAt
{
    get { return _SomeTime; }
    set { _SomeTime = value; }
}

If this is not helpful, can you post your source where the exception is occuring and post the XML file it is reading?

If programming languages were drugs, VBA was my "gateway drug" :P

Hi, my name is Zach, and I'm a programmer. I wrote my first "Hello World" program when I was 12 and I've been addicted ever since...Sorry, got caught up in the joke there.

Anyway, do you have any words of wisdom for this? I understand that it's okay if things are imported properly, but now my question is, if using a PK that the user can set will prevent the error from ever happening to begin with, why would you bother taking the risk of improper imports?

A user defined PK will prevent breaks...or am I missing something?

Is it bad practice to create relationships between tables where the PK is an identity AKA auto incremented?

The reason why I ask is, while identity keys won't repeat, they are set based on the order that someone enters data, so the first entry is 1, second is 2 etc...

If the data is exported, or somehow backed up and needs to be restored, is there a chance that those numbers could change?

For some reason I seem to remember someone saying back in my VBA MS Access days that PK shouldn't be auto increment. I can't remember why though?

No problem :)

No, you can't make it inbetween, but what you can do is pad the key or truncate the key with data to make it fall into the required length.

That way if a user enters the key: "HelloWorld!" which is only 88 bits, you can have your application pad the data with say "0" until the length is 128 or 192. The user would still type "HelloWorld!" but the key is changed to: "HelloWorld!00000".

Same with a key that is too long, just drop characters from either the front or back of the string until you reach the right length.

Something else you might consider, taking the user's input, and hashing it with a provider that returns 128 bits. MD5 is one. I attached a table from Wiki that shows hash functions and their output size in bits.

Also, something else...

I'm not really sure what you plan on using your encryption for, but if it's something very important to your user or to you, you really shouldn't hard code the key into the program.

The CSP also has a GenerateKey() method that I would HIGHLY recommend you read up on. :)

I've used it before and it works nicely, that way you get a random key each time the data/object is encrypted.

I can post some example code of one I've used before if you like.

Correct.

It's one or the other. The MSDN says:

This algorithm supports key lengths from 128 bits to 192 bits in increments of 64 bits.

Since it's in 64 bit increments you have 128, or 128 + 64 = 192.

Is it necessary for you to use XML? What is it you are doing or trying to do?

jazerix is 56 bits, because each character in your string is 1 byte, or 8 bits. 7 * 8 = 56 :)

Just make your key longer or if the user can enter the key themselves, create a function to either pad the key with a value, or truncate the key to meet the length requirements.

Keep in mind though, not ALL characters are equal, UTF-8 encoding can take up to 4 bytes/character depending on the character.

I attached a little project I made that you can run that will calculate the size of your string as you type it. Dunno if it will be useful, but meh.

Question 1: Why a console app and a winform app? I think it might be easier to have just a winform app and that's all unless the console one does something special, but I don't see why it couldn't be combined.

Question 2: What's your goal in doing something like this? Seems odd to me. While it can be done, there might be a better, easier, more elegant way of doing whatever it is your trying to do.

Actually it WOULD be possible for many different branches to have one address. For example a company might want all bills for all branches to go to one address, or all communications regarding x, y and z to go to the same address regardless of the branch.

Surprisingly that does happen. It's not so much a "hard" relationship between a branch and address, it's more of a "related to..." type relationship.

I think I'm going to play around with this a bit.

Thanks a ton for both of your help and suggestions! I really do appreciate it! This is why I love this forum!

Momerath,

So you would have a FK in Addresses to Branch as well?

I'm assuming the "Branch" table would just hold a primary key to bridge the gap between the "ParentCompany" table and the various other "BankBranch" and "ConstructionBranch" etc... and a FK to the ParentCompany tables right?

No, they aren't the actual objects. I'd prefer to not state what the objects really are, but for everyone's sanity and to get the right help/suggestions I'll provide as much as needed but as little as possible. They function in a similar way though.

Assume for a moment that you are a business that provides a certain service, and you need to keep a listing of all your clients that use your service so you have a table that has basic information about your client, mostly their company name and an account number that you assign to them.

Now, because your clients sometimes have multiple "branches" you need a way to relate the "Parent" company to the "Branch." Think of the clients like banks, you have Bank of America as an entity (goes in the Company table) and you have the Bank of America on 5th Street in Seattle Washington. So the "Branches" table would hold all of the branches for a company that exist. Each branch though, depending on its size, could have any number of addresses: a billing address, a physical address, a ship to address, a customer service address etc... so great, we have 3 tables:

#1: Companies - Holds general info about the parent company that applies to all branches.

#2: CompanyBranches - holds unique info that applies only to the one branch.

#3: Addresses - holds addresses for all branches (the entry in the Companies table, assuming the Company has …

Thanks Momerath, I have seen that demonstrated before, but I'm not sure if it will achieve what I am after, but I'll have to see...

gusano79,

No, the model you gave is missing a part and a rule. Let me clarify:

One Parent table row can have multiple Children table rows.
One Children table row can have multiple Address table rows

One SchoolDistrict table row can have multiple School table rows.
One School table row can have multiple Address table rows.

The missing rule is, if the School or Parent table has a row, it MUST have at least one row in the SchoolDistrict or Children tables.

For each row in the School and Children tables, there MUST be at least one Address table entry.

Some of these rules are enforced in the business logic and not on the SQL server, but what I need from the relationships setup is so that if a parent or school district is changed or deleted, it cascades down all the way to the address table.

I attached an image that partially represents what I am trying to achieve.

I am in desperate need of some guidance here. Any suggestions or ideas would be very much appreciated!

Is it possible to have a relationship based on two columns? For example:

The table "Parents" has columns:
ParentID (PK)

The table "Children" has columns:
ChildID (PK)
ParentID

The table "Addresses" has columns:
ChildID
ParentID
Address
City
State
Zip

The table "SchoolDistricts" has columns:
ChildID (PK) (Don't let "Child" fool you, it has nothing to do with the Child, it's a "child" of a "Parent" School)
ParentID (there's another table called Schools that is related to this column)

(I know, it's a lame (and probably confusing) example, but the question remains)

Is it possible to create a relationship between the Addresses table and the Children table using BOTH ChildID and ParentID, as well as a relationship between the SchoolDistricts table and the Addresses table using BOTH ChildID and ParentID?

Maybe it's poor design? Is there a better way to do this maybe?

I need to have relationships set up similar to this:

One "Parent" can have many "Children"
One "Child" can have many "Addresses" (amuse me, it happens)
One "School District" can have many "Addresses"

Am I going about this the wrong way? Do I need two tables instead? One to store Children addresses and one to store School District addresses? Seems silly to do that considering they …

I use e just because I think VS uses it by default, instead of confusing future viewers of my code, I keep the e. Chances are high that they will know what it is when reading an event triggered method.

Could just be me being naive though.

I noticed in the Advanced Properties of the connection string dialog, there is a setting for AttachDbFilename (see picture attached).

The question is, in this case, the file resides on the E:\ drive of my local system. When the application is finished and deployed, will my application's connection string still refer to the E:\ drive? Should I move the MDF file to the SQL data folder or can I leave it where it is and just copy the MDF file to the application's directory and the connection string will update when the application is installed?

I guess the bottom line is...do I need to worry about this or will the database attach correctly on install when the project is finished?