3 Replies - 323 Views - Last Post: 13 November 2011 - 06:13 PM

#1 momo1984  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 25-August 11

What would be a good DB design for this ?

Posted 08 November 2011 - 07:35 AM

Hi ,
I want to create a database that stores users and products.
The user will be able to login to the system to retreive information and also edit products.
A super user that can also add/edit/delete users from system.

Basically I thought of having 2 tables, one for products and the other for users.
The users table has the following fields: username, password, level, name, place and so on.
the product table : product_name,product_description,product_creation_date,product_expiration_date,product_batch_number

note that 100 products may have the same batch number.

I am not sure how to relate all to have a good structure.

Any help would be appreciated,
thank you

Is This A Good Question/Topic? 0
  • +

Replies To: What would be a good DB design for this ?

#2 DarenR  Icon User is online

  • D.I.C Lover

Reputation: 485
  • View blog
  • Posts: 3,247
  • Joined: 12-January 10

Re: What would be a good DB design for this ?

Posted 08 November 2011 - 07:45 AM

you should also have a table that holds the names of the users and their info like email, first name, last name, user id

your user table will be related to this table by user id

the products can also have an productid and possibly add a Usergroup column where you can use the user category for modification.


User table you would need a user groupid for power user and reg user.
Was This Post Helpful? 2
  • +
  • -

#3 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9388
  • View blog
  • Posts: 35,246
  • Joined: 12-June 08

Re: What would be a good DB design for this ?

Posted 08 November 2011 - 07:52 AM

Ghostfly's suggestions are good. Normalizing your data is a good idea - especially when there is a large quantity of it. Oh, and I hope you are not planning on storing passwords in the table. At best a *HASH* should be stored with a salt tossed on top (assuming this is going to be used in a production environment).

Oh, and everything needs a unique ID. Users, products, etc. Do not rely on product name.
Was This Post Helpful? 2
  • +
  • -

#4 hiddenghost  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 39
  • View blog
  • Posts: 621
  • Joined: 15-December 09

Re: What would be a good DB design for this ?

Posted 13 November 2011 - 06:13 PM

Try guessing which fields will be empty/optional or duplicate in the future.
Decide which tables will have columns data that will not be empty and keep them as separate tables from other tables that will have empty column data.
Connect related data in a join between these separate tables.

If there is a lot of duplicate data(100 products may have the same batch number) then you can place duplicate data in a different table with a foreign key to it's related table. If it really is only going to be around 100 products that have the same number you could just keep that in the same table. If you expect to have way more than 100 you'll probably want to separate that from unique data.

If you can't use a foreign key for some reason you'll have to generate a column that will substitute for it with related data like abbreviations of products so you can join the tables that way. Just make sure the abbreviation column is in both tables, and you might want to add a unique one digit number to the end of similar abbreviations.

Focus on duplicates and empty values and you'll figure it out.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1