Would like suggestions on my design

Databases
1

Hi everyone.

I am making a small project site for my family that will allow users to register, log in, and make lists of items that they want… products on amazon etc.

I just want to know if this design I have come up with for the database is ok for that purpose:

here are my two tables:

members (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
username varchar(55) NOT NULL
email varchar(55) NOT NULL,
password char(55) NOT NULL,
)

lists (
user_id INT NOT NULL,
item_id INT NOT NULL AUTO_INCREMENT,
item_url varchar(2000),
item_category varchar(255),
item_subcategory varchar(255),
item_notes varchar(255),
)

I’ll be honest I just guessed for some of it (item_url varchar length, for example), so I would love some suggestions on how to make these tables more suitable.

2

called it lists because everything in that table will be part of each users ‘list’. I guess calling it items makes sense though, will change that. :slight_smile:

which would be best for urls, text? and is there a standard length I should use for urls?

“the lists.user_id in the lists table, does that refer to a user? If so I would call it user_id to avoid confusion.”

I’m not sure… do you mean change id in the members table to user_id as well? It’s already called user_id in the lists table.

3

The maximum length of varchar is 255.
For anything more you need to use the text datatype (or tinytext, mediumtext, or longtext, depending on your needs).

the lists.user_id in the lists table, does that refer to a user? If so I would call it user_id to avoid confusion.

I don’t really get why all columns in the lists table start with “item_”. Shouldn’t the table be called “items” or do you have a table called “items” you didn’t show here?

4

AFAIK there is no real imposed limit on the length of a URL, but in terms of used space it doesn’t matter which one why choose. Going for text is fine.

Oops, I meant the other way around, name user_id in the table formerly known as lists: member_id
member_id refers to members.id is easier to remember than user_id refers to member.id
You’ll thank yourself later, trust me :slight_smile:

5

this was bumped up to 65,535 in version 5.0.3

:slight_smile:

6

Ok, will use text(5000) or something to be safe.

Oh of course! Yeah, that makes a lot more sense. I got ya now, thanks. :slight_smile:

Ok I guess that should do it unless anyone else has any other comments on the table design. Thanks so much for the help, it is much appreciated.