how to relate tables and how normalized the database?Is is correct my work?

Question

Post reply

how to relate tables and how normalized the database?Is is correct my work?

naila-330342

Right there with Babe

Points: 798
More actions
May 20, 2006 at 9:30 pm

#60955
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 Informationitemcode,itemcategory,itemsubcategory,supplierId,

Description, price, customduty, salestax, commissionable, status, quantity,amount,Promotion)

·        Nonstockableitemcode,categoryconnection,categoryservices,

Categoryprmotion,nonstockableId)

·        NonstockableTypesnonstockableId,itemcode)

·        Variable priceitemcode,CEDonLinerent,repairCharges,rentelCharges

SIMReplacementCharges, descriptionCharges)

·        Fixed PriceitemCode,connectionCharges,SIMCardDuty,packagePlan)

·        Stock ablestockableId,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.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Mike C SSC-Insane Points: 23224 More actions · Answer 1

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...

naila-330342 Right there with Babe Points: 798 More actions · Answer 2

naila-330342

Right there with Babe

Points: 798

May 20, 2006 at 9:46 pm

#639135

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????

Mike C SSC-Insane Points: 23224 More actions · Answer 3

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.

naila-330342 Right there with Babe Points: 798 More actions · Answer 4

naila-330342

Right there with Babe

Points: 798

May 24, 2006 at 8:51 am

#639807

hello please help me in my work tell me

how to normalized the database?

Mike C SSC-Insane Points: 23224 More actions · Answer 5

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...

tnk SSCrazy Points: 2613 More actions · Answer 6

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.

Mike C SSC-Insane Points: 23224 More actions · Answer 7

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...

tnk SSCrazy Points: 2613 More actions · Answer 8

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.

Mike C SSC-Insane Points: 23224 More actions · Answer 9

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.

tnk SSCrazy Points: 2613 More actions · Answer 10

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.

Mike C SSC-Insane Points: 23224 More actions · Answer 11

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...