how to relate tables and how normalized the database?Is is correct my work?
-
I am not much known about making the database .its my first experience .I have done some but I want that some one check my item data .Is it correct or not ? OR its relations and its primary keys are correct .please help me about item data and check this .
In Item we make three tables that are our entities in ERD such as
- Item Table (It’s a main Table)
- Item Information
- NonStockable
- Stock able
Item table is the main table and that are linked with other tables. And Non-Stock able has sub- tables such as NonStockableTypes and this table has more sub tables such as variablePrice, Fixed Price .
· Item Table : (typecode,itemcode,nonstockableId,stockableId)
· Item Information
itemcode,itemcategory,itemsubcategory,supplierId,Description, price, customduty, salestax, commissionable, status, quantity,amount,Promotion)
· Nonstockable
itemcode,categoryconnection,categoryservices,Categoryprmotion,nonstockableId)
· NonstockableTypes
nonstockableId,itemcode)· Variable price
itemcode,CEDonLinerent,repairCharges,rentelChargesSIMReplacementCharges, descriptionCharges)
· Fixed Price
itemCode,connectionCharges,SIMCardDuty,packagePlan)· Stock able
stockableId,cards,hardware)We make the relations are
· ItemInformations primary key goes to the Item Table and make a Foreign key in item table.
· Nonstockable has a item code as a primary key and relate to the item table .and NonstockableTypes has a primary key that relate to the nonstockabletable and this table has more relation such as variable price and fixed price.
· In Stackable table stackableId is a primary key that is relate to the item Table.
-
Question - I might have missed it in there, but what exactly are you trying to model? At first glance I would think that Stockable/Nonstockable would be a column on the Item table, for each item (CHAR column, 'Y' or 'N') and not a separate table. But I can't be sure as I'm a little confused about what exactly you're modeling...
-
thanks for replay . Actually i am confused about these things (stockable,Nonstockable).if you tell me is it correct all tables and attributes ?
i am making the database for Point of sale System for mobile .
please tell me Is it correct all tables and attributes????
-
POS System for mobile ... phones?
I would think that stockable would be a column in your Inventory table (ItemTable?). The stockable flag could be 'Y' for yes or 'N' for no (or some other flags). I'm not sure what your variable/fixed price scheme is, so I can't really advise too much on those either. There are a lot of factors that you didn't really make clear to help you out here.
Do a google on database normalization.
-
hello please help me in my work tell me
how to normalized the database?
-
Database normalization basically involves removing repetitive data, eliminating some "data anomalies" and creating relationships between your tables. Here's a short article with links to other articles: http://databases.about.com/od/specificproducts/a/normalization.htm
Most of the time a minimum of 3NF (Third Normal Form) is recommended. 3NF takes care of most data anomalies and potential maintenance nightmares. Start by reading up on 1NF (First Normal Form) and get your tables up to speed there.
Without a copy of your exact DDL and some knowledge of what you're trying to accomplish, I can't help much more than to point you in the right direction...
-
It's somewhat difficult to understand what you are going for, but it looks like to me you want like an inheritance hierarchy, whereby Items is your main table, and Stockable and Nonstockable have all of the attributes of Items, plus some more of their own. Similarly fixed price and variable price inherit something, but I can't tell what you're trying to do with those.
If what I am thinking is the case, I would recommend something like this:
create table items (
itemCode int identity(1,1) primary key,
itemTypeCode int,
foreign key (itemTypeCode) references itemTypes
)
create table itemsStockable (
itemCode int primary key,
...whatever other attributes for stockable items...
foreign key (itemCode) references items (itemCode)
)
create table itemsNonstockable (
itemCode int primary key,
...whatever other attributes for nonstockable items...
foreign key (itemCode) references items (itemCode)
)
The reason you wouldn't want to store stockableID and nonstockableID in the items table is that logically an item is only one or the other. To figure out which one just check if the itemCode exists in the itemsStockable table or the itemsNonstockable table.
I'm not quite sure where you want to go with fixed and variable price tables. How you set it up depends on what can inherit what attributes. Can items be stockable and either fixed or variable price? Can nonstockable items be fixed and variable price? Can an item be only fixed/variable price without being stockable or nonstockable?
We would need to know the answers to those questions in order to properly design a structure.
-
I disagree with this statement:
"The reason you wouldn't want to store stockableID and nonstockableID in the items table is that logically an item is only one or the other. To figure out which one just check if the itemCode exists in the itemsStockable table or the itemsNonstockable table."
Stockable or nonstockable is (probably) just an attribute of each item. You wouldn't design two tables for "Cars", one for the red cars ("carsRed") and one for blue cars ("carsBlue"). At least I wouldn't...
OTOH, trying to decipher what the OP wants is nearly impossible - not enough details, no DDL and no real explanation of what he wants to accomplish...
-
The example of red and blue cars is not like the situation here at all.
The reason you wouldn't use two tables for red and blue cars, is that red and blue are values of one attribute, color. However, if Red cars was an entity that had other attributes unique to only red cars, and blue cars had other attributes unique to only blue cars, then yes you would use seperate tables.
In this case, there seems to be unique attributes to stockable and nonstockable items, and as a result it is necessary to use seperate tables. Otherwise you would have to put all stockable and nonstockable attributes together into one big items table, and all of the stockable attributes would be null if the item was nonstockable and all of the nonstockable attributes would be null if the item was stockable. I certainly wouldn't do that.
-
As I said, the OP didn't make a lot of things really clear.
-If "XYZ Cell Phone Battery" is a stockable item and "WXY Cell Phone Battery" is a nonstockable item, why are the two being stored in separate tables?
-Why are you tracking additional information for one that you don't need for the other?
-What do you do when "XYZ Cell Phone Battery" becomes nonstockable, or vice versa?
-And how do you then deal with all the missing information that you didn't have to keep track of when it was in the other category, but now have to keep track of?
-Is "stockable/nonstockable" items a class differentiator (like the difference between car and fish?) or is it just an attribute (yellow and blue)?
-Does the OP really need to keep track of more information for one than the other? Or is the table design being dictated by the report writing software? (i.e., the nonstockable report will show ABC and the stockable report will show DEF).
And finally, if you want some sort of consolidated report (I just want one report with every item on it), you'll be doing some fancy footwork to produce such a report. Effectively there is no such thing as a single listing of "every item we sell", since "XYZ Cell Phone Battery" is a complete different type of item from "WXY Cell Phone Battery", which may or may not be a completely different type of item from "GHI Cell Phone Battery". There's not even a useful report of all the Cell Phone Batteries, since the cell phone batteries are not the same class of object.
-
You're right, not a lot was clear from the original post. We have no requirements, just some sketchy entities and attributes listed with smiley faces all over the place. The original post had stockable/nonstockable listed as seperate entities with their own sets of different attributes. I offered a suggestion on that assumption.
You raise some good questions that would need answers before a good answer could be provided.
-
Yeah, he didn't really spell it out, and I assumed that he's trying to make some sort of inventory tables... but if he's modeling completely different types of items then he might at least change the names to better reflect it. Like if "nonstockable items" are Service Agreements and Customer Contracts, a better naming convention would definitely clear up the situation...
-
Actually Non-Stockable Have many types Such as (Fixprice ,VariablePrice) So it creats a confusion which we include OR Not?
-
Actually Non-Stockable Have many types Such as (Fixprice ,VariablePrice) So it creats a confusion which we include OR Not?
-
Actually Non-Stockable Have many types Such as (Fixprice ,VariablePrice) So it creats a confusion which we include OR Not?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply