About tables - if this is possible

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

About tables - if this is possible

Author

Topic

syon
Starting Member

2 Posts

Posted - 2012-02-24 : 16:11:40

Hello, im currently a student and am trying to do a program for self-use incorporating SQL server and java (its a simple stock control for my fathers workspace), i only have basic knowledge about either of them.

My problem resolves on knowing how to do my tables since i have a situation i didnt have to deal with before.

rather then saying it by words, gonna use A,B,C, .. to explain show it.

Category (A) key: A
Sub-Category (A, B), key: AB
Sub-Sub-Category(A, B, C) key:ABC

Product(A, B, D) key: ABD OR Product(A, B, C, D) key: ABCD

since i have sub-Categorys that dont have sub-sub-categorys, products can be from there... or would have to be from sub-sub-category in case they exist.

is my only option making Product1 (A, B, D) and Product 2(A, B, C, D)?

is there any way to make this happen? as far as i know, it wouldnt be... but would rly appreciate some input to be sure, along with some general "definition" i could look up to learn how to do it.

ty in advance,
cumps

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-24 : 17:32:21

do you have actual tables yet?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

syon
Starting Member

2 Posts

Posted - 2012-02-24 : 19:35:00

i did them just now, didnt add other variables yet becouse they werent necessary to show what i meant. (some of this tables, mainly categorys probably wont have any other information besides their "name".. since they exist mostly for me to seperate the type of products)

CREATE TABLE Categoria (
nomeCategoria varchar(10) primary key not null
)

CREATE TABLE SubCategoria1 (
nomeCategoria varchar(20) foreign key references Categoria,
nomeSubCategoria1 varchar(20) not null,
Primary key (nomeCategoria, nomeSubCategoria1)
)

CREATE TABLE SubCategoria2 (
nomeCategoria varchar(10),
nomeSubCategoria1 varchar(20),
nomeSubCategoria2 varchar(20) not null,
Foreign Key (nomeCategoria, nomeSubCategoria1) references SubCategoria1,
Primary Key (nomeCategoria, nomeSubCategoria1, nomeSubCategoria2)
)

CREATE TABLE Marca (
nomeMarca varchar(10) Primary Key not null
)

CREATE TABLE Produto (
nomeCategoria varchar(10),
nomeSubCategoria1 varchar(20),
nomeProduto varchar(15) not null,
nomeMarca varchar(10) foreign key references Marca,
Foreign Key (nomeCategoria, nomeSubCategoria1) references SubCategoria1,
Primary Key (nomeCategoria, nomeSubCategoria1, nomeProduto, nomeMarca)
)

CREATE TABLE Produto (
nomeCategoria varchar(10),
nomeSubCategoria1 varchar(20),
nomeSubCategoria2 varchar(20) not null,
nomeProduto varchar(15) not null,
nomeMarca varchar(10) foreign key references Marca,
Foreign Key (nomeCategoria, nomeSubCategoria1, nomeSubCategoria2) references SubCategoria2,
Primary Key (nomeCategoria, nomeSubCategoria1, nomeSubCategoria2, nomeProduto, nomeMarca)
)

As i mentioned on the 1st post, i could avoid this situation by having diferent names on the product tables (product1 and product2), but since they are the same thing... is there any way to make 1 table with 2 types of keys? this would make it easier for later use in java. (from what i was taught, this would be impossible... but since im a begginer, there might be some other alternatives)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources