Please register to participate in our discussions with 2 million other members - it's free and quick! Some forums can only be seen by registered members. After you create your account, you'll be able to customize options and access all our 15,000 new posts/day with fewer ads.
And the second example ... I showed a simple set of tables used for a basic POS Transaction.
And there was NOTHING wrong with these tables.
Even the data which you claim to be unneeded and redundant:
Well...you only claimed it was a POS transaction after I corrected you. Originally it was a "Sales transaction DB", which is not necessarily a POS application.
Besides that point, yes, your sample is still structurally flawed and will violate normalization rules, especially non-key dependencies. In fact, you just might violate 1, 2 and 3 NF's.
Quote:
are part of the PRIMARY INDEX for both these transaction tables. They allow this INDEX to be unique.
Again, there is no need to store
Sales Date
Store Number
Register Number
Transaction Number
in multiple tables. It is a waste of space and is redundant. And, there is no need to create a primary key based on those attributes, in fact it is silly. All you really need is a a unique trans/inv number in combination with the custid and possible date. Your tables are filled with fluff. Surely, you must know you don't need a primary key to create a unique index?
Quote:
SKU is not always present or valid (it can be a dummy sku), so it can't be relied on, so it is important to retain as much about the description of merchandise at the POS transaction level. But this is a business related issue ... not a database issue.
Please, this is preposterous. A product, or merchandise in your example, must have a unique identifier, like a SKU or product number. How else would you identify and maintain the products in your inventory - certainly not by name, that would be absurd. Hint; what do you think all those barcodes on basically everything you buy are for?
There is absolute NO need to have anything to do with merchandise, other than the SKU, in that last table of yours. It is extremely poor design. You see, none of those attributes have a thing to do with a sales transaction - they have everything to do with the SKU, since they describe the merchandise - dept, style, class. They do not belong there, period. They are dependent on the SKU alone. That would be 3NF.
Quote:
Wrong
Here's my answer again. You have a habit of changing your criteria when things don't work out.
Since your first example of duplication didn't fly, I suppose you must drill down until you find something, anything.
Quote:
Both pass program validations, and get entered into the database. The two entries generate two different recipient ID numbers (the primary key), and so they are treated as two separate companies.
Quote:
Completely ... OUT OF THIN AIR ... you begin talking about DATABASE DESIGN ????
Well, not really. That is kinda what we have been talking about regarding recovery.gov. Looking at your example again, I doubt that is how it would go down. You were attempting to make the point of how data could be entered into a DB and then require cleansing. I was making the point that it would never be entered that way in the first place. There would be no "amt" at the stage of adding a "new" financial institution - most likely there would be an application process that dealt just with the bank, address, etc.
Quote:
And just for the record... the Sales Clerk who is working at the POS terminal in the store DOES NOT KNOW the Customer ID.
Your POS example. You brought up the POS (muddying the waters again) example in regards to a custid. We were not discussing POS until you went on a tangent to explain that custid would be an irrelevant piece of data. I said not in all applications, but yes, in a POS it would be.
You've snarled this up with your ever-expanding examples of meaningless crap that has no relationship to recovery.gov, but that I have unfortunately responded to.
Quote:
Humor In The Workplace Seminars; Solicitation from the Government (obama)
Oh, btw, have you been to the agency site that requested this program? It's called the bureau of public debt.
Well...you only claimed it was a POS transaction after I corrected you. Originally it was a "Sales transaction DB", which is not necessarily a POS application.
Besides that point, yes, your sample is still structurally flawed and will violate normalization rules, especially non-key dependencies. In fact, you just might violate 1, 2 and 3 NF's.
There is absolutely nothing abnormal about my sales tables.
And it doesn't matter whether they are POS or Sales. They both contain essentially the same information. I've worked with both for many years, and I know this for fact.
You also continually feel the need to state you "corrected me".
Sorry ... no such luck.
Nothing is flawed about my tables. But you can go on believing that they are.
Quote:
Again there is no need to store
Sales Date
Store Number
Register Number
Transaction Number
in multiple tables. It is a waste of space and is redundant. And, there is no need to create a primary key based on those attributes, in fact it is silly. All you really need is a a unique trans/inv number in combination with the custid and possible date. Your tables are filled with fluff. Surely, you must know you don't need a primary key to create a unique index?
Yes ... there is. But if you don't work with Customer Transactions, you probably won't know this.
Quote:
Please, this is preposterous. A product, or merchandise in your example, must have a unique identifier, like a SKU or product number. How else would you identify and maintain the products in your inventory - certainly not by name, that would be absurd. Hint; what do you think all those barcodes on basically everything you buy are for?
There is absolute NO need to have anything to do with merchandise, other than the SKU, in that last table of yours. It is extremely poor design. You see, none of those attributes have a thing to do with a sales transaction - they have everything to do with the SKU, since they describe the merchandise - dept, style, class. They do not belong there, period. They are dependent on the SKU alone. That would be 3NF.
So I see you don't read well. You missed the part where sku's aren't always valid in POS data.
And, once again ... you need to understand the specific business reasons behind it for saving dept, class, style and sku at the transaction level.
It is not simply a technical decision, as you seem to think it is.
Quote:
Here's my answer again. You have a habit of changing your criteria when things don't work out.
Since your first example of duplication didn't fly, I suppose you must drill down until you find something, anything.
Well, not really. That is kinda what we have been talking about regarding recovery.gov. Looking at your example again, I doubt that is how it would go down. You were attempting to make the point of how data could be entered into a DB and then require cleansing. I was making the point that it would never be entered that way in the first place. There would be no "amt" at the stage of adding a "new" financial institution - most likely there would be an application process that dealt just with the bank, address, etc.
Your POS example. You brought up the POS (muddying the waters again) example in regards to a custid. We were not discussing POS until you went on a tangent to explain that custid would be an irrelevant piece of data. I said not in all applications, but yes, in a POS it would be.
You've snarled this up with your ever-expanding examples of meaningless crap that has no relationship to recovery.gov, but that I have unfortunately responded to.
And you think YOU are the only one wasting your time in this thread?
Quote:
Oh, btw, have you been to the agency site that requested this program? It's called the bureau of public debt.
You use the "Obama" = "Government" only when it suits your needs. Otherwise it's "Tax Payer" = "Government.
You also continually feel the need to state you "corrected me".
Sorry ... no such luck.
Nothing is flawed about my tables. But you can go on believing that they are.
I've pointed your errors out - trying to explain to you exactly where you are going wrong, not just according to me, but according to basic, fundamental rules in DB normalization that adheres to the relational model. I can just imagine the god awful mess you might have.
Quote:
Yes ... there is. But if you don't work with Customer Transactions, you probably won't know this.
That won't fly buddy. Repeating data, redundancies and non-key dependencies are not specific to a particular application - it just means someone didn't know what they are doing when creating the physical model.
Quote:
So I see you don't read well. You missed the part where sku's aren't always valid in POS data.
Well, there must be some kind of unique identifier - whether it is a SKU, product number, etc, even in a POS app. POS like grocery stores, clothing stores, sporting goods and the like all have some kind of numerical identifier associated with a particular product. How could they maintain an accurate inventory? I guess you've never asked store personnel to check stock for you, in that particular store and in others - how do you think they do this?
It doesn't matter if it's a POS app - there is NO good reason to have any of the attributes that describe a particular piece of merchandise stored over and over and over again in a Salestrans, they don't belong, it wastes space and it violates 3NF, at least.
Again, if you are talking about seeing this information on an invoice/screen - yeah - it would be retrieved from it's own little table based of the SKU/Prodnum. This is simple, fundamental DB 101.
Quote:
And, once again ... you need to understand the specific business reasons behind it for saving dept, class, style and sku at the transaction level.
There is none I can think of. And a bunch I can think of for NOT putting it there.
I've pointed your errors out - trying to explain to you exactly where you are going wrong, not just according to me, but according to basic, fundamental rules in DB normalization that adheres to the relational model. I can just imagine the god awful mess you might have.
Uh .... no. You pointed out errors based on your own MISGUIDED ASSUMPTIONS about my tables, and the associated data.
Your problem is, you like to make comments WITHOUT KNOWING ALL THE FACTS.
Instead of ASKING the appropriate questions about the data, you just go based on YOUR OWN MISGUIDED ASSUMPTIONS.
That is NOT a good quality to have as an Applications Developer.
The FIRST THING you should do when you develop a database, is FULLY UNDERSTAND the data that will be stored.
Once again ...below are the Customer Database tables:
Customer Table Customer ID
Customer Name
Customer Address
Customer Phone Number
Sales Transaction Header Table Customer ID
Sales Date
Store Number
Register Number
Transaction Number
Payment Type (cash/credit)
Credit Card Number
Sales Transaction Details Table Customer ID
Sales Date
Store Number
Register Number
Transaction Number
Line Item Number
Merchandise Dept
Merchandise Class
Merchandise Style
Merchandise SKU
Quantity
Sales Amount
The primary key of the Customer Table is Customer ID. It is a UNIQUE value.
The key of the Sales Transaction Header Table is:
Customer ID
Sales Date
Store Number
Register Number
Transaction Number
It represents a UNIQUE value. If ANY of these columns were not present in the key, there would be duplicate keys in the data.
The primary key of the Sales Transaction Details Table is:
Customer ID
Sales Date
Store Number
Register Number
Transaction Number
Line Item Number
It represents a UNIQUE value. If ANY of these columns were not present in the key, there would be duplicate keys in the data.
This Customer Database contains data for many customers, shopping in many stores, using many registers (POS terminals), having many transactions, over many sales dates, with many sales lines per transaction (Line Item Number).
You also NEED TO ASK what the data types will be for all the data, and what range of values these columns may contain. Can they be stored as Integer, SmallInt, Alpha, etc.?
Here is info from a randomly selected Database Rules website, just for a reference:
The relational model dictates that each row in a table be unique. If you allow duplicate rows in a table, then there's no way to uniquely address a given row via programming. This creates all sorts of ambiguities and problems that are best avoided. You guarantee uniqueness for a table by designating a primary key—a column that contains unique values for a table. Each table can have only one primary key, even though several columns or combination of columns may contain unique values. All columns (or combination of columns) in a table with unique values are referred to as candidate keys, from which the primary key must be drawn. All other candidate key columns are referred to as alternate keys. Keys can be simple or composite. A simple key is a key made up of one column, whereas a composite key is made up of two or more columns.
Quote:
That won't fly buddy. Repeating data, redundancies and non-key dependencies are not specific to a particular application - it just means someone didn't know what they are doing when creating the physical model.
Primary Key data IS REPEATED. How else would you expect for it to be a UNIQUE KEY?
I would have EXPECTED you to know this a supposedly "experienced" developer.
Quote:
Well, there must be some kind of unique identifier - whether it is a SKU, product number, etc, even in a POS app. POS like grocery stores, clothing stores, sporting goods and the like all have some kind of numerical identifier associated with a particular product. How could they maintain an accurate inventory? I guess you've never asked store personnel to check stock for you, in that particular store and in others - how do you think they do this?
Once again ... you NEED TO ASK QUESTIONS before making ASSUMPTIONS.
Do you ever meet with your USERS to understand how their SPECIFIC business works?
For some businesses, SKU can contain potentially a dummy value if the SKU is unknown during a sale or refund (tag was removed), and the store clerk is unable to identify the item. In this case, the important value might be the Dept Number. All of the other identifying columns might contain dummy values.
You can't just ASSUME that everything will always function PERFECTLY in the business world, when it comes to data processing. You have to allow for unexpected conditions to be handled.
Quote:
It doesn't matter if it's a POS app - there is NO good reason to have any of the attributes that describe a particular piece of merchandise stored over and over and over again in a Salestrans, they don't belong, it wastes space and it violates 3NF, at least.
In the case of a Sales Transaction Table, I am not repeating the full NAME of the department ... only the department number, which is typically 3 digits in length.
As I mentioned above, the SKU may be a DUMMY VALUE, or even missing, so the other attributes about the merchandise item (Dept Number, Class, Style, Size, etc) are saved at the transaction level as well.
You may think they violate relational database rules, but these rules DO NOT always work with ALL businesses under ALL CONDITIONS.
That is why you NEED to ask questions of the USERS (aka Clients) to FULLY UNDERSTAND the data.
Having these values (dept, etc) present in the Transaction Level Data also speeds up processing (no need to join huge transactional tables containing hundreds of millions of rows to small mdse master tables) for certain applications which process the entire table or a large part of it , and it also helps retain the history of the transaction (what would happen if a SKU was accidentally deleted from a SKU master table, and then the same SKU number was later was re-added as a totally different item in a different department?). Can you imagine how that would affect the transaction history ?
$18 MILLION for website re-design - how do they justify this?
While it is quite expensive for a website redesign, it is something that needs to be done by a reputable company with high-level security, so there are added costs in that...you couldn't just hire a homeless programmer to do it.
That being said, people don't realize how little of a number $18,000,000 is. There are 300,000,000 people in this country, so that means this website is costing us 6 cents each. I don't know about you all, but I've certainly left 6 cents on the counter at the convenience store this week just because I didn't want 2 pennies in change from my $1.98 transaction. Surely nobody is going homeless from a 6 cent expenditure.
If you want to talk about government waste and costs that have taken their toll on our economy, why not mention the ridiculous (and futile) drug war? We've spent ~$28,000,000,000 this year SO FAR on the pointless drug war, surely I'm feeling the cost of my $100 to the drug war a lot more than my 6 cents to the website. I WISH all government expenditures were in the tens of millions instead of the tens of billions!
When the "budget" is over one trillion, eighteen million is a miniscule number.
I would have EXPECTED you to know this a supposedly "experienced" developer.
Oh, I know all about keys - it's just your example is still wrong.
Quote:
The key of the Sales Transaction Header Table is:
There is no need for those attributes to be part of a primary key. Your transaction number should be a unique value and along with the custid, which is the primary key of the customer table and a foreign key in the Salestransheader table, would make up the primary key in salestranshead table and possibly the date as well.
The store and register are completely irrelevant.
Quote:
It represents a UNIQUE value. If ANY of these columns were not present in the key, there would be duplicate keys in the data.
Incorrect. Custid and transnum/invnum would suffice as a unique value in preventing duplicates. As I said above, a transaction number would be a unique value in and of itself - combined with a unique custid and viola! you have it. The other attributes are basically unnecessary fluff.
Quote:
The key of the Sales Transaction Details Table is:
Customer ID
Sales Date
Store Number
Register Number
Transaction Number
Line Item Number
Again, it is a complete waste of space and performance to duplicate these attributes in another table, in addition to all the merchandise attributes you would be storing over and over again. The only values necessary here for a primary key would be custid,transnum,SKU,linenum - the rest is extraneous data that is not conducive to proper table structure.
Why would you have all the merchandise data stored over and over again in this table? All you need is the SKU, which would be validated against it's very own table. These attributes are dependent on the SKU alone, they have nothing to do with a sale - they describe a product. This is extremely poor design.
Quote:
Primary Key data IS REPEATED. How else would you expect for it to be a UNIQUE KEY?
The way I explained.
Quote:
You also NEED TO ASK what the data types will be for all the data, and what range of values the columns may contain. Will they be Integer, SmallInt, Alpha, etc.?
No! You're kidding!! I never knew that.
Off on another tangent, I see?
I have shown you over and over again how you are misinformed/misguided in your knowledge of what is good/bad design and yet you never address the issue of rules violations, which your example clearly violates 1NF, 2NF and 3NF - you just keep deflecting.
Quote:
In the case of a Sales Transaction Table, I am not repeating the full NAME of the department ... only the department number, which is typically 3 digits in length.
See, here you go again, changing those goal posts when I point out your errors;
Quote:
Merchandise Dept
Merchandise Class
Merchandise Style
Merchandise SKU
Nowhere do you mention "3 digits". It does not matter how many digits you are repeating - the point is the above does not belong in a transaction table. You can scream it to the rafters - it will still be wrong - they are only dependent on the SKU, not the transaction - violation of 3NF.
Quote:
As I mentioned above, the SKU may be a DUMMY VALUE, or even missing, so the other attributes about the merchandise item (Dept Number, Class, Style, Size, etc) are saved at the transaction level as well.
Nonsense. How can you have a product that cannot be uniquely identified? How will you maintain your inventory? Are you going to say now that it would not matter? Isn't the point of any POS system to sell....product?
Quote:
You may think they violate relational database rules, but these rules DO NOT always work with ALL businesses under ALL CONDITIONS.
They do, no argument about that. Any developer I know would be laughed out of the room if he/she put together a table like that. It is Database 101.
Quote:
That is why you NEED to ask questions of the USERS (aka Clients) to FULLY UNDERSTAND the data.
Clients selling products are interested in selling those products, maintaining their inventories and being able to replenish their stock - all based on a particular product.
Quote:
Having these values (dept, etc) present in the Transaction Level Data also speeds up processing (no need to join huge tables with hundreds of millions of rows) for certain applications which process the entire table or a large part of it , and it also helps retain the history of the transaction (what happens when a SKU is accidentally deleted from the SKU master table?)
I think this says it all. You must realize that would never be allowed to happen? If a SKU (unique product) in a product table has a relationship in another table (salestrans), which it would by way of SKU as a foreign key, - it could not be deleted without first ensuring that the salestrans records with that particular SKU are not orphaned. This is called Referential Integrity, something you are obviously not aware of or thought much about.
Your edited question;
Quote:
(what would happen if a SKU was accidentally deleted from a SKU master table, and then the same SKU number was later was re-added as a totally different item in a different department?). Can you imagine how that would affect the transaction history ?
As I stated above, it would not be allowed to happen in a correctly modeled DB. As long as a SKU from a product table has detail records in a transaction table, it could not be deleted without first taking care of the detail records with a cascading delete. I would have thought an experienced DBer like you would have known that. On second thought, based on your other assumptions about good design practices, I take that back. Btw, the same applies to adding a transaction without the SKU/ProdNum validation - it must exist in the Prod table first.
Quote:
For some businesses, SKU can contain potentially a dummy value if the SKU is unknown during a sale or refund (tag was removed), and the store clerk is unable to identify the item. In this case, the important value might be the Dept Number. All of the other identifying columns might contain dummy values.
If a customer is bring in an item without a tag or SKU, they must first identify the unique SKU or product number to return it to inventory. You are really just digging the hole deeper as you try to justify your wrong-headed thinking on this. Do yourself a favor - think about a grocery store, a sporting goods store, a clothing store - all excellent example of POS applications.
Oh, I know all about keys - it's just your example is still wrong.
There is no need for those attributes to be part of a primary key. Your transaction number should be a unique value and along with the custid, which is the primary key of the customer table and a foreign key in the Salestransheader table, would make up the primary key in salestranshead table and possibly the date as well.
The store and register are completely irrelevant.
I can tell that you have absolutely no CLUE about how retail stores maintain data.
Transaction Number is an ABSOLUTE MUST for a Customer Transaction Table. It by itself is NOT unique number. It is obtained DIRECTLY from a POS Terminal. It is the SAME number printed on the customer's sales receipt document. Transaction Number is generated by the POS Terminal. It is a function of the POS software, not the Application Sales Programs that eventually read the data, and load it to the database. The Transaction Number recycles back to 1 after it reaches the maximum digits allowed for this number, which is a number selected that is greater than the maximum transactions which could be done in a single sales date at a single register.
If a store was constantly connected to the internet, and had the ability to obtain a transaction number from a master table, then it could be a completely unique number. But in the real world, you can't expect internet availability 24/7, and you can't close a retail store simply because it's internet connection is down.
So once again, YOU HAVE TO UNDERSTAND THE DATA and the BUSINESS RULES for that specific company to make decisions about the database design.
In my example, the Store + Sales Date + Register + Transaction Number + Line Item Number are necessary to maintain a unique key at the transaction details level. And Customer ID is included, since it is a Customer Table, although this field is not required to make the key unique.
Quote:
Custid and transnum/invnum would suffice as a unique value in preventing duplicates. The other attributes are basically unnecessary fluff.
Unnecessary fluff? LOL!
Quote:
Again, it is a complete waste of space and performance to duplicate these attributes in another table, in addition to all the merchandise attributes you would be storing over and over again. The only values necessary here for a primary key would be custid,transnum,SKU,linenum - the rest is extraneous data that is not conducive to proper table structure.
Why would you have all the merchandise data stored over and over again in this table? All you need is the SKU, which would be validated against it's very own table. These attributes are dependent on the SKU alone, they have nothing to do with a sale - they describe a product. This is extremely poor design.
I've concluded that discussing retail databases with you is a complete waste of my time.
Once again, you obviously don't understand the data, and are only working off your own MIS-GUIDED ASSUMPTIONS about the data.
You never ask questions ... you only give answers.
That is NOT how a good developer works.
Speaking of being laughed at by other developers ...
Anytime you are ready to discuss recovery.gov 2.0 based on the ACTUAL Requirements Doc ... let me know ...
Transaction Number is an ABSOLUTE MUST for a Customer Transaction Table. It by itself is NOT unique number.
Can you read? I never said that - look again.
Quote:
I can tell that you have absolutely no CLUE about how retail stores maintain data.
I think I have already demonstrated what you have no clue about. Namely, proper DB concepts and practices that do not violate every rule of the relational model, which yours certainly do. First it was a stimulus recipient example, then a customer example, then a sales transaction system, then that morphed into a complete, ready to go POS along with warehousing capabilities - you just keep jumping around to try and make your point. Neither of those applications have a thing to do with this website and the underlying system supporting it and yet you continue to prattle on and on about a POS app that is meaningless. Whenever I point out a gross flaw in the examples you post, you then dig the hold a little deeper and go off on a tangent.
Quote:
I've concluded that discussing retail databases with you is a complete waste of my time.
Once again, you obviously don't understand the data, and are only working off your own MIS-GUIDED ASSUMPTIONS about the data.
You never ask questions ... you only give answers.
This is hilarious! After I've shot down your numerous assumptions - like having repeating, redundant values as well as your totally flawed table with non-key dependencies - and then you went on and on about a phantom SKU that was not necessary to identify, well anything, in a retail application? Who do you think you are fooling? Imagine that? Not having a means to identify a product - oh god, I can just imagine what a god awful mess your "shop" might have on their hands.
Shall we just forget this grossly misunderstood concept of yours regarding the addition and deletion of "keys"?
Quote:
Having these values (dept, etc) present in the Transaction Level Data also speeds up processing (no need to join huge tables with hundreds of millions of rows) for certain applications which process the entire table or a large part of it , and it also helps retain the history of the transaction (what happens when a SKU is accidentally deleted from the SKU master table?)
(what would happen if a SKU was accidentally deleted from a SKU master table, and then the same SKU number was later was re-added as a totally different item in a different department?). Can you imagine how that would affect the transaction history ?
and my instruction of just how much you do not know?
Quote:
I think this says it all. You must realize that would never be allowed to happen? If a SKU (unique product) in a product table has a relationship in another table (salestrans), which it would by way of SKU as a foreign key, - it could not be deleted without first ensuring that the salestrans records with that particular SKU are not orphaned. This is called Referential Integrity, something you are obviously not aware of or thought much about.
As I stated above, it would not be allowed to happen in a correctly modeled DB. As long as a SKU from a product table has detail records in a transaction table, it could not be deleted without first taking care of the detail records with a cascading delete. I would have thought an experienced DBer like you would have known that. On second thought, based on your other assumptions about good design practices, I take that back. Btw, the same applies to adding a transaction without the SKU/ProdNum validation - it must exist in the Prod table first.
Sorry, but any developer worth his salt would know about a basic, fundamental constraint that would not allow your scenario of adding/deleting primary keys in tables with a relationship.
This is just DB 101, buddy. How do you explain your ignorance of such a concept? Wait-don't answer that on line - PM me.
Quote:
Speaking of being laughed at by other developers ...
You wouldn't just be laughed at, you would likely be fired as incompetent and for sure never hired if you could not understand such a basic concept.
Last edited by sanrene; 07-20-2009 at 02:56 PM..
Please register to post and access all features of our very popular forum. It is free and quick. Over $68,000 in prizes has already been given out to active posters on our forum. Additional giveaways are planned.
Detailed information about all U.S. cities, counties, and zip codes on our site: City-data.com.
Please register to participate in our discussions with 2 million other members - it's free and quick! Some forums can only be seen by registered members. After you create your account, you'll be able to customize options and access all our 15,000 new posts/day with fewer ads.
