[eluser]mTuran[/eluser]
I have 2 table for my users. One of "experts" another of "employer", each table have unique ids. in this point i want to give unique id experts and employers same time. For example if last created expert id is 5 then next created employer will be 6 that is which i want. I am waitting your solutions thanks.
[eluser]mTuran[/eluser]
and database is mysql also please don't say use UUID. UUID is very long(16 chars) string i can't say to my users your id is = 6ccd780c-baba-1026-9564-0040f4311e29 
[eluser]slowgary[/eluser]
Do you really want to give your users the primary key of your tables as their user id? Seems like a bad choice. What if you decide to alter your schema? Also, is your first user going to have an id of 1??
Additionally, I'm not sure there's a reliable way to 'alternate' IDs between tables or that it's even a good idea. I really think you might end up with a serious mess on your hands in the long run.
If 'experts' and 'employers' are both 'users', why not just have 1 table for 'users' and add a field for 'user_type', which can be enumerated as 'experts', 'employers'? This way you don't have to worry about managing the primary key. Also, if you're looking for a friendly ID to be able to give the user, you should just allow them to choose their own alphanumeric username. I think UUIDs are actually 36 characters as a string, but if you choose to give your users a numeric ID, you'll need to give them a very large number to assure that you don't run out of numbers. A number like '234509567759' is not as friendly as a username like 'happyharry'.
[eluser]mTuran[/eluser]
@slowgary
Firstly thank you for your post. I don't think give id 1 to my first user i will start counting from 11111 this will dissapear 'difference' between user ids."experts" and "employers" fields are not same because of that i can't join into one table. I want that because if all users both experts and employers is have unique id then managing will be more easy and confortable. There is a way to do that by creating one more table which is hold unique id, user id and type of user but i am looking for more proper way.
[eluser]slowgary[/eluser]
[quote author="mTuran" date="1245588248"]i can't join into one table. I want that because if all users both experts and employers is have unique id then managing will be more easy and confortable.[/quote]
This seems to contradict. Each 'expert' and 'employer' having a unique ID would be much easier and comfortable if they were in the same table. Also, they ARE the same... they're 'users'. I still think the better way would be to use 1 table for the users with unique IDs. Most likely, information would be duplicated otherwise. Won't every user have a password, email, and other similar fields? They belong in the same table. Instead of using a separate table for their unique IDs, use a separate table for attributes that don't belong to both 'experts' and 'employers' and tie them together with their ID. Also, you should not use the user's unique ID as the table's primary key. You should have an auto increment integer primary key, PLUS a unique ID index as the user's ID number. This will help you if you change your database structure in the future.
[eluser]TheFuzzy0ne[/eluser]
What characters are your users allowed in their names? If you restrict it, you should be able to just give them their username as a UUID. After all, you won't be allowing more than one person to have the same username, will you?
[eluser]mTuran[/eluser]
@slowgary
They 'users' right but not have same fields. For example in experts table there is a field called "skills" but it is not available in employers table.If i do this in your way i have create 2 more tables called "expert_attrs", "employer_attrs". Also in my application i will full text search at these attributes. Do you recommend that ? Thanks
@TheFuzzy0ne
i allow for usernames only alphanumeric and underscore sign. Also i check username is available or not at both expert and employer registrations. if i will not found a solution for unique id, may be i use usernames. TheFuzzy0ne please answer question at above. Thanks
[eluser]slowgary[/eluser]
If an expert will have more than 1 skill, wouldn't it be a separate table anyways? The only alternative is something like a comma separate field, but that's not very good design. If you separate the attributes into their own table, you can group them as an added benefit, and provide a listing of experts by skill, like 'PHP', 'CSS', 'JavaScript'.
[eluser]mTuran[/eluser]
Firstly i think seperate by comma like php,css,javascript and then use full-text search. I guess it is not very good design like you said. The another choice is use "tag" design. Let's try with table schemes:
Code: CREATE TABLE IF NOT EXISTS `skill_tagmap` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`expert_id` int(11) DEFAULT NULL,
`tag_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `bookmark` (`expert_id`),
KEY `tag` (`tag_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `tags` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`tag` varchar(255) COLLATE utf8_turkish_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `tag` (`tag`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci AUTO_INCREMENT=1
Is this looking more proper way ? Thank you
[eluser]slowgary[/eluser]
Sure, it really depends on your architecture. I would probably do the whole thing like this:
Code: table accounts
======================================
account_id
account_name
account_password
account_type //enum('expert', 'employer')
other_fields
table skills
=======================================
skill_id
skill_name
table account_skills
=======================================
account_id
skill_id
This is just my opinion. There are plenty of ways to cook and eat a cat, as they say. This way you'd also need to create the skills yourself and let the experts choose from a list, otherwise you might end up with branching... e.g. one user enters 'CSS', another enters 'Cascading Style Sheets' and you end up with 2 different phrases that employers need to search for in order to find someone with that skill.
I think doing it this way though will simplify your username/id dilemma, making it much easier to require uniqueness. Also, I think it's safer to have a primary key that's unknown to the user for table relationships - if the user wishes to change their username (which could be an email address, for instance), you will not have to update all other table's references to that row.
|
